SqlDataReader getting rows when no data

Question

This is incredibly urgent, I need to present this application in 3 and a half hours.

My application checks against a data source to see if a value exists in the database and changes values depending on whether or not the value in question was found. The problem is that I've run the sql query with the value in question in SSMS and no rows were returned, and yet, my DataReader says it has rows.

This means that my application is reporting inaccurately. Here's my code:

Using conn As New SqlConnection("Data Source=.\SQLEXPRESS; Initial Catalog=Testing;       Integrated Security=True;")
    Dim cmd As New SqlCommand(sql, conn)

    conn.Open()
    Dim reader As SqlDataReader = cmd.ExecuteReader

    If reader.HasRows Then
       Value = True
       AcctNumber = reader(1)
    End If

    reader.Close()
End Using

I've removed code that's not relevant to this post, but what you may want to know is:

Value is Boolean AcctNumber is a String

As this is an application for work, I'd rather not include the SQL Query. The problem is the reader. If I comment out Value = True, I get the right info, but leaving that out will mean that in a case where Value should be True, it'll report inaccurately as well.

Thanks in advance!

EDIT: Full Source Code:

Case "Business"
    ' Change the number format to local because that's what it is in the db
    If Microsoft.VisualBasic.Left(NumberToCheck, 2) = "27" Then
        NumberToCheck = NumberToCheck.Replace(Microsoft.VisualBasic.Left(NumberToCheck, 2), "0")
    End If

    Dim sql As String = "SELECT a.TelNumber, c.AccountNumber " & _
            "FROM TelInfo a " & _
            "INNER JOIN Customers b ON a.CustID = b.pkguidId " & _
            "INNER JOIN Accounts c ON b.pkguidId = c.CustID " & _
            "WHERE a.TelNumber = '" & NumberToCheck & "'"
    Using conn As New SqlConnection("Data Source=.\SQLEXPRESS; Initial Catalog=Testing; Persist Security Info=True; " & _
        "User Id=JoeSoap; Password=paoseoj;")
        Dim cmd As New SqlCommand(sql, conn)

        conn.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader

        If reader.Read Then
            Value = True
            AcctNumber = reader(1)
         End If

        reader.Close()
    End Using

On the comments below made before 08/02/10 (mm/dd/yy):

Value is just a boolean that gets returned by the function to indicate that the searched telephone number (NumberToCheck) exists in the database.

So...

Private AcctNumber As String

Dim val As Boolean = False
val = CheckNumber("3235553469")
If val Then
    ' AcctNumber will have been set by CheckNumber
    Label1.Text = AcctNumber
End If

val will only be returned True if the NumberToCheck (in this example 3235553469) exists in the database.

Having copied the value of NumberToCheck into SSMS and testing the query there, I can verify that the query does work as expected.

No, I can't populate a DataSet because of the volume of information in the table (+/- 9.5m rows). Even with the 'WHERE' filter, the query is too heavy on resources and eventually ends in an OutOfMemory Exception which is why I went with a DataReader.

I'm going to try the ExecuteScalar option as suggested as an answer by Darryl now, will update with the results.

I know the ConnectionString in the edit is different to the original. I had to change certain things for legal purposes... the information I'm working with is pretty sensitive. — Logan Young
– Logan Young, Commented Jul 29, 2010 at 10:03
if value = true, can you check the AcctNumber too? Is it a valid account number coming from your database? — Babar
– Babar, Commented Jul 29, 2010 at 10:39
Oh, and are you sure that "WHERE a.TelNumber = '" & NumberToCheck & "'" works as expected? What's the value of NumberToCheck you're testing with? — Anton Gogolev
– Anton Gogolev, Commented Jul 29, 2010 at 10:44

Community · Accepted Answer · 2017-05-23 12:27:28Z

3

Try changing your If statement to

If reader.Read() Then
   Value = True
   AcctNumber = reader(1)  
End If

HasRows exhibits strange behavior in certain situations, so it's better to avoid it altogether.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Jul 29, 2010 at 9:23

Anton Gogolev

116k39 gold badges204 silver badges293 bronze badges

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

5 Comments

Alex Humphrey Over a year ago

+1 - it's basically breaking because the reader hasn't been moved to the first row.

Logan Young Over a year ago

Good suggestion, but no improvement

Anton Gogolev Over a year ago

@Logan Make sure you're using correct sql and are issuing this statement against a correct DB -- there's nothing else that can go wrong here.

Logan Young Over a year ago

I just don't get it... the sql is correct because it works when I run it direct on the db, and I copied the connection string from an application that I know works.

Anton Gogolev Over a year ago

@Logan I think it will be better if you posted full source code here.

Darryl Peterson · Accepted Answer · 2010-07-29 12:32:46Z

1

This does not solve the problem, but it may get you through your presentation. Use "ExecuteScalar" which should work when you return a single value.

    AcctNumber = ""
    AcctNumber = cmd.ExecuteScalar
    If AcctNumber = "" Then
        Value = False
    Else
        Value = True
    End If

answered Jul 29, 2010 at 12:32

Darryl Peterson

2,2701 gold badge17 silver badges13 bronze badges

Comments

egrunin · Accepted Answer · 2010-07-29 11:22:13Z

0

Just to be sure it's not a data problem, parameterize your SQL. Change the statement:

Dim sql As String = "SELECT a.TelNumber, c.AccountNumber " & _
        "FROM TelInfo a " & _
        "INNER JOIN Customers b ON a.CustID = b.pkguidId " & _
        "INNER JOIN Accounts c ON b.pkguidId = c.CustID " & _
        "WHERE a.TelNumber = @telNumber"

Then do this:

Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@telNumber", NumberToCheck) ' <==== added line

Edit

I think we're looking in the wrong place. Add this just before if reader.Read():

Value = false
AcctNumber = ""

This will make sure you're not having a variable-scope problem. (You are using Option Strict On, right?)

edited Jul 29, 2010 at 11:22

answered Jul 29, 2010 at 9:58

egrunin

25.2k8 gold badges53 silver badges96 bronze badges

3 Comments

egrunin Over a year ago

We might be having an overlapping-edit problem, your code still looks like "'" & NumberToCheck & "'"...?

Logan Young Over a year ago

Um... no, I didn't add that edit, my comment was on your post suggesting I obfuscate the code a little so that I can post...

Logan Young Over a year ago

OK, I've added the parameter with no improvement

Logan Young · Accepted Answer · 2010-08-02 07:57:10Z

0

In the immortal words of Homer Simpson... "DOH!!"

I just had a look on the database that my application searches for the telephone numbers and, I don't know how I could have missed it, but the value I'm searching for is actually there.

The problem is, therefore, not likely anything to do with my code, but rather the data...

answered Aug 2, 2010 at 7:57

Logan Young

4311 gold badge7 silver badges23 bronze badges

Collectives™ on Stack Overflow

SqlDataReader getting rows when no data

4 Answers 4

5 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related