SqlDataReader reading beyond end of data

TheJudeDude

I received an error ( Invalid attempt to read when no data is present ) while reading data with a SqlDataReader. I went to the data and found the last record, then I debugged from that record. Code: sqlReader.Read(); //this is after the last row has already been read, should throw exception? if (!sqlReader.HasRows)//sqlReader.HasRows = true...?? After this it loops back to a while and tries to read data, in which I get the error. I would appreciate if someone could explain to me the error of my ways :D

Jude

Bassam Saoud

Do you have the following structure ?

while(sqlReader.Read())
{
// do reads here
}

Jimmanuel

HasRows doesn't tell you how many rows you haven't "Read()", it only tells you if there are rows in the reader. Read advances you to the next row, starting from one before the first; the first time you call it you're placed at the first row. To loop through all of the rows use this:

while (sqlReader.Read())
{
// do something with the row
}

For Reference: MSDN[^]

:badger:

Migounette

In fact you should use it in the other way: DataTableReader dataTableReader = myDatabase.CreateDataReader(); if (dataTableReader.HasRows) { while (dataTableReader.Read()) { Console.Out.Write(...); } } Read will returns a boolean and does not through an exception. Moreover it also should depend on your provider (MySQL, SQL Server...), the cursor method used behind may conduct to the problem. You should read that you can use Read before HasRows but in the past I got some problems with some providers. Hope it helps

ajith k rajagopalan

DataTableReader dataTableReader = myDatabase.CreateDataReader(); if (dataTableReader.HasRows) { while (dataTableReader.Read()) { //// write here... eg : messagebox.show("oUT pUT ");//////// } }

TheJudeDude

I have the following

while (sqlReader.Read())
{
//test for data field
while(sqlReader["SOME FIELD"] == variable)
{
//do work, write data to file, yada, yada
sqlReader.Read()//advance to next record
if (!sqlReader.HasRows)//there's no more data
{
blEnd = true;
break
}
if (blEnd){break} //break out of sqlReader.Read() loop
//do more work, yada, yada
}
//end of sqlReader.Read(), loop
}

Jude

TheJudeDude

Here is the exact error I am receiving: System.InvalidOperationException: Invalid attempt to read when no data is present But sqlReader.HasRows property is True.

Jude

Jimmanuel

What if the data is NULL in the database? Before you check sqlReader["SOME FIELD"] against your target value you should check it against DBNull.Value. Also, this:

      sqlReader.Read()//advance to next record
      if (!sqlReader.HasRows)//there's no more data

has the same problem as before. HasRows doesn't care if you called Read or how many times it's called. It always returns the same thing. You shouldn't have more than one call to Read and you should always check it's return value. What it looks like you're trying to do if go through all of the rows in the reader and do some work when the value of sqlReader["SOME FIELD"] equals variable. If you found variable then exit the loop. If that's what you're doing then try this:

bool found = false;

while (sqlReader.Read() && !found)
{
if (sqlReader["SOME FIELD"] == DBNull.Value)
{
// advance to the next row
continue;
}

// convert the data to the appropriate type here, I'm using string as an example
string dbVal = sqlReader\["SOME FIELD"\] as string;

if (dbVal != variable)
{
    // advance to the next row
    continue;
}

// value is found!
found = true;
// do something . . .

}

Even if it's not what you're doing, note that there's only one Read, it's return value is checked and we're checking for DBNull.Value. dbValshould be the same type as whatever variable is, I just picked string as an example. Also note that HasRows is not needed.

:badger:

TheJudeDude

Thank you much for your reply. What I am confused about in your example is the comment to 'continue to the next row'. How do I advance the cursor without calling Read()?

Jude

Jimmanuel

continue[^] The continue; statement returns the code to the while() line which calls Read(), and that's how it advances to the next row :)

:badger: