objCmd.Execute error

nnrin · October 3rd, 2007, 07:22 AM

Hi I got this error when I

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/executefiles/TryItOut-4.asp, line 181

and Line 181 is objCmd.Execute

This is the code:

Quote:

quote:'Create and open the database object

Quote:

    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "DSN=FILES"

    'Create the command object
    Set objCmd = Server.CreateObject("ADODB.Command")

    'Set the command object properties
    Set objCmd.ActiveConnection = objConn
    objCmd.CommandText = strSQL
    objCmd.CommandType = adCmdText

    'Execute the command
    objCmd.Execute

    'Display the update string
    Response.Write "The following update string was executed and " & _
        "the values updated in the Clubs table.<P>"
    Response.Write strSQL

End If 'End If for step processing

'Close and dereference database objects
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>

Thanks guys

Dharam Pal Sikhwal · October 3rd, 2007, 07:40 AM

Hi,

Please give update string.

Give code where u store update string in strSQL variable.

nnrin · October 3rd, 2007, 08:00 AM

Quote:

quote:Originally posted by Dharam Pal Sikhwal
Hi,

Please give update string.

Give code where u store update string in strSQL variable.

Thanks Dharam for responding, I'm fairly new to this,
Anyhow, the following is the strSQL:

Code:

ElseIf Request.Form("FormAction") = "Step3" Then

    'Build the update string
    strSQL = "Update ACCOUNT_REGISTRATION Set " & _
        "PROJECT = " & _
            CSTR(Request.Form("project")) & ", " & _
        "GROUP = " & _
            CSTR(Request.Form("group")) & ", " & _
        "EMAIL = " & _
            CSTR(Request.Form("email")) & ", " & _
        "PHONE = " & _
            CSTR(Request.Form("phone")) & ", " & _
        "EXTENSION = '" & _
            CStr(Request.Form("extension")) & "' " & _
        "Where USERNAME = '" & _
            CStr(Request.Form("username")) & "'"

woodyz · October 3rd, 2007, 10:26 AM

As a test, I suggest you output strSql to response.write without trying to process it so you can see the string you are building and check it for correctness. It looks to me that it is very likely that you are not properly enclosing some string values in sngle quotes. You do this with EXETNSION and USERNAME, but not with email, phone, group, and project. If any of these are strings (as I suspect they are) you need to use the single quotes.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems

dparsons · October 3rd, 2007, 10:33 AM

To illustrate Woody's point:

If your SQL Statement literally equates to something like:
UPDTE Account_Registrations SET Project = myProject, Group = myGroup, Email = [email protected], Phone = MyPhone, Extenstion = myExtenstion WHERE UserName = 'foo'

SQL will throw an error since strings need to be delimited by ' '

Apply the same methodology you are using for your WHERE clause, e.g. you surround the Form value with ' ' to your set statement.

It would look something like this:
UPDTE Account_Registrations SET Project = 'myProject', Group = 'myGroup', Email = '[email protected]', Phone = 'MyPhone', Extenstion = 'myExtenstion' WHERE UserName = 'foo'

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

woodyz · October 3rd, 2007, 01:01 PM

dparsons is correct about what I was saying.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems

dparsons · October 3rd, 2007, 01:33 PM

BTW, good to see you Woody, haven't seen you around in a while =]

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

woodyz · October 3rd, 2007, 01:53 PM

Hello Doug,

I've been very busy at work and speaking at conferences and programmers groups about Agile development. Work is good, but speaking has been great fun. I do miss posting here and doing this "distributed debugging" on little troubleshooting problems here. Hopefully I'll get a chance to pop in a little bit every day.

Nice to see you are still here. Looks like you've been busy!

Cheers,

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems

dparsons · October 3rd, 2007, 02:08 PM

Ya I have been doing a bit of work for Wrox over the course of the year, but it has been a good ride so far and it is keeping me up on all the latest and greatest =] So ya, I am still around, not quite a staple like Imar or Peter but I am still here!

Glad to hear you have been keeping busy and if I make it out to the West Coast I will have to look you up when you are speaking at a Users Group!

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

woodyz · October 3rd, 2007, 02:38 PM

Just let me know when.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems