ASP error

[man_im_bored]

Yeah, I'm starting to learn ASP, and what I'm trying to create is a login. I've set up the db and created a way for people to add a username and password to the db, but I can't get someone to "login" which is a pain. In the db, I've got two colums: 1)username and 2)passcol - which is the password column.

It connects successfully and everything, just doesn't run the query.

Here's the error:
'Technical Information (for support personnel)

'Error Type:
'Microsoft JET Database Engine (0x80040E14)
'Characters found after end of SQL statement.
'/programming/asp/login/login_engine.asp, line 34


- - - - - - - - - - - - - - - - - - - - - CODE - - - - - - - - - - - - - - - - - - - - - -

<%
Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file

Dim strSQL ' The SQL Query we build on the fly
Dim strUser ' Username
Dim strPass ' Password

strUser= Request.Form("username")
strPass= Request.Form("passbox")


If strUser <> "" Then
If strPass <> "" Then

strDBPath = Server.MapPath("db.mdb")


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



cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\programming\asp\login\db.mdb;"

' Build our query based on the input.
strSQL = "SELECT username, passcol " _
& "FROM usertable " _
& "WHERE username = strUser;" _
& "AND passcol = strPass "

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = cnnSearch.Execute(strSQL)

Do While Not rstSearch.EOB

Do While Not rstSearch.EOF

Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
Response.Redirect("failed.asp")
Loop
Loop
Response.Redirect("success.asp")
End If
else
Response.Redirect("failed.asp")
End if

%>


:D

[Kodo]

'apostrophe replacement helps avoid SQL injection as well as errors with strings being passed to the SQL statement.

strUser=replace(request.form("username"),"'","''")
passcol=replace(request.form("passbox"),"'","''")

strSQL = "SELECT ID FROM usertable WHERE username = '"&strUser&"' AND passcol = '"&strPass&"' "

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = cnnSearch.Execute(strSQL)

if not rstSearch.eof then
session("ID")=RS("ID")

rstSearch.close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing

Response.Redirect("success.asp")

else

rstSearch.close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing


Response.Redirect("failed.asp")
End if

the loop you had would have caused problems. Now, if you want to maintain some security after they've logged in or if you just want to make sure they're logged in, I added a session variable that holds the ID of the user's record. You need to do this at the top of your pages or make a function and call it at the top of your pages.

if session("ID")="" or isnull(Session("ID") then
response.redirect "login.asp
end if

this will check for the sessions state and maintain that persons log in until the browser is closed or until you use session.abandon.

[man_im_bored]

Thanks, that helps heaps. I was going to use cookies instead of the id thing, but might as well use that. Thanks again.