homepage Welcome to WebmasterWorld Guest from 54.196.225.45
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Invalid SQL error given for Open statement
RecordSet open gives "Invalid SQL Statement" error
evilracecarlive




msg:3920548
 8:07 pm on May 26, 2009 (gmt 0)

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




msg:3920562
 8:23 pm on May 26, 2009 (gmt 0)

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

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

evilracecarlive




msg:3920639
 12:12 am on May 27, 2009 (gmt 0)

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




msg:3920908
 2:11 pm on May 27, 2009 (gmt 0)

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

lukesimswilson




msg:3920993
 4:08 pm on May 27, 2009 (gmt 0)

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




msg:3921014
 4:39 pm on May 27, 2009 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved