Data Reader No Record Found Logic

Hi,

I’m using ASP.NET 2005 with VB.NET and SQL Server 2005.  The following code logic isn’t working when a record is not found:

While Rdr.Read
            If Rdr(2) <> Me.txtUserID.Text Then          ‘Record Not Found
                Me.lblStatus.Text = "Invalid User"
            Else
                Me.lblStatus.Text = "Welcome " & Rdr(2)
            End If
 End While

How can I fix this code or is there a better way to check if a record exists using the SQL Data Reader?

******Additional Information**********

Users Table:
UserID      UserLastName      UserFirstName      UsesrLogIn         UserPassword
1      Goodheart                      Denise                      Denise         DeniseG

Stored Procedure:
Create PROCEDURE [dbo].[spSelLogIn]
@UserLogIn as varchar(50),
@UserPassword as varchar(50)
As
Select UserID,UserLogIn, UserPassword from Users
WHERE @UserLogIn=UserLogIn and @UserPassword=UserPassword

Code:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEnter.Click
        Dim sSQL As String
        Dim sCon As String
        Dim sLoginID As String
        Dim sPassword As String

        sLoginID = "denise"
        sPassword = "deniseg"
        sCon = "Data Source=Gizmo;Initial Catalog=SandBox;User Id=" & sLoginID & ";Password=" & sPassword & ";"

        Dim Con As SqlConnection = New SqlConnection(sCon)

        'Create sSQL String
        sLoginID = Me.txtUserID.Text
        sPassword = Me.txtPassword.Text
        sSQL = "EXEC dbo.spSelLogIn '" & sLoginID & "','" & sPassword & "'"

        Dim Cmd As SqlCommand = New SqlCommand(sSQL, Con)
        Cmd.Connection.Open()
        Dim Rdr As SqlDataReader = Cmd.ExecuteReader()

        While Rdr.Read
            If Rdr(2) <> Me.txtUserID.Text Then
                Me.lblStatus.Text = "Invalid User"
            Else
                Me.lblStatus.Text = "Welcome " & Rdr(2)
            End If
        End While

        Cmd.Connection.Close()
End Sub

Thanks,
Denise
DeniseGoodheartAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
try

if rdr.hasrows then
While Rdr.Read
            If Rdr(2) <> Me.txtUserID.Text Then
                Me.lblStatus.Text = "Invalid User"
            Else
                Me.lblStatus.Text = "Welcome " & Rdr(2)
            End If
End While
else
       Me.lblStatus.Text = "Invalid User"

end if
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
irps20001Commented:
Hi
In my observation, You are comparing Rdr(2) with UserId.
Rdr(2) is representing PASSWORD from users table and you are comparing PASSWORD from database with the UserID that has entered from the Form.
I guess you have to change your code from Rdr(2) to Rdr(1) where Rdr(1) represents UserId/LoginID

Apart from that , your code is working Fine

HTH
Happy Programming
Rana
0
DeniseGoodheartAuthor Commented:
Hi Rana,

You are absolutely right about my incorrect test data.  My test data is actually Denise as the UserID and Denise as the UserPassword, and I accidently put DeniseG as the UserPassword for my question.  Anyway, your suggestion did not fix the problem, but raised a good point about using realistic test data.  My test data worked because all my values are “Denise.”

Thanks,
Denise
0
DeniseGoodheartAuthor Commented:
Hi appari,

Your code worked great!

Many Thanks,
Denise
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.