469,934 Members | 2,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

INSERT INFO statement problem on ASP page

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!
Jan 4 '10 #1
1 2319
jhardman
3,406 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. dim query, objConn, objRS
  2. set objConn = server.createobject(adodb.connection)
  3. objConn.open connectionString 'use whatever connection string you want
  4.  
  5. query = "SELECT fk_nsn_shipment_tracker, qty_shipped, date_shipped, date_received, tracking_number, shipping_company, date_entered FROM nsn_shipment_tracking_lines"
  6.  
  7. set objRS = server.createobject(adodb.recordset)
  8. objRS.open query, objConn, adopendynamic, adlockoptimistic
  9. 'adopendynamic and adlockoptimistic are integer constants, 2 and 3 I believe, you can look those up
  10.  
  11. objRS.addnew()
  12. objRS("fk_nsn_shipment_tracker") = fk_id
  13. 'etc
  14. 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
Jan 6 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by robert | last post: by
3 posts views Thread by A Lonely Programmer | last post: by
reply views Thread by Tim::.. | last post: by
1 post views Thread by Ed Dror | last post: by
2 posts views Thread by paulmitchell507 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.