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

From: Val Mazur (group51a_at_hotmail.com)
Date: 09/13/04 

Date: Sun, 12 Sep 2004 21:12:55 -0400

Hi,

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

-- 
Val Mazur
Microsoft MVP
"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

  • 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: 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)
  • 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: 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)