Invalid SQL error given for Open statement

RecordSet open gives "Invalid SQL Statement" error

[evilracecarlive]

I am trying to establish a connection to an Access DB and write a new row into a table with a declared variable. I get this error pointing to the "objRS.Open" line.

Could anyone take a look and let me know what I am missing? I would greatly appreciate it. I have scoured books and online forums but cant seem to figure out what is causing this error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/Default.asp, line 14

Browser Type:
Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.2; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET CLR 1.0.3705; .NET CLR 1.1.4322; MS-RTC LM 8)

Here is all of the code

<%
'Declare the variables for the connection
Dim objConn, URL, DBQ, objRS, objCMD
URL=Server.MapPath("../fpdb/id.mdb")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="& URL) 
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "PageVisits", objConn
Dim urlID
urlID = Request.QueryString("ID")
Set obCMD = Server.CreateObject("ADODB.Command")
objCMD.ActiveConnection = objRS
objCMD.Commandtext = "INSERT INTO PageVisits (URLID) VALUES (urlID)"
objCMD.CommandType = adCmdText
objCMD.Execute 
%>

The idea here is that a unique ID is contained in the URL and it will be captured and dropped into a db.

Thanks in advance for any help that can be offered!

[syber]

I believe that your VALUES clause must have the actual value in, not a variable.

"INSERT INTO PageVisits (URLID) VALUES (" + urlID + ")"

[evilracecarlive]

Thanks for your fast reply, syber.

I made your suggested change, but it didnt seem to make any difference. I'm still getting the exact same error.

Any other suggestions?

[lukesimswilson]

How is the database set up? is the URLID a integer or a varchar field?

[lukesimswilson]

If the URLID is an integer i would write the asp like this:

<%
'Declare the variables for the connection
Dim objConn, URL

URL=Server.MapPath("../fpdb/id.mdb")

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="& URL)

Dim urlID
urlID = Request.QueryString("ID")

objConn.execute("INSERT INTO PageVisits (URLID) VALUES (" & urlID & ")")

set objConn = Nothing

%>

[evilracecarlive]

luke thank you that seemed to do the trick. avoiding the objRS and objCMD must have been what it needed, (though I am not sure why). either way, it is simpler code which I like :)

on another note, I noticed a problem with my "URL=Server.MapPath("../fpdb/id.mdb")"

the leading "../" has to be left out
"URL=Server.MapPath("fpdb/id.mdb")"

so now it works! there is a bit of customization I need to do, but now that I have the basic script running I should be able to figure it all out.

Thank you both for your help.