INSERT INFO statement problem on ASP page

[Maklar60]

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!

[jhardman]

Thanks for doing the basics, I always ask people in your position to response.write the query.

There is definitely something else going on. somehow a less than sign (<) is being sent in the query. If you want, you could post more of the code to let us see, because the query you are posting here isn't being sent.

I generally use a different method: I use a "select" query to populate a recordset, add a new row to the recordset, set the values of that row, then use the recordset's "update" method to update the db. If you want to try that method, the code looks something like this:

dim query, objConn, objRS
set objConn = server.createobject(adodb.connection)
objConn.open connectionString 'use whatever connection string you want
 
query = "SELECT fk_nsn_shipment_tracker, qty_shipped, date_shipped, date_received, tracking_number, shipping_company, date_entered FROM nsn_shipment_tracking_lines"
 
set objRS = server.createobject(adodb.recordset)
objRS.open query, objConn, adopendynamic, adlockoptimistic
'adopendynamic and adlockoptimistic are integer constants, 2 and 3 I believe, you can look those up
 
objRS.addnew()
objRS("fk_nsn_shipment_tracker") = fk_id
'etc
objRS.update()

One of the things I really like about this method, is you can automatically check for duplicate entries by adding a "where" clause to your query, something like "WHERE fk_nsn_shipment_tracker = 165" then before I add a new row, I check if there were any rows returned; if yes, I can say "This record appears to be a duplicate", otherwise I add it. Does this make sense?

Jared