Invalid attempt to read when no data is present

Question

    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?

Answer 1

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

SqlDataReader dr = cmd10.ExecuteReader();
if (dr.Read()) 
{
    // read data for 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
}

Answer 2

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

Answer 3

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