DataReader problem with while(dr.Read())

[Assimalyst]

Hi,

I am using a datareader to extract an integer value from an SQL table.

string strPDTNo = pdtDateCboBx.SelectedValue;

SqlCommand cmd = new SqlCommand("proc_GetSpirNo", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@pdtNo", strPDTNo));

SqlDataReader dr = cmd.ExecuteReader();

// count rows, helps deal with potential null entries
int i = 0;
while(dr.Read())
{
i++;
}

if (i!=0)
{
int intSpirNo = (int)dr["spirNo"];
.. . .

but when the "int intSpirNo = (int)dr["spirNo"];" statement is executed
i get an error "Invalid attempt to read when no data is present."
Though there is definately a single value read into the spirNo column,
i checked it with query analyser.

However, if i remove the while statement and set i=1 (so the if
statement runs)
it works as expected. But i need a way to tell if the datareader has
extracted null or more rows.

Any ideas?

Thanks

 

[W.G. Ryan MVP]

You can use HasRows on the DataReader ifyou're using the 1.1 framework to
tell if it has rows. The problem you're probably having is that you only
have 1 row. When you call the first Read(), it's moving you past that
record so there's nothing else left to read.

HTH,

Bill

 

[Mark Rae]

But i need a way to tell if the datareader has extracted null or more
rows.

Use the .HasRows property in v1.1 of the Framework.

Also, since you're returning only one item of information, consider using
ExecuteScalar instead...

 

[Marina]

The last time dr.Read executes, it returns False - which is what gets you
out of the loop. At this point you are past the end of the result set - you
are no longer at a row.

When you try to read data, you get an error, since you are not on a row.