ExamGuru IT/.Net Guru : Weird SqlDataReader: Invalid attempt to read when no data is present. on microsoft.public.dotnet.framework.adonet

.NETGURU

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

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.adonet.

DotNet Ed

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

Reply to this message...

Joyjit Mukherjee

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" <Click here to reveal e-mail address> wrote in message
news:%Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

DotNet Ed

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" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Val Mazur

Hi,

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

--
Val Mazur
Microsoft MVP

"DotNet Ed" <Click here to reveal e-mail address> wrote in message
news:%Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

DotNet Ed

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" <Click here to reveal e-mail address> wrote in message
news:u%Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

	System.Data.CommandType
	System.Data.DataSet
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlDataReader
	System.Data.SqlClient.SqlParameter
	System.Data.SqlDbType
	System.InvalidOperationException

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification