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

From: DotNet Ed (tiredofspam_at_abolishspam.now)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 02:17:43 +0200

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

  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • Re: Weird SqlDataReader: Invalid attempt to read when no data is present.
    ... I know my stored procedure is working ok. ... >> I used the SQL Profiler to see how the stored procedure was being ... >> I know that the query returns hits. ... The data reader object's HasRows ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Weird SqlDataReader: Invalid attempt to read when no data is present.
    ... When I execute the stored ... I know my stored procedure is working ok. ... > the SQL Query analyser and got results. ... The data reader object's HasRows ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Stored Procedure performance
    ... >Subject: RE: Stored Procedure performance ... >I would say that your Query is executing the Stored Procedure for every ... >In this way the Stored Procedure would only execute once and not on all ...
    (comp.databases.informix)
  • Re: Differences in execution times for a stored procedure
    ... When a SP is executed for the first time, SQL Server compiles a query plan ... >I am trying to execute a time-consuming stored procedure in my application. ...
    (microsoft.public.dotnet.framework.adonet)