Invalid attempt to read data even when data is present in SqlDataReader in vb.net

Question

Here's my code which gives error, the query returns value for the particular item.

Also in the database side the query return rows even I have put condition that if reader has rows then only assign it to a variable but still it throws an error eg.

dqty = sqlreader("qty")

Code:

Private Function checkquantity(ByVal code As String, ByVal quan As Integer) As Boolean
    sqlcommand.CommandText = "select sum(qty) as qty from pos_stock_balance where item_code='" & code & "'"
    sqlcommand.Connection = AppsCon
    sqlreader = sqlcommand.ExecuteReader
    If sqlreader.HasRows Then

        dqty = sqlreader("qty")
        sqlreader.Close()

    Else
        sqlreader.Close()
    End If
    If quan > dqty Then
        Return False
    Else
        Return True

    End If
End Function

Rajaprabhu Aravindasamy · Accepted Answer · 2013-11-14 11:51:01Z

7

It is because you are directly accessing the data without reading it, Try this,

If sqlreader.HasRows Then
      If sqlreader.read()
        dqty = sqlreader("qty")
        sqlreader.Close()
       End If
Else
       sqlreader.Close()
End If

Reference

Cleaned version of your code,

Private Function checkquantity _
(ByVal code As String, ByVal quan As Integer) As Boolean

    try

    sqlcommand.CommandText = "select" _
    & "sum(qty) as qty from pos_stock_balance where item_code='" & code & "'"

    sqlcommand.Connection = AppsCon
    sqlreader = sqlcommand.ExecuteReader

    If sqlreader.read() Then
         dqty = sqlreader("qty")
    End If

    If quan > dqty Then
        Return False
    Else
        Return True
    End If

    Finally
       sqlreader.Close()
    End try

End Function

Although i cleaned your code, Your code is still vulnerable to sql injection. Try to use parameterised queries to avoid that

edited Nov 14, 2013 at 11:51

answered Nov 14, 2013 at 11:28

Rajaprabhu Aravindasamy

67.2k17 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rajaprabhu Aravindasamy Over a year ago

@Ric Catch is optional.

Ric Over a year ago

Fair enough, thought you may want to do something should en exception get raised. Nevermind.

vimal vasudevan Over a year ago

@Rajaprabhu Aravindasamy :could you tell me in detail difference between sqlreader.hasrows and sqlreader.read

Rajaprabhu Aravindasamy Over a year ago

@vimalvasudevan I hope this link will clarify you doubt. forums.asp.net/t/1175522.aspx

Wayne Barron Over a year ago

Thank you, this is what I needed to insert the record.

Ric · Accepted Answer · 2013-11-14 11:32:30Z

2

If you are simply returning a scalar value use:

dqty = CType(sqlcommand.ExecuteScalar(), Integer)
...
If quan > dqty Then
    Return False
Else
    Return True    
End If

This returns an object which can be casted to the necessary type allowing your comparisons at the end of the code to continue as normal without the need for a SqlDataReader at all. But beware that as your sql is not wrapped in an ISNULL(), the value returned could be null in which case you may wish to check for this. As a further note, use parameterized queries!

answered Nov 14, 2013 at 11:32

Ric

13.3k3 gold badges32 silver badges36 bronze badges

1 Comment

vimal vasudevan Over a year ago

i saw your answer now.i had already voted for that answer and after that yours i got update now..well both answers satifsy my question and well yours allows me to remove unnecessary declarations and code..well its a matter of choice...i cant mark both as answers right ric.. well thanks for the support answer dear....

Collectives™ on Stack Overflow

Invalid attempt to read data even when data is present in SqlDataReader in vb.net

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related