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?

Julien Poulin · Accepted Answer · 2022-11-02T12:39:42.150

219

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
}

edited Nov 02 '22 at 12:39

answered Jul 18 '09 at 14:08

Julien Poulin

12,737
10
51
76

4

besides that, you might want to try for the data to be null using dr.IsBDNull(0) – Luis Robles Sep 09 '11 at 16:10
5

`"You have to call DataReader.Read to fetch the result:"` As soon as I saw that, I was immediately, "oh! Yeah! I didn't call .Read()". Ya know, I've been doing this for 35 years. You'd think........... lol – KWallace Mar 27 '21 at 23:08

score 17 · Answer 2 · answered Jul 18 '09 at 14:08

17

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

answered Jul 18 '09 at 14:08

Colin Mackay

18,736
7
61
88

score 9 · Answer 3 · answered Apr 27 '14 at 02:38

9

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

answered Apr 27 '14 at 02:38

Charlie

8,530
2
55
53

score 8 · Answer 4 · answered May 27 '13 at 13:41

8

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

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

answered May 27 '13 at 13:41

dougczar

585
7
8

4

in my case I had this, and though I have been using this elsewhere, in this particular scenario that produced this error, this did NOT stop the error from bubbling. My query resultant has rows, and yet the reader has no data. I really don't understand what is going on. Verified the query and parameters myself. – Barry Sep 19 '17 at 15:39

score 3 · Answer 5 · edited Apr 29 '16 at 05:15

3

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;

edited Apr 29 '16 at 05:15

JRodDynamite

12,325
5
43
63

answered Apr 23 '16 at 18:37

Aneel Goplani

79
3

score 3 · Answer 6 · answered Aug 18 '16 at 10:41

3

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

answered Aug 18 '16 at 10:41

Dev

1,451
20
30

Invalid attempt to read when no data is present

6 Answers6

Linked

Related