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

From: Joyjit Mukherjee (joyjit_mukherjee_at_hotmail.com)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 10:53:29 +0530

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" <tiredofspam@abolishspam.now> wrote in message
news:%23AlyacSmEHA.2588@TK2MSFTNGP12.phx.gbl...
> 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
>
>

Relevant Pages