conn.Execute(SQL) error

[SteveH]

Hello

I am getting the following error which trying to use an INSERT SQL statement.

The error is this:

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''message'.

/ASP_Operations.asp, line 31

line 31 refers to conn.Execute(SQL) in the following code:

<%

Dim conn,rs,SQL,myMail,name,staffID,email,subject,campus,message

'Open MS Access database, store form field values

set conn=Server.CreateObject("ADODB.Connection")

conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("my_database.mdb")& ";"

name = Request.Form("name")
staffID = Request.Form("staffID")
email = Request.Form("email")
subject = Request.Form("subject")
campus = Request.Form("campus")
message = Request.Form("message")

SQL="INSERT INTO users (name, staffID, email, subject, campus, message) VALUES ('" & _
name & "', '" & staffID & "','" & email & "', '" & subject & "', '" & campus & "'message"')"

conn.Execute(SQL)

I have declared conn as a variable, so I am unsure why the server is pointing to that as an error.

Many thanks.

Steve

[Old Pedant]

It's not the conn.execute, per se. That is clearly saying you have an error in your SQL.

If you would debug, you'd see that is true.

For example, do a
Response.Write "DEBUG SQL: " & SQL & "<HR>"
*just* before the conn.Execute.

And then take the SQL that the debug shows you and try to execute it directly in Access. Access would give you the same error and *probably* also point out where the error is.

It happens that this error is easy to spot even without trying it in Access:

SQL = "INSERT INTO users (name, staffID, email, subject, campus, message) " _
& VALUES ('" & name & "', '" & staffID & "','" & email & "', '" & subject & "', '" & campus & "', '" & message & "')"


But you also didn't show us your *ACTUAL* code, because the code you showed us would get a VBScript syntax error and wouldn't have gotten as far as letting Access find an error. If you count your " marks, you get an odd number (13, in fact). Impossible. You can't write legal VBScript (or most any language) code that has an odd number of " marks. So next time please copy/paste your actual code.

[SteveH]

Hello OP

Sorry, I thought the error referred to the 'Execute' (although I have got things working with that before) - that's why I didn't post the rest of the MDB-related script.

Thanks for letting me know about the odd number of " (quotation marks) - I didn't know that, but it does make sense (pairs, etc).

This is what I have now:


<%

Dim conn,rs,SQL,myMail,name,staffID,email,subject,campus,message

set conn=Server.CreateObject("ADODB.Connection")

conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("my_database.mdb")& ";"

name = Request.Form("name")
staffID = Request.Form("staffID")
email = Request.Form("email")
subject = Request.Form("subject")
campus = Request.Form("campus")
message = Request.Form("message")

SQL = "INSERT INTO users (name, staffID, email, subject, campus, message) " _
& VALUES ('" & name & "', '" & staffID & "','" & email & "', '" & subject & "', '" & campus & "', '" & message & "')"

'Response.Write "DEBUG SQL: " & SQL & "<HR>"
conn.Execute(SQL)

conn.Close
Set conn=Nothing

Set myMail=CreateObject("CDO.Message")

In red is what you have given me, thank you.

Yes, I still get an error:

Microsoft VBScript compilation error '800a03ea'

Syntax error

/ASP_Operations.asp, line 28

& VALUES ('" & name & "', '" & staffID & "','" & email & "', '" & subject & "', '" & campus & "', '" & message & "')"
----------^


The error is pointing to the first single apostrophe after VALUES.

Steve

[Old Pedant]

You now have 15 " marks.

You are missing the one in front of VALUES.

SQL = "INSERT INTO users (name, staffID, email, subject, campus, message) " _
& " VALUES ('" & name & "', '" & staffID & "','" & email & "', '" & subject & "', '" & campus & "', '" & message & "')"

My fault. I zapped it when I rearranged your code.

But, still, if you'd counted the " marks you would have known something is wrong.

Note, by the way, that now the error is a VBScript syntax error. Which is actually what I would have expected from the code you showed in the first post. Which is why I don't think you did a true copy/paste when showing that code.