CODEWITHSUNDEEP
vb.netsqldatareader
Andrew Punio
Andrew Punio

Reputation: 13

Failed to read when no data is present

i have this code,,its work (kind of).

Dim connString As String = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString
    Dim conn As New SqlConnection(connString)
    conn.Open()
    Dim comm As New SqlCommand("SELECT username, Password,type   FROM users WHERE username='" & TextBox1.Text & "' AND Password='" & TextBox2.Text & "'", conn)
    Dim reader As SqlDataReader
    reader = comm.ExecuteReader

    Dim count As Integer
    count = 0
    While reader.Read
        count = count + 1
    End While
    If count = 1 Then
        MessageBox.Show("username and password are correct")


        Form2.Show()


        Form2.Label1.Text = Me.TextBox1.Text
        Form2.Label2.Text = reader(2).ToString
    ElseIf count > 1 Then
            MessageBox.Show("username and password are duplicated")
        Else
            MessageBox.Show("username and password are wrong")

    End If

im getting error with this line: 

Form2.Label2.Text = reader(2).ToString
and error is "Invalid attempt to read when no data is present"

why its says "no data"

i have all data in database?

can someone help me to correct this code? thank you ..

Upvotes: 0

Views: 55

Answers (2)

Stephen Wrighton
Stephen Wrighton

Reputation: 37839

A SqlDataReader is a forward only data read element. The error is occurring because you're calling the reader's READ function twice; once as true to increment to 1, and a second time to get a false to fall out of the while statement. Since you're no longer in the WHILE statement, the reader had to have read the end of the result set, thus there is no data for you to read.

Consider the changed code below: 

Dim connString As String = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString
Dim count As Integer = 0 
Dim userType as string = ""

Using conn As New SqlConnection(connString)
    conn.Open()
    Using Comm as SqlCommand = conn.CreateCommand 
       comm.commandText = "SELECT username, Password, type FROM Users WHERE username = @UserName AND Password = @Pwd; "
       comm.parameters.AddWithValue("@Username", TextBox1.Text) 
       comm.parameters.AddWithValue("@Password", Textbox2.text) 

       Dim reader As SqlDataReader
       reader = comm.ExecuteReader

       If reader IsNot Nothing Then 
          If reader.HasRows() Then 
             While reader.read 
                count = count + 1 
                If Not reader.IsDbNull(2) Then userType = reader(2).ToString 
             End While 
          End If 
          If Not reader.IsClosed Then reader.close
          reader = Nothing 
       End If 
    End Using 
End Using
If count = 1 Then
    MessageBox.Show("username and password are correct")
    Form2.Show()

    Form2.Label1.Text = Me.TextBox1.Text
    Form2.Label2.Text = userType 
 ElseIf count > 1 Then
    MessageBox.Show("username and password are duplicated")
 Else
    MessageBox.Show("username and password are wrong")
 End If

First off, SQLParameters are your friend. Learn them. They are the single easiest way to fight against SQL Injection when using the SqlClient classes.

Secondly, notice that I'm doing the actual retrieval of the data from the reader inside the WHILE loop. This ensures that there's actual data for me to read.

Third, notice the USING statements on the SqlConnection and SqlCommand objects. This helps with garbage collection, and has a couple of other benefits as well.

Finally, notice the checks I'm doing on the SqlDataReader before I ever attempt to access it. Things like that would prevent from another error appearing if you did not return any results.

Upvotes: 0

jmcilhinney
jmcilhinney

Reputation: 54427

You should not be using a loop at all. There should be no way that you can get more than one record so what use would a loop be? You should be using an If statement and that's all:

If reader.Read() Then
    'There was a match and you can get the data from reader here.
Else
    'There was no match.
End If

If it's possible to have two records with the same username then there's something wrong with your database design and your app. That column should be unique and your app should be testing for an existing record when someone tries to register.

Upvotes: 1

Related Questions