Weird SqlDataReader: Invalid attempt to read when no data is present.

[DotNet Ed]

I'm having a strange problem with the SqlDataReader and it is as follows.
First of all I have a stored procedure called sr_companies, it searches a
table for entries matching the given criteria. When I execute the stored
procedure using the Query Analyser (exec sr_company @Type=40) I get a list
of matching rows. I know my stored procedure is working ok.

I used the SQL Profiler to see how the stored procedure was being invoked
and it was ok. I also used the same invocation shown in the Profiler within
the SQL Query analyser and got results (i.e. hits).

But then... when I execute the code that reads from the data reader it craps
out as soon as it attempts to read the first column. It throws a
System.InvalidOperationException with the following message: " Invalid
attempt to read when no data is present."

I know that the query returns hits. The data reader object's HasRows
property returns true. I can also use the r.GetOrdinal("columnname") method
to obtain the field number of the particular column within the result set,
all 7 of them. This means it knows what it is being returned. But it still
escapes me WHY it says there is no data when the query actually returns
hits!!! it even says it "has rows" in the result!!! Here is what I do:

// setup a connection
SqlConnection dbConn = new...

// setup a command to execute the stored procedure
SqlCommand dbCmd;
dbCmd = new SqlCommand("sr_companies", dbConn);
dbCmd.CommandType = CommandType.StoredProcedure;

// add the search criteria required in the parameter
SqlParameter dbPar = dbCmd.Parameters.Add("@Type", SqlDbType.TinyInt);
dbPar.Value = 40;

// Obtain a data reader provided by execution of the query
dbConn.Open();
SqlDataReader r = dbCmd.ExecuteReader();
// is r.Records affected valid at this moment? or only after a r.Close() ???
if (r.HasRows)
{ // it is coming here, so HasRows is true for the sample query. Omiting
loop for clarity...
int colnr = r.GetOrdinal("CompanyName"); // this also returns the
correct value
string name = r.GetString(colnr); // CRAPS OUT...
System.InvalidOperationException
:
}

// read data

// close
r.Close();
dbConn.Close();


so, I really don't know why it does not do it. It says it has rows, the
connection is open because the reader is not closed yet, the stored
procedure executes properly, the search criteria given actually produces at
least one row of results. I only need forward access, that is why I am using
a Data Reader rather than a DataSet.

Regards,
Emilio

 

[Val Mazur]

Hi,

Like for me code looks fine. What is the datatype of this first column?

 

[DotNet Ed]

I'm using the appropriate method for each column:
r.GetString(...) for Company Name (VarChar 60)
r.GetGuid(...) for the ID (uniqueidentifier)
r.GetInt(...) for the type (tinyint)
The problem is not with the conversion (in such case you get a different
error) but the fact that it complains that there is no data which is
strange. WHen I examine the data reader object with the Quick Watch I see a
member called "data ready" and it appears to be false. But I can't imagine
why, the query works fine by hand and upon return it says it does have at
least one row of data available.

Regards,
Emilio

 

[Joyjit Mukherjee]

Hi,

are you looping through the reader by a r.Read() method before getting the
values from the reader through GetOrdinal() or Getxxxx() ?

Regards
Joyjit

 

[DotNet Ed]

I have something like this:

while (r.Read())
{
read all fields for each record
}

so within the loop i read each and every one of the fields as well as the
ordinal thing (that can be easily optimized but that is not the problem).
So, first time I go into REad() it should read the fields of the first
record. Yet it doesn't it throws the exception saying there is no data.

Any other taker?
Emilio