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
"Val Mazur" <(E-Mail Removed)> wrote in message
news:u%(E-Mail Removed)...
> Hi,
>
> Like for me code looks fine. What is the datatype of this first column?
>
> --
> Val Mazur
> Microsoft MVP
>
>
> "DotNet Ed" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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
> >
> >
>
>
|
Similar Threads
