I keep getting this error with my code. Can someone help me figure it out?


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Catlin'.

/Change_Management_Description_Update.asp, line 91


Here is the code:


If Request.Form("FormAction")="Step2" Then

        strSQL = "Update CM Set " & _

        "Requester = " & _
        cStr(Request.Form("Requester")) & "' " & _

        "WorkGroup = " & _
        cStr(Request.Form("WorkGroup")) & "' " & _

        "Priority = " & _
        cStr(Request.Form("Priority")) & "' " & _

        "Title = " & _
        cStr(Request.Form("Title")) & "' " & _

        "Date = " & _
        cStr(Request.Form("Date")) & "' " & _

        "Target = " & _
        cStr(Request.Form("Target")) & "' " & _

        "Category = " & _
        cStr(Request.Form("Category")) & "' " & _

        "Assignee = " & _
        cStr(Request.Form("Assignee")) & "' " & _

        "Justification = " & _
        cStr(Request.Form("Justification")) & "' " & _

        "Components = " & _
        cStr(Request.Form("Components")) & "' " & _

        "Applications = " & _
        cStr(Request.Form("Applications")) & "' " & _

        "Hardware = " & _
        cStr(Request.Form("Hardware")) & "' " & _

        "Networks = " & _
        cStr(Request.Form("Networks")) & "' " & _

        "WorkGroups = " & _
        cStr(Request.Form("WorkGroups")) & "' " & _

        "Testing = " & _
        cStr(Request.Form("Testing")) & "' " & _

        "CompletedBy = " & _
        cStr(Request.Form("CompletedBy")) & "' " & _

        "Implementations = " & _
        cStr(Request.Form("Implementations")) & "' " & _

        "Recovery = " & _
        cStr(Request.Form("Recovery")) & "' " & _

        "Resources = " & _
        cStr(Request.Form("Resouces")) & "' " & _

        "Impact = " & _
        cStr(Request.Form("Impact")) & "' " & _

        "Approved = " & _
        cStr(Request.Form("Approved")) & "' "


        Dim RS, Conn, SQL
        Dim objCmd, strSQL, adCmdText
        Dim adOpenStatic, adLockOptimistic


        Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "Change_Management"
        Set RS = Server.CreateObject("ADODB.Recordset")
        RS.Open strSQL, conn, 3, 2
        Set objCmd = Server.CreateObject("ADODB.Command")
        Set objCmd.ActiveConnection = Conn
        objCmd.CommandText = strSQL
        objCmd.CommandType = adCmdText
        objCmd.Execute


        'Close the database objects
        RS.Close
        Set RS=Nothing
        objCmd.Close
        Set objCmd=Nothing
        Conn.Close
        Set Conn=Nothing
        Response.Redirect "Change_Management_Description_of_Request.asp"
        Else

        Response.Write "No success"

        End If

It looks like every one of the values you are writing to the SQL statement is ending with a ' but never starts with it.

       "Requester = " & _
        cStr(Request.Form("Requester")) & "' " & _

should be

       "Requester = '" & _
        cStr(Request.Form("Requester")) & "' " & _

Or possibly you meant that ' to be a , because those are missing too. The best troubleshooting for any kind of database error is to simply write out the constructed query and see what the database is seeing.

Peter
------------------------------------------------------
Work smarter, not harder.

I put a ' in front of the " like you said, and now I get this error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'WorkGroup'.

It seems that it did move down to the next recordset, but instead of the value of the recordset, it is now trying to use the field name itself.

Where should I put , ?'s

I put the ', in between the "" and now I'm getting a new error.

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/Change_Management_Description_Update.asp, line 98


Also, when I go back to my webpage that displays all the descriptions of submitted info, all the "descriptions" fields are the same as the last updated one. It updated all the same fields to the same value.

Request.Form() returns a string, so it's unnecessary to CStr() the result.

Unless every one of those fields is a string field, you will need to adjust the query to suit the datatypes of the fields.

It sounds like you might want to look thru some SQL tutorials. But for now, give this a try...

    strSQL = "Update CM Set " & _
    "Requester = '" & Request.Form("Requester") & "' " & _
    ", WorkGroup = '" & Request.Form("WorkGroup") & "' " & _
    ", Priority = '" & Request.Form("Priority") & "' " & _
    ", Title = '" & Request.Form("Title") & "' " & _
    ", Date = '" & Request.Form("Date") & "' " & _
    ", Target = '" & Request.Form("Target") & "' " & _
    ", Category = '" & Request.Form("Category") & "' " & _
    ", Assignee = '" & Request.Form("Assignee") & "' " & _
    ", Justification = '" & Request.Form("Justification") & "' " & _
    ", Components = '" & Request.Form("Components") & "' " & _
    ", Applications = '" & Request.Form("Applications") & "' " & _
    ", Hardware = '" & Request.Form("Hardware") & "' " & _
    ", Networks = '" & Request.Form("Networks") & "' " & _
    ", WorkGroups = '" & Request.Form("WorkGroups") & "' " & _
    ", Testing = '" & Request.Form("Testing") & "' " & _
    ", CompletedBy = '" & Request.Form("CompletedBy") & "' " & _
    ", Implementations = '" & Request.Form("Implementations") & "' " & _
    ", Recovery = '" & Request.Form("Recovery") & "' " & _
    ", Resources = '" & Request.Form("Resouces") & "' " & _
    ", Impact = '" & Request.Form("Impact") & "' " & _
    ", Approved = '" & Request.Form("Approved") & "' "

Peter
------------------------------------------------------
Work smarter, not harder.

I also have an unique auto number index in the SQL table. Do I have to do anything with that? Can I use it to determine what recordsets get updated? I'm very new to this stuff, so I appreciate any help you can provide.

Thank you for what you have helped me with so far.

Try this. Your auto-number field in the table would be an Id value that you request. If you need to edit a specific record, this is necessary. With this query the code checks for an existing record that matches Id. If there is no match the program will add a new record. ALSO, in this example, your "Date" field was changed to "ThisDate" as "Date" is a reserved word.

<%
response.buffer = true

cn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\databases\yourdatabase.mdb"

Id = request("Id")
Requester = request("Requester")
WorkGroup = request("WorkGroup")) & "' " & _
Priority = request("Priority")
Title = request("Title")
ThisDate = request("ThisDate")
Target = request("Target")
Category = request("Category")
Assignee = request("Assignee")
Justification = request("Justification")
Components = request("Components")
Applications = request("Applications")
Hardware = request("Hardware")
Networks = request("Networks")
WorkGroups = request("WorkGroups")
Testing = request("Testing")
CompletedBy = request("CompletedBy")
Implementations = request("Implementations")
Recovery = request("Recovery")
Resources = request("Resouces")
Impact = request("Impact")
Approved = request("Approved")

set rs = server.createobject("adodb.recordset")
sql = "select * from CM"
sql = sql & " where Id = " & Id
rs.open sql cn, 3, 3
if rs.eof then
    rs.addnew
end if
    rs("Requester") = Requester
    rs("WorkGroup") = WorkGroup
    rs("Priority") = Priority
    rs("Title") = Title
    rs("ThisDate") = ThisDate
    rs("Target") = Target
    rs("Category") = Category
    rs("Assignee") = Assignee
    rs("Justification") = Justification
    rs("Components") = Components
    rs("Applications") = Applications
    rs("Hardware") = Hardware
    rs("Networks") = Networks
    rs("WorkGroups") = WorkGroups
    rs("Testing") = Testing
    rs("CompletedBy") = CompletedBy
    rs("Implementations") = Implementations
    rs("Recovery") = Recovery
    rs("Resources") = Resources
    rs("Impact") = Impact
    rs("Approved") = Approved
    rs.update

response.redirect "Change_Management_Description_of_Request.asp"
%>