Update Query Help

[dalezjc]

Hi,

I searched the forum for answer to this, but couldn't fnd anything, although there were some articles that came close.

Below is my update query code, but I keep getting "expected end of statement" errors. I'm a newbie to ASP so I assume it's a syntax error of some sort.

set Conn=Server.CreateObject("ADODB.Connection")
set rsUpdate = server.CreateObject("ADODB.Recordset")
Conn.open "Driver={SQL Server}; Server=server;Database=minel;UID=user;PWD=pwd;"

set rsUpdate = conn.Execute "Update usertbl SET written_exam = '" & Trim(Request.Form("written_exam")) & "' , " & _
" where user_ID = " & Request.Form("user_ID")

Thanks for the help.

[improvcornartist]

Can you post the actual error message? Also, is user id a number, string, etc?

[dalezjc]

user_id is a int. And here's the complete error message:

Microsoft VBScript compilation error '800a0401' 

Expected end of statement 

/staneval/update.asp, line 74 

set rsUpdate = conn.Execute "Update usertbl SET written_exam = '" & Trim(Request.Form("written_exam")) & "' , " & _

[improvcornartist]

Try removing the comma in the SQL statement.

set rsUpdate = conn.Execute "Update usertbl SET written_exam = '" & Trim(Request.Form("written_exam")) & "'  " & _
" where user_ID = " & Request.Form("user_ID")

Let me know what that does.

[dalezjc]

Removed comma and stll the same error.

[improvcornartist]

How about:

set rsUpdate = conn.Execute "Update usertbl SET written_exam = '" & _ Trim(Request.Form("written_exam")) & "'  " & _
" where user_ID = " & Request.Form("user_ID")

[dalezjc]

Now I'm getting the following error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/staneval/update.asp, line 74

set rsUpdate = conn.Execute "Update usertbl SET written_exam = '" & _ Trim(Request.Fo rm("written_exa m")) & "' " & _

[improvcornartist]

I think your error is due to punctuation. I'm not sure of how to fix it, but my next trial would be to place the SQL statement in a variable, rather than trying to connect with it directly.

strSQL = "Update usertbl SET written_exam = '" & _ Trim(Request.Form("written_exam")) & "'  " & _
" where user_ID = " & Request.Form("user_ID")
set rsUpdate = conn.Execute strSQL

This way you can also print strSQL to the screen to make sure the SQL command is correct.

[dalezjc]

Okay, I'll try that. Thanks for the help.

Dale