why this right looking query doesn't work? 1

[chrisdq]

Hi,

In the access database I have a table called records with the following datum.

ID Name Username Password
1 Chris chris yeehaa
2 Dell dell dell


For some reason, this SQL query
rs.Open "SELECT Password FROM records WHERE Username='username'", conn

doesn't work from ASP but it works from SQL analyzer. Could someone tell me why? The following codes is from the whole asp page. Thanks in advance.

<%

username = request.form("username")
password = request.form("password")
correctpass = 0

Response.Write("Checking the access database for the password of username " & username & "<br>")

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:/Inetpub/

http://wwwroot/ODBC.mdb"

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT Password FROM records WHERE Username='username'", conn

do until rs.EOF
for each x in rs.Fields
if password = x then
correctpass = 1
end if
next

Response.Write("<br>")
rs.MoveNext
loop

rs.close
conn.close

if correctpass = 1 then
Response.Write("<br>Correct password</br>")
else
Response.Write("<br>wrong password</br> ")
end if

%>

 

[Sashanan2]

I've found that when queries work in one program but not in another, the barrier is usually restricted words. I wouldn't be surprised if Password is a restricted keyword in ASP and not properly read as part of your query. Try enclosing it in brackets like [Password] and see if that helps. If that's not it, it might be one of the other words (Username, perhaps); I haven't worked with ASP before so I can't say for sure.


"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie

 

[PHV]

rs.Open "SELECT Password FROM records WHERE Username='" & username & "'", conn

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DotNetGnat]

this shud do the trick

rs.Open "SELECT [Password] FROM records WHERE [Username]='username'", conn

Password and Username are the keywords...

-DNG

 

[PHV]

DNG, you too are confusing literal and variable ?

 

[DotNetGnat]

PHV,

i thought he might have a username named username and there is nothing wrong with it...is it....

if username is a variable then he has to use...

rs.Open "SELECT [Password] FROM records WHERE [Username]='"&username&"' ", conn

i assumed that he has a username named username

-DNG

 

[chrisdq]

Thank you all for all of your help.

PHV: your codes solved my problem.
Somehow when I type my query
rs.Open "SELECT Password FROM records WHERE Username='username'", conn
in SQL analyzer, it returns the password matching with the one input from the user. That's where I got confused.

So I guess specifying 'username' is considered a string in ASP not variable right?

Thanks again for the great tips.

 

[PHV]

is considered a string in ASP not variable
In fact, a literal constant.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886