CODEWITHSUNDEEP
c#ado.netsqldatareader
knowledgehunter
knowledgehunter

Reputation: 1415

Invalid attempt to read when no data is present

private void button1_Click(object sender, EventArgs e)
{
    string name;
    name = textBox5.Text;
    SqlConnection con10 = new SqlConnection("con strn");
    SqlCommand cmd10 = new SqlCommand("select * from sumant where username=@name");
    cmd10.Parameters.AddWithValue("@name",name);
    cmd10.Connection = con10;
    cmd10.Connection.Open();//line 7
    SqlDataReader dr = cmd10.ExecuteReader();
}

if ( textBox2.Text == dr[2].ToString())
{
    //do something;
}

When I debug until line 7, it is OK, but after that dr throws an exception:

Invalid attempt to read when no data is present.

I don't understand why I'm getting that exception, since I do have data in the table with username=sumant.

Please tell me whether the 'if' statement is correct or not. And how do I fix the error?

Upvotes: 115

Views: 172230

Answers (6)

Julien Poulin
Julien Poulin

Reputation: 13025

You have to call DataReader.Read() to fetch the result:

SqlDataReader dr = cmd10.ExecuteReader();
if (dr.Read()) 
{
    // read data for single/first record here
}

DataReader.Read() returns a bool indicating if there are more blocks of data to read, so if you have more than 1 result, you can do:

while (dr.Read()) 
{
    // read data for each record here
}

Upvotes: 227

Dev
Dev

Reputation: 1541

I was having 2 values which could contain null values. 

while(dr.Read())
 {
    Id = dr["Id"] as int? ?? default(int?);
    Alt =  dr["Alt"].ToString() as string ?? default(string);
    Name = dr["Name"].ToString()
 }

resolved the issue

Upvotes: 3

Aneel Goplani
Aneel Goplani

Reputation: 77

I used the code below and it worked for me. 

String email="";
    SqlDataReader reader=cmd.ExecuteReader();
    if(reader.Read()){
        email=reader["Email"].ToString();
    }

String To=email;

Upvotes: 3

Charlie
Charlie

Reputation: 9108

I just had this error, I was calling dr.NextResult() instead of dr.Read().

Upvotes: 10

dougczar
dougczar

Reputation: 595

I would check to see if the SqlDataReader has rows returned first:

SqlDataReader dr = cmd10.ExecuteReader();
if (dr.HasRows)
{
   ...
}

Upvotes: 8

Colin Mackay
Colin Mackay

Reputation: 19175

You have to call dr.Read() before attempting to read any data. That method will return false if there is nothing to read.

Upvotes: 19

Related Questions