Updating Multiple Records

[rubertoga]

I get the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

I've checked the names allocated to the table and field names against an SQL reserved word list and they do not appear on there. I've also tried renaming the table and all its fields and made changes in the code respectively but I'm still getting the same error. Anybody have any ideas? Code below, thanks.

<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<% 'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsUpdateQuestion 'Holds the recordset for the record to be updated

'Create an ADO connection object
Set adoCon = Server.CreateObject(&quot;ADODB.Connection&quot


'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open &quot;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & Server.MapPath(&quot;ksp.mdb&quot

Dim iCount
iCount=Request.Form(&quot;Count&quot
Dim strSQL 'Holds the SQL query to query the database

'need to get each value from previous page
Dim strOptionID, strQuestion, strOption, strResponse, strMark, strTick
'loop through each form element
Dim iLoop
For iLoop=0 to iCount
strOptionID=Request.Form(iLoop & &quot;.optionid&quot
strQuestion=Request.Form(iLoop & &quot;.questionid&quot
strOption=Request.Form(iLoop & &quot;.optiontext&quot
strResponse=Request.Form(iLoop & &quot;.reply&quot
strMark=Request.Form(iLoop & &quot;.mark&quot
strTick=Request.Form(iLoop & &quot;.tick&quot

strSQL=&quot;UPDATE QuestionOptions SET [QuestionID] =&quot; & strQuestion &&quot; , [OptionText] ='&quot; & strOption &&quot;', [Reply] ='&quot; & strResponse &&quot;', [Mark]=&quot; & strMark &&quot; , [Tick] = &quot;& strTick &&quot; WHERE [OptionID] = &quot; & strOptionID &&quot;&quot;
adoCon.Execute strSQL
Next

'Reset server objects
adoCon.Close
Set adoCon = Nothing

'Return to the update select page in case another record needs ammending
Response.Redirect &quot;optionupdate.asp&quot;
%>

 

[baddos]

Try doing a Response.Write strSQL and see if the query looks correct.

 

[rubertoga]

how is this done exactly I'm not sure, new to ASP.

 

[robi2]

just insert the code
response.write strSQL

before the:
adoCon.Execute strSQL

and then look at it, if still you find no errors, paste it here.
You are probably recieving empty variables from the request.querystring lines.

You should do a check to see if all variables aren't empty before executing the statement.

 

[baddos]

Yes... You might want to do a Response.End after doing the Response.Write so any error or other html doesn't cover it up.

-Bad Dos