Re: 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 14:55:41 +0200

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

"Joyjit Mukherjee" <joyjit_mukherjee@hotmail.com> wrote in message
news:eIT3PHVmEHA.3896@TK2MSFTNGP15.phx.gbl...
> 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
Loading