DataReader .HasRows but returns 'Invalid attempt to read when no data is present' error

Hi,

I am using a stored procedure to extract data from an SQL server
database using a datareader.

The datareader .HasRows returns true, i have also checked this with
query analyser, but when i attempt to use a returned value to
SelectValue in a combobox I get the error 'Invalid attempt to read when
no data is present'. How can this be when .HasRows is true?

Here's the relevant code:

SqlCommand cmdLesion = new SqlCommand ("proc_SelectLesion", conn);
cmdLesion.CommandType = CommandType.StoredProcedure;

cmdLesion.Parameters.Add(new SqlParameter("@patientNo", strPatientNo));
cmdLesion.Parameters.Add(new SqlParameter("@lesLocation",
strLesionLocation));

conn.Open();

SqlDataReader dr;
dr = cmdLesion.ExecuteReader(CommandBehavior.SingleRow);

if(dr.HasRows)
{
lesionLocationCboBx.SelectedValue = dr["lesNo"].ToString();
}
else
{
lesionLocationCboBx.SelectedIndex = 0;
}

dr.Close();


And the stored procedure "proc_SelectLesion":

CREATE PROCEDURE proc_SelectLesion
(@patientNo int,
@lesLocation varchar(50)
)

AS

SELECT lesNo
FROM tbllesion
WHERE lesLocation = @lesLocation AND patientNo = @patientNo
GO

Any ideas?

Many thanks.

KerryMoorman
Mon May 08 08:10:01 CDT 2006

Assimalyst,

From the docs:

"The default position of the SqlDataReader is prior to the first record.
Therefore, you must call Read to begin accessing any data."

Kerry Moorman


"Assimalyst" wrote:

> Hi,
>
> I am using a stored procedure to extract data from an SQL server
> database using a datareader.
>
> The datareader .HasRows returns true, i have also checked this with
> query analyser, but when i attempt to use a returned value to
> SelectValue in a combobox I get the error 'Invalid attempt to read when
> no data is present'. How can this be when .HasRows is true?
>
> Here's the relevant code:
>
> SqlCommand cmdLesion = new SqlCommand ("proc_SelectLesion", conn);
> cmdLesion.CommandType = CommandType.StoredProcedure;
>
> cmdLesion.Parameters.Add(new SqlParameter("@patientNo", strPatientNo));
> cmdLesion.Parameters.Add(new SqlParameter("@lesLocation",
> strLesionLocation));
>
> conn.Open();
>
> SqlDataReader dr;
> dr = cmdLesion.ExecuteReader(CommandBehavior.SingleRow);
>
> if(dr.HasRows)
> {
> lesionLocationCboBx.SelectedValue = dr["lesNo"].ToString();
> }
> else
> {
> lesionLocationCboBx.SelectedIndex = 0;
> }
>
> dr.Close();
>
>
> And the stored procedure "proc_SelectLesion":
>
> CREATE PROCEDURE proc_SelectLesion
> (@patientNo int,
> @lesLocation varchar(50)
> )
>
> AS
>
> SELECT lesNo
> FROM tbllesion
> WHERE lesLocation = @lesLocation AND patientNo = @patientNo
> GO
>
> Any ideas?
>
> Many thanks.
>
>

Jim
Mon May 08 08:13:10 CDT 2006

I believe need to issue a Read() call to move to each row.

if(dr.HasRows)
{
dr.Read();
lesionLocationCboBx.SelectedValue = dr["lesNo"].ToString();
}


"Assimalyst" <c_oxtoby@hotmail.com> wrote in message
news:1147091409.620206.208580@j73g2000cwa.googlegroups.com...
> Hi,
>
> I am using a stored procedure to extract data from an SQL server
> database using a datareader.
>
> The datareader .HasRows returns true, i have also checked this with
> query analyser, but when i attempt to use a returned value to
> SelectValue in a combobox I get the error 'Invalid attempt to read when
> no data is present'. How can this be when .HasRows is true?
>
> Here's the relevant code:
>
> SqlCommand cmdLesion = new SqlCommand ("proc_SelectLesion", conn);
> cmdLesion.CommandType = CommandType.StoredProcedure;
>
> cmdLesion.Parameters.Add(new SqlParameter("@patientNo", strPatientNo));
> cmdLesion.Parameters.Add(new SqlParameter("@lesLocation",
> strLesionLocation));
>
> conn.Open();
>
> SqlDataReader dr;
> dr = cmdLesion.ExecuteReader(CommandBehavior.SingleRow);
>
> if(dr.HasRows)
> {
> lesionLocationCboBx.SelectedValue = dr["lesNo"].ToString();
> }
> else
> {
> lesionLocationCboBx.SelectedIndex = 0;
> }
>
> dr.Close();
>
>
> And the stored procedure "proc_SelectLesion":
>
> CREATE PROCEDURE proc_SelectLesion
> (@patientNo int,
> @lesLocation varchar(50)
> )
>
> AS
>
> SELECT lesNo
> FROM tbllesion
> WHERE lesLocation = @lesLocation AND patientNo = @patientNo
> GO
>
> Any ideas?
>
> Many thanks.
>

Assimalyst
Mon May 08 08:16:39 CDT 2006

Thanks,

I put in a while(dr.Read()) loop, seems to fix the problem. :)