Skip to main content
Participant
April 7, 2008
Question

Help! Syntax Error in SQL statement

  • April 7, 2008
  • 1 reply
  • 228 views
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

%>
This topic has been closed for replies.

1 reply

Participating Frequently
April 8, 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.