Update Query Help

dalezjc · June 18th, 2007, 08:52 AM

I'm trying to update a field in my database (SQL) using the following query:

Code:

set Conn=Server.CreateObject("ADODB.Connection")
set rsUpdate = server.CreateObject("ADODB.Recordset")
Conn.open "Driver={SQL Server}; Server=myserver;Database=StanEval;UID=xx;PWD=xx;"

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

But 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.Form("written_exam")) & "' " & _

mat41 · June 18th, 2007, 06:58 PM

You dont need the underscore before you trim. Try:

set rsUpdate = conn.Execute "UPDATE usertbl SET written_exam = '" & Trim(Request.Form("written_exam")) & "' WHERE user_ID = " & Request.Form("user_ID") & ";"

Note the trailing semi colon, this is always a good idea

Wind is your friend
Matt

dalezjc · June 19th, 2007, 06:31 AM

Still getting the following error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/staneval/update.asp, line 76

set rsUpdate = conn.Execute "UPDATE usertbl SET written_exam = '" & Trim(Request.Form("written_exam")) & "' WHERE user_ID = " & Request.Form("user_ID") & ";"
----------------------------^

dalezjc · March 10th, 2008, 03:06 PM

Yes, it's been a while, but I'm still interested in some help on this.

dalezjc · March 14th, 2008, 04:12 PM

Bump.

dparsons · March 14th, 2008, 07:10 PM

Do this, and post the resulting string

Dim sql
sql = "Update usertbl SET written_exam = '" & Trim(Request.Form("written_exam")) & "' " _
& " where user_ID = " & Request.Form("user_ID")

Response.Write sql

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

woodyz · March 15th, 2008, 01:36 AM

Wow. 9 months since the original post, and still no answer. I don't think I have that much patience. Anyway...

Okay, tell me if I am wrong, guys. But don't you need parenthesis for a function call returning a value used in an expresion? Such as:

Code:

set rsUpdate = conn.Execute("UPDATE usertbl SET written_exam = '" & Trim(Request.Form("written_exam")) & "' WHERE user_ID = " & Request.Form("user_ID") & ";")

Or something like that. Of course, there could be a few other problems, but that is a start.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit

dalezjc · March 18th, 2008, 08:47 AM

Quote:

quote:Originally posted by dparsons
Do this, and post the resulting string

Dim sql
sql = "Update usertbl SET written_exam = '" & Trim(Request.Form("written_exam")) & "' " _
& " where user_ID = " & Request.Form("user_ID")

Response.Write sql

hth.

Here's the output:

Update usertbl SET written_exam = '' where user_ID =

dparsons · March 18th, 2008, 08:49 AM

This is an invalid SQL Statement: Update usertbl SET written_exam = '' where user_ID = because your where clause is empty. You need to have a value for userID.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========