Skip to main content
Participant
April 7, 2008
質問

Help! Syntax Error in SQL statement

  • April 7, 2008
  • 返信数 1.
  • 228 ビュー
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

%>
このトピックへの返信は締め切られました。

返信数 1

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.