asked on

SQL Datareader only checks first line in database

Hi

I am building a .net website and want to be able to use a login control to access a page where the username and password are taken from an existing database.

The code I have used is below:

Now this works great.... as long as you are the first person in the database table. For some reason the datareader doesn't read all the rows to match the username and password entered.

Any help would be greatly appreciated.

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As AuthenticateEventArgs) 
    Dim Authenticated As Boolean = False 
    Authenticated = SiteLevelCustomAuthenticationMethod(Login1.UserName, Login1.Password) 
    e.Authenticated = Authenticated 
    If Authenticated = True Then 
        Response.Redirect("Home.aspx") 
    End If 
End Sub 
 
Private Function SiteLevelCustomAuthenticationMethod(ByVal UserName As String, ByVal Password As String) As Boolean 
    Dim boolReturnValue As Boolean = False 
    ' Insert code that implements a site-specific custom 
    ' authentication method here. 
    ' This example implementation always returns false. 
    Dim strConnection As String = "server=dtpxp-skumari;database=master;uid=sa;pwd=;" 
    Dim Connection As New SqlConnection(strConnection) 
    Dim strSQL As String = "Select * From Employee" 
    Dim command As New SqlCommand(strSQL, Connection) 
    Dim Dr As SqlDataReader 
    Connection.Open() 
    Dr = command.ExecuteReader() 
    While Dr.Read() 
        If (UserName = Dr("name").ToString()) And (Password = Dr("Password").ToString()) Then 
            boolReturnValue = True 
        End If 
        Dr.Close() 
        Return boolReturnValue 
    End While 
End Function

Open in new window

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dirk Haest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

imitchie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imitchie

replace

Dim strSQL As String = "Select * From Employee"
Dim command As New SqlCommand(strSQL, Connection)
Dim Dr As SqlDataReader
Connection.Open()
Dr = command.ExecuteReader()
While Dr.Read()
If (UserName = Dr("name").ToString()) And (Password = Dr("Password").ToString()) Then
boolReturnValue = True
End If
Dr.Close()
Return boolReturnValue
End While

with

Dim strSQL As String = "Select [name] From Employee where [name] = '" + Replace(UserName, "'", "''") + "' and [Password]= '" + Replace(Password, "'", "''") + "'"
Dim command As New SqlCommand(strSQL, Connection)
Dim Dr As SqlDataReader
Connection.Open()
Dr = command.ExecuteReader()
boolReturnValue = Dr.Eof
Dr.Close()
Return boolReturnValue

imitchie

grr... time tosleep

    Dim strSQL As String = "Select [name] From Employee where [name] = '" + Replace(UserName, "'", "''") + "' and [Password]= '" + Replace(Password, "'", "''") + "'"
    Dim command As New SqlCommand(strSQL, Connection)
    Dim Dr As SqlDataReader
    Connection.Open()
    Dr = command.ExecuteReader()
    boolReturnValue = not Dr.Eof
    Dr.Close()
    Return boolReturnValue

Open in new window

DJFuller

ASKER

Oh my god.. what an idiot I am..

Thanks all of you... I guess I can share points out.

imitchie: what does adding break do?

Guy Hengel [angelIII / a3]

>imitchie: what does adding break do?
it finishes the while immediately. as at that place, you knew the username/password is correct, no need to loop further in the DataReader.

the suggestion of imitchie to include the username/password test directly in the query is obviously the most efficient method, but could even be made better by using sqlcommand and sqlparameter objects ... but that is another discussion. just read up on those key terms will help you coding alot.