SQLDataReader

[stevenr]

Hope this is in the right forum.

I have a page with a datareader which selects data from a database using the select command depending on the username, this then calls the password and checks it.

My problem is that when the userid isn't in my SQL database I get an error saying "no data found".
How can I put a check in so that when there is no data it returns a message?

My code so far is:

dim dr as sqldatareader = strsql.executereader
dr.read()
if dr("password&quot = password.text then
response.write("OK&quot
else
response.write("PWord Error&quot
end if

 

[gagz]

dim dr as sqldatareader = strsql.executereader
if (dr.read) then
    if dr("password") = password.text then
        'do something
    end if
    response.write("OK")
else
    response.write("PWord Error")
end if

if i understand you correctly... this assumes that by virtue of there being no id, you mean there is no record at all... hth

 

[stevenr]

Thats right, if the name searched for doesn't exist in the table then I get an error, I wanted a way that if it this happens then it tells you politely

 

[jmeckley]

another way to read through the datareader is:

[tt]dim dr as sqldatareader = strsql.executereader
While dr.read()
if dr("password&quot = password.text then
response.write("OK&quot
else
response.write("PWord Error&quot
end if
End While[/tt]

Jason Meckley
Database Analyst
WITF

 

[jmeckley]

1 more thing, don't forget to close the data reader! You will get errors if you try to execute other statements against the database

[tt]dr.close()[/tt]

Just a thought, but why not pass the username and password to a sql statement, if the record count of the data reader is > 0 then the user is OK. If it's not > 0 then the data doesn't exist.

If each username and password combination is unique you can use a scalar command which uses less resouces than a data reader. A scalar command can only be used when 1 value is returned. Here is an example:
[tt]
Dim SqlStr as SqlCommand
Dim Valid as Integer

With SqlStr
.CommandType = CommandType.Text
.CommandText = "Select 1 from

where username='" & UsernameTextField.text & "' and password='" & PasswordTextField & '"
.Connection = Cnn
End With

Valid = SqlStr.ExecuteScalar

If Valid = 1 Then
Response.Write("OK&quot
Else
Response.Write("Access Denied&quot
End If
[/tt]
HTH

Jason Meckley
Database Analyst
WITF