SQL syntax error??
Hi. Not sure if I've posted here before, but anyway. Got a really simple bit of asp that keeps throwing an error - "Syntax error in FROM clause". Now I can't see anything wrong in the SLQ string and I've no idea what's causing it. Can anyone take a quick look and possibly provide some suggestions? Cheers.
<%
Option Explicit
Dim objConn
%>
<%
Dim objRS, strName, strEmail, strPass, x, adOpenDynamic, adLockOptimistic, adCmdTable, strSQL
adOpenDynamic = 2
adLockOptimistic = 3
adCmdTable = 2
strName = Request("regname")
strEmail = Request("regemail")
strPass = Request("regpass")
strSQL = "SELECT * FROM users WHERE userID='" & strName & "' OR useremail='" & strEmail & "' OR userpass='" & strPass & "';"
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Provider="Microsoft.Jet.OLEDB.4.0"
objConn.Open "databaselink.mdb"
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, adOpenDynamic, adLockOptimistic, adCmdTable
if objRS.EOF then
objRS.AddNew
objRS("userID") = strName
objRS("useremail") = strEmail
objRS("userpass") = strPass
objRS.Update
else
do until objRS.EOF
for each x in objRS.fields
response.write(x.name)
response.write(" = ")
response.write(x.value)
next
objRS.MoveNext
loop
end if
%>[Edited by Peter J. Boettcher on Feb. 21, 2001 at 08:54 AM]
-hello mum-
Peter J. Boettcher posted this at 14:00 — 21st February 2001.
They have: 812 posts
Joined: Feb 2000
Vader D,
Your code looks ok. I edited your post to take out the name of your database, you have to be a little careful with Access databases since they can be downloaded to the client if they know the URL.
There is a small bug in ADO, sometimes when you say "SELECT *" it doesn't work properly (very rare). Try changing your "SELECT *" to "SELECT userID,useremail,userpass" and add whatever other fields you need in your result.
To help debug try using a simple select statement without the WHERE or OR. If that works, add the WHERE, and if that works add the OR. This will help you find the specific problem.
PJ | Are we there yet?
pjboettcher.com
Vader, D posted this at 15:14 — 21st February 2001.
They have: 3 posts
Joined: Feb 2001
Thanks very much, I'll try that. On the subject of, well, security in general - I'm becoming increasingly aware of the many possible holes I can leave open on an ASP site that is (read: will eventually be) almost entirely driven by an Access database. Apart from not sticking the path up in coding forums (oops
), do you know of any asp sites that cover stuff like that - validating for malicious form submissions, protecting the database, etc? Most of the ASP articles I read seem to concentrate on speedier code and why session variables are Satan's little helpers - very little along the lines of "This Is What Your Beginning ASP Book Left Out"
Cheers again,
um, Darth.
(please, call me Anakin..)
-hello mum-
Peter J. Boettcher posted this at 15:30 — 21st February 2001.
They have: 812 posts
Joined: Feb 2000
Anakin,
If your site is going to be mostly database driven and you're using Access as your database backend, you're going to have problems. Access start's to really chug at around 25-30 (depending on the queries) simultaneous users and basically dies at anything over 50 (once again depending on queries). If your site is never going to be that busy then I guess it's not a problem.
As for security, that's another reason not to use an Access database
. You can minimize your risk's by adding a password to the database and including that in your connection string. Also, instead of always putting the path to your .mdb file in every page you could store that in an Application variable (need access to global.asa) and include that variable on every page instead. That way if you ever paste you code on a forum the link won't show up 
I don't really know of any beginner ASP sites but some good ASP sites in general are:
http://www.asp101.com
http://www.aspin.com
http://www.4guysfromrolla.com
http://www.ultimateasp.com
Have fun!
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.