We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Data Reader No Record Found Logic

DeniseGoodheart
on
Medium Priority
328 Views
Last Modified: 2012-05-05
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
Comment
Watch Question

Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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

Author

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

Author

Commented:
Hi appari,

Your code worked great!

Many Thanks,
Denise
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.