MAX(id) using SqlDataReader C#

Question

How do I change this:

        using (SqlCommand myCommand = myConnection.CreateCommand())
        {
            myConnection.Open();
            myCommand.CommandText = "SELECT FormID FROM tbl_Form";
            using (SqlDataReader reader = myCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    int FormID = reader.GetInt32(reader.GetOrdinal("FormID"));
                    MessageBox.Show(FormID.ToString());
                }
            }
        }

to get MAX(FormID) ?

My natural tendency is to throw a MAX around FormID but I'm getting an IndexOutOfRange exception.

Dan Kanze · Accepted Answer · 2012-02-12 00:16:43Z

19

When you select the maximum id you shouldn't use a SqlDataReader - the query returns just one item, which by default is unnamed so your existing query breaks because it expects a result named "FormID" - although you could have "fixed" your query by using "SELECT MAX(FormID) as FormId FROM tbl_Form". Instead use ExecuteScalar():

myCommand.CommandText = "SELECT MAX(FormID) FROM tbl_Form";
int maxId = Convert.ToInt32(myCommand.ExecuteScalar());

edited Feb 12, 2012 at 0:16

Dan Kanze

18.6k28 gold badges85 silver badges136 bronze badges

answered Feb 12, 2012 at 0:06

BrokenGlass

161k28 gold badges297 silver badges339 bronze badges

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

Comments

Antony Scott · Accepted Answer · 2012-02-12 00:07:51Z

1

It's been a while since I used this style of database access, but I think you just need

select max(FormID) from tbl_Form

along with a call to ExecuteScalar

answered Feb 12, 2012 at 0:07

Antony Scott

22.1k12 gold badges64 silver badges95 bronze badges

Collectives™ on Stack Overflow

MAX(id) using SqlDataReader C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related