INSERT Statement problem in VBScript/ASP

[Maklar]

I am attempting to execute an INSERT statement on my page but continually get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '<'.

/int_code04/myNMLC/insertNewTrackRecord.asp, line 97

I've tested the INSERT stmt both within SQL Server and as a string literal within the page's code with hardcoded values to ensure that the statement works, which is does. But when I assign this statement to a variable using the variables containing the values and then execute it (as shown below), it doesn't work:

"mySQL="INSERT INTO nsn_shipment_tracking_lines (fk_nsn_shipment_tracker, qty_shipped, date_shipped, date_received, tracking_number, shipping_company, date_entered) VALUES (" & fk_ID & ", " & qty_shipped & ", " & shipped_dt_and_clause & ", " & received_dt_and_clause & ", '" & tracking_number & "'," & shipping_company & ", getdate())"

cn.Execute(mySQL)"

When I Response.write the contents of my variable containing the above SQL statement (response.write (mySQL)), I get the following:

INSERT INTO nsn_shipment_tracking_lines (fk_nsn_shipment_tracker, qty_shipped, date_shipped, date_received, tracking_number, shipping_company, date_entered) VALUES (890
, 450, '10/30/2009', '11/8/2009', 'DFTG4385DRE673', 1, getdate())

This looks right to me but again, this doesn’t work. One other “process of elimination” thing I tried was to first remove all of the date field values from the INSERT statement, to see if I could accomplish an insert with out these values (since most fields in the table allow for NULL values). The result was this didn’t make a difference. Then I tried the insert one field at a time; this also made no difference.

Any insight to this troubling issue would be greatly appreciated! Thanks in advance!

[olivthill2]

Maybe getdate() is not well understood by ODBC, since it is not a standard SQL statement.

[rnasesa]

Have you tried remove the getDate function out of the SQL statement as in either

"mySQL="INSERT INTO nsn_shipment_tracking_lines (fk_nsn_shipment_tracker, qty_shipped, date_shipped, date_received, tracking_number, shipping_company, date_entered) VALUES (" & fk_ID & ", " & qty_shipped & ", " & shipped_dt_and_clause & ", " & received_dt_and_clause & ", '" & tracking_number & "'," & shipping_company & ", " & getdate() & ")"

or

"mySQL="INSERT INTO nsn_shipment_tracking_lines (fk_nsn_shipment_tracker, qty_shipped, date_shipped, date_received, tracking_number, shipping_company, date_entered) VALUES (" & fk_ID & ", " & qty_shipped & ", " & shipped_dt_and_clause & ", " & received_dt_and_clause & ", '" & tracking_number & "'," & shipping_company & ", " & Date() & ")"

I am assuming getdate() is some VBScript function that you have created to enter todays date. The second option uses the VBScript Date() command. You may or not not have to put date delimits depending on your ODBC database.