Update Query

Asp Database

[vanjamier]

Hello,

I have a problem updating my database.

If Request.Form("btnEdit") = "Update" Then
 
 owname = Request.Form("txtname") 
 owemail = Request.Form("txtemail") 
 softname = Request.Form("txtswname") 
 softver = Request.Form("txtswver") 
 company = Request.Form("txtcom") 
 number = Request.Form("txtnum") 
 serial = Request.Form("txtserial") 
 date1 = Request.Form("txtdate") 
 date2 = Request.Form("txtldate") 
  
 Set rs2 = Server.CreateObject("ADODB.RecordSet")
 
 'update record
 sqlstr2 = "Update LicenseTab set" _
  & " Ownername = '" & owname & "', " _
  & " Owneremail = '" & owemail & "', " _
  & " SwName = '" & softname & "', " _
  & " SwVersion = '" & softver & "', " _
  & " SwCompany = '" & company & "', " _
  & " LicenseAmount = '" & number & "', " _
  & " LicenseNumber = '" & serial & "', " _
  & " LicenseDate = '" & date1 & "', " _
  & " LicenseEndDate = '" & date2 & "', " _
  & " Where id = ' & strID & '" 
     
 rs2.Open sqlstr2, connStr
 End if

My error message is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Where'.
/itls/slid/license_mod.asp, line 63

I thought i already break down the query but... :(

[mattglet]

sqlstr2 = "Update LicenseTab set" _
& " Ownername = '" & owname & "', " _
& " Owneremail = '" & owemail & "', " _
& " SwName = '" & softname & "', " _
& " SwVersion = '" & softver & "', " _
& " SwCompany = '" & company & "', " _
& " LicenseAmount = '" & number & "', " _
& " LicenseNumber = '" & serial & "', " _
& " LicenseDate = '" & date1 & "', " _
& " LicenseEndDate = '" & date2 & "', " _
& " Where id = " & strID

That should do it, as long as "id" is an interger field.

[vanjamier]

Hey i tried it,
I still get the name error

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Where'.

/itls/slid/license_mod.asp, line 63

[txbakers]

You need to do a REsponse.Write(sqlstr2) and put the end result on the screen so you can see what the error is.

[vanjamier]

SQL Statement: Update LicenseTab set Ownername = 'William1', Owneremail = 'William@infxx ', SwName = 'testSoftware1', SwVersion = '1.000.0001 ', SwCompany = 'Microslop', LicenseAmount = '1002', LicenseNumber = '199e-2342-3431-232-2323 ', LicenseDate = '1/1/2004', LicenseEndDate = '1/2/2020', Where id = '18'

This is the result, seems fine doesnt it?

[vanjamier]

I realised i have a comma before where.. thats all

[txbakers]

I realised i have a comma before where.. thats all

I can remember the hours spent trying to debug something like this, only to find a misplaced single quote or comma. I do so much of this dynamically, with variables and concatenation, that it's so easy to.

My best friend in all this is the Response.Write command. I can see exactly what I'm sending to the DB and where it fails and why I'm not getting the results I'm expecting.

[vbgod]

I know this may be picky, but this message also appears if you allow the user to use an illegal character / and don't do anything about it.

MyVar = MyVar.Replace("'","''")

This will replace any single quote with two single quotes (it's an escape feature). When you read the data back from the database, there will just be one single quote.

[vanjamier]

oh thanks for tips people.
Much appreciated.

[Zaphod Beeblebrox]

Another tip to debug these things: After using Response.Write to display the query, copy/paste it into a MS Access query window and run it there. It often gives a better error message, and also often shows where exactly in the query the error occurred.