#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    6
    Rep Power
    0

    Syntax Error in UPDATE Statement


    I am trying to update several fields in a record. I tried updating just one field and it works. If i update more than one field then I get the message Syntax Error in Update statement.

    I had to rewrite my code to using a prepared statement. I was using ADO and it was working fine. Please help me figure out why I am having a syntax error if I am upding more than one field. Thanks. Below is my code:

    Code:
     Set objCmd = Server.CreateObject("ADODB.command")
     objCmd.ActiveConnection = objConn
     If blnNewRecord Then
     	objCmd.CommandText = "INSERT INTO Member SET EmailAddress = ?, Password = ?, FamilyName = ?, GivenName = ?, PhoneNumber = ?, StreetAddress1 = ?, "
     	If Session("StreetAddress2") <> " " Then
     		objCmd.CommandText = objCmd.CommandText & "StreetAddress2 = ?, City = ?, State = ?, PostalCode = ?, Country = ?, "
     	Else
     		objCmd.CommandText = objCmd.CommandText & "City = ?, State = ?, PostalCode = ?, Country = ?, "
     	End If
     	objCmd.CommandText = objCmd.CommandText & "Active = ?, LastLogin = ?, RegistrationDate = ?, MailingList = ?, Status = ? WHERE SSN = ? "
     Else
      	objCmd.CommandText = "UPDATE Member SET EmailAddress = ?, Password = ?, FamilyName = ?, GivenName = ?, PhoneNumber = ?, StreetAddress1 = ?, "
      	If Session("StreetAddress2") <> "" Then
      		objCmd.CommandText = objCmd.CommandText & "StreetAddress2 = ?, City = ?, State = ?, PostalCode = ?, Country = ?, "
      	Else
      		objCmd.CommandText = objCmd.CommandText & "City = ?, State = ?, PostalCode = ?, Country = ?, "
      	End If
       	objCmd.CommandText = objCmd.CommandText & "Active = ?, LastLogin = ?, RegistrationDate = ?, MailingList = ?, Status = ? WHERE SSN = ? "
     End If
    
     objCmd.CommandType = adCmdText
     objCmd.Prepared = True
    
     objCmd.Parameters.Append(objCmd.CreateParameter("EMailAddress", adVarChar, adParamInput, Len(Request("Email")), Request("EMail")))
     objCmd.Parameters.Append(objCmd.CreateParameter("Password", adChar, adParamInput, Len(Request("Password")), Request("Password")))
     objCmd.Parameters.Append(objCmd.CreateParameter("FamilyName", adChar, adParamInput, Len(Request("FamilyName")), Request("FamilyName")))
     objCmd.Parameters.Append(objCmd.CreateParameter("GivenName", adChar, adParamInput, Len(Request("GivenName")), Request("GivenName")))
     objCmd.Parameters.Append(objCmd.CreateParameter("PhoneNumber", adChar, adParamInput, Len(Request("PhoneNumber")), Request("PhoneNumber")))
     objCmd.Parameters.Append(objCmd.CreateParameter("StreetAddress1", adChar, adParamInput, Len(Request("Address1")), Request("Address1")))
     If Request("StreetAddress2") <> "" Then
     	objCmd.Parameters.Append(objCmd.CreateParameter("StreetAddress2", adChar, adParamInput, Len(Request("Address2")), Request("Address2")))
     End If
     objCmd.Parameters.Append(objCmd.CreateParameter("City", adChar, adParamInput, Len(Request("City")), Request("City")))
     objCmd.Parameters.Append(objCmd.CreateParameter("State", adChar, adParamInput, Len(Request("State")), Request("State")))
     objCmd.Parameters.Append(objCmd.CreateParameter("PostalCode", adChar, adParamInput, Len(Request("PostalCode")), Request("PostalCode")))
     objCmd.Parameters.Append(objCmd.CreateParameter("Country", adChar, adParamInput, Len(Request("Country")), Request("Country")))
     objCmd.Parameters.Append(objCmd.CreateParameter("Active", adBoolean, adParamInput, Len(Request("Active")), TRUE))
     objCmd.Parameters.Append(objCmd.CreateParameter("LastLogin", adDate, adParamInput, Len(Now()), Now()))
     objCmd.Parameters.Append(objCmd.CreateParameter("RegistrationDate", adDate, adParamInput, Len(Request("RegistrationDate")), Request("RegistrationDate")))
     If Request("MailingList") = Checked Then
     	objCmd.Parameters.Append(objCmd.CreateParameter("MailingList", adBoolean, adParamInput, Len(MailingList), TRUE))
     Else
     	objCmd.Parameters.Append(objCmd.CreateParameter("MailingList", adBoolean, adParamInput, Len(MailingList), FALSE))
     End If
     objCmd.Parameters.Append(objCmd.CreateParameter("Status", adChar, adParamInput, Len(rsMemInfo("Status")), rsMemInfo("Status")))
     objCmd.Parameters.Append(objCmd.CreateParameter("SSN", adInteger, adParamInput, Len(Request("SSN")), Request("SSN")))
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    I think you need the Set keyword in the 2nd line of code.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    6
    Rep Power
    0
    I found my mistake. I was using the word password which was a reserved word. When I replaced it, it worked.
    Thanks for your input Doug.
  6. #4
  7. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    Thanks for posting the solution you found.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo