Returning a value when statement is true SQL and ASP 1

[UnfitElf]

Hi all..
First i would just like to say im a beginner at all this SQL stuff... BUT, i have a small prob. I am creating a login page in asp but am using a database so thats were the SQL come in. I need it to search the database and return the value 1 if the username is equal to the username in the database, and if the password is equal to the password from the database. I am getting the username and password of a form i have created.

The section of code i an having problems with is this:

<%
sSQL = &quot;select 1 from users where username = '&quot; & request(&quot;Username&quot & &quot;' and password = '&quot; & request(&quot;Password&quot & &quot;'&quot;

Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot
oConn.Open &quot;DSN=myDatabase&quot;
oConn.execute sSQL
oConn.Close
Set oConn = Nothing
response.write(sSQL)'This is to see if the value of sSQL is actually = &quot;1&quot;. Currently it is not as something is going wrong.
%>

How can i get the SQL to return the value 1 when the statement is true and make eighter the sSQL variable or any other variable equal to the one if the statement is true.

Thanks for any help

 

[JohnBates]

Hi UnfitElf !

If you just need to find out if the user and pw is valid.... can I suggest a minor change to your method ?
Execute your sql statement in the .open, then check for the end-of-file condition.


dim rs, sql

<%

sSQL = &quot;select * from users where username = '&quot; & request(&quot;Username&quot & &quot;' and password = '&quot; & request(&quot;Password&quot & &quot;'&quot;

set rs = createobject(&quot;adodb.recordset&quot

rs.open sql,conn, adOpenStatic, adLockReadOnly, adCmdText

If NOT rs.eof Then
.
. do whatever because login was good
.
else
. it was as invalid login attempt
End If

%>


Maybe this would work for you ?
Hope this helps. John

 

[Tarwn]

I would actually suhhest combining the methods. Your method of executing the SQL string is actually much quicker, but checking for EOF is a very good way to finish the validation.
Try this:

<%
sSQL = &quot;select username from users where username = '&quot; & request(&quot;Username&quot;) & &quot;' and password = '&quot; & request(&quot;Password&quot;) & &quot;'&quot;

Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
oConn.Open &quot;DSN=myDatabase&quot;
Set rsValidation = oConn.execute sSQL
If rsValidation.EOF Then
   Response.Write &quot;Invalid Username/Password!&quot;
   Response.End  'stops outputting here if invalid
End If

oConn.Close
Set oConn = Nothing
Set rsValidation = Nothing
%>

The execute method is more efficient than doing a .Open so this should be a little quicker (though both should be unnoticeable to a person).

-Tarwn Experts are only people who have realized how much they will never know about a language.
________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[darpankumar]

it will store 1 in rs.Fields(0) rather than in sSQL. sSQL is only the string variable to store the query.

So,

Response.Write(rs.Fields(0))

Best Regards,

Darpan Kumar Gogia
aviint@yahoo.com

 

[Tarwn]

There isn't a recoreset specified in the original post, so that wouldn't work unless you added a recordset and set the recordset equal to the conn.execute statement. The problem with this is if there wasn't a record it will return the error &quot;Either EOF or BOF is true&quot;.

-Tarwn Experts are only people who have realized how much they will never know about a language.
________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924