Help! Syntax Error in SQL statement

Report · Apr 07, 2008

Hello. I'm getting an error message and I'm just not seeing where I went wrong. The SQL statement is:

updateSQL = "UPDATE TrainingHistory SET Status='" & fFormat(Request.Form(cStatus)) & "', StatusComments='" & fFormat(Request.Form(cStatusComments)) & " WHERE Training_ID=" & fFormat(Request.Form(cTrainingID))

The error message is:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression '' WHERE Training_ID=9054'.

I've been looking at it for a while. Not sure where I went wrong. Here is a more complete version of the code:

<%
Function fFormat(vText)
fFormat = Replace(vText, "'", "''")
End Function

Sub sRunSQL(vSQL)
set cExecute = Server.CreateObject("ADODB.Command")
With cExecute
.ActiveConnection = MM_coldsuncrea_lms_STRING
.CommandText = vSQL
.CommandType = 1
.CommandTimeout = 0
.Prepared = true
.Execute()
End With
End Sub

If Request.Form("action")="update" Then
'Set variables for update
Dim updateSQL, i
Dim cTrainingID, cStatus, cStatusComments

'Loop through records on screen and update
For i = 1 To fFormat(Request.Form("counter"))

'Create the proper field names to reference on the form
cTrainingID = "Training_ID" & CStr(i)
cStatus = "Status" & CStr(i)
cStatusComments = "StatusComments" & CStr(i)

'Create the update sql statement
updateSQL = "UPDATE TrainingHistory SET Status='" & fFormat(Request.Form(cStatus)) & "', StatusComments='" & fFormat(Request.Form(cStatusComments)) & " WHERE Training_ID=" & fFormat(Request.Form(cTrainingID))

'Run the sql statement
Call sRunSQL(updateSQL)
Next

'Refresh page
Response.Redirect("ClassUpdateRoster.asp?Training_ID=") & (rsClassDetails.Fields.Item("event_ID").Value)
End If

%>

Report · Apr 08, 2008

You need another single quote after the double quote before the WHERE clause. You are not closing the single quote you used to delimit the value for StatusComments.