Reading from database using SqlDataReader

Question

G_ID is an integer column in Groups table. I want the maximum value of it. When I trace below code I receive the error I've mentioned in code. Reader.HasRows equals true during debugging. So why it says "no data is present"

SqlConnection sqlc= new SqlConnection("data source=. ; database=LDatabase; integrated security=true");
SqlCommand cmd= new SqlCommand("select MAX(G_ID) as MAXID from Groups", sqlc);

sqlc.Open();
SqlDataReader Reader= cmd.ExecuteReader();
int MaxID = 0;
        if (Reader.HasRows)
        {
            MaxID = Convert.ToInt32(Reader["MAXID"].ToString());// Here I receive this error:  System.InvalidOperationException: Invalid attempt to read when no data is present.
            MaxID += 1;
        }

You need to call Reader.Read() before you can access the columns ... — marc_s
– marc_s, Commented Dec 5, 2013 at 21:23

Conrad Frix · Accepted Answer · 2013-12-05 21:27:35Z

3

Before accessing a DataReader you need to call the method Read to position the reader on the first record

SqlConnection sqlc= new SqlConnection("data source=. ; database=LDatabase; integrated security=true");
SqlCommand cmd= new SqlCommand("select MAX(G_ID) as MAXID from Groups", sqlc);

sqlc.Open();
SqlDataReader Reader= cmd.ExecuteReader();
int MaxID = 0;
if (Reader.Read())
{
    MaxID = Convert.ToInt32(Reader["MAXID"].ToString());
    MaxID += 1;
}

By the way, your query returns just one row and one column from the DB so a better approach is to use the ExecuteScalar method

SqlCommand cmd= new SqlCommand("select MAX(G_ID) as MAXID from Groups", sqlc);
sqlc.Open();
object result = cmd.ExecuteScalar();
if (result != null)
{
    MaxID = Convert.ToInt32(result) + 1;
}

edited Dec 5, 2013 at 21:27

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

answered Dec 5, 2013 at 21:24

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Steve Over a year ago

Thanks, I am not sure why I have started thinking in VB lately

Behnaz_Mardanzadeh Over a year ago

When I execute above code(ExecuteScalar()) I receive this error: System.InvalidOperationException: ExecuteScalar: CommandText property has not been initialized

Steve Over a year ago

That's strange, the CommandText is the SELECT.... in the command constructor. Are you sure that you are executing the same SqlCommand shown above?

Behnaz_Mardanzadeh Over a year ago

I also tried this: sqlc.Open(); Int32 result = (Int32)cmd.ExecuteScalar(); if (result != 0) { result += 1; } but still I get the same error.

Steve Over a year ago

If you try the DataReader version do you get something instead?

|

mayabelle · Accepted Answer · 2013-12-05 21:24:48Z

0

You are checking if the reader has rows but you are not reading them. Do this instead (note I'm also wrapping things to make sure they gets disposed properly):

SqlConnection sqlc= new SqlConnection("data source=. ; database=LDatabase; integrated security=true");
SqlCommand cmd = new SqlCommand("select MAX(G_ID) as MAXID from Groups", sqlc);

sqlc.Open();
try {
    using (SqlDataReader reader = cmd.ExecuteReader()) {
        int MaxID = 0;

        while (reader.Read()) {
            MaxID = Convert.ToInt32(Reader["MAXID"].ToString());
            MaxID += 1;
        }
    }
}
finally {
    sqlc.Close();
}

answered Dec 5, 2013 at 21:24

mayabelle

10.1k10 gold badges40 silver badges59 bronze badges

Collectives™ on Stack Overflow

Reading from database using SqlDataReader

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related