System.IndexOutOfRangeException when trying to return 'select count (Id)'

Question

I tried to look this up but no other question really fit. I have a web page where I will be showing analytics. One stat is total amount of users, where i am trying to take the total amount of rows in my user table in a sql database. I had thought the Id would be an int but it appears it is varchar(128). I will include the error and the code. Thank you!!

An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code

public static string GetUserSum(string rConnStr)
    {
        string UserCount = "";

        using (SqlConnection conn = new SqlConnection(rConnStr))
        {
            const string sqlText = @"
SELECT COUNT(Id)
FROM AspNetUsers
             ";

            using (SqlCommand cmd = new SqlCommand(sqlText, conn))
            {

                conn.Open();

                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    bool result = sdr.Read();
                    if (result)
                    {

                        UserCount = DBUtils.GetValue<string>(sdr["Id"]); //it breaks right here

                    }
                }
            }
        }

        return UserCount;
    }

you can change sdr["Id"] to sdr[0] or add an alias to your count(Id), as it is there's no column being returned with "Id" as name — Juan, May 18 '15 at 23:17

score 3 · Answer 1 · answered May 18 '15 at 23:07

3

I think the problem is that the column name in what you are getting from SQL is not what you're expecting. I believe the following change to your code will fix that.

const string sqlText = @"
SELECT COUNT(Id) AS Id
FROM AspNetUsers
             ";

answered May 18 '15 at 23:07

hatchet - done with SOverflow

15,919
5
47
57

score 1 · Accepted Answer · answered May 18 '15 at 23:17

1

Your error comes from this line:

 UserCount = DBUtils.GetValue<string>(sdr["Id"]);

Because no field with the Id "Id" is returned from your query.

The easiest fix is:

UserCount = DBUtils.GetValue<string>(sdr[0]).Tostring();

answered May 18 '15 at 23:17

GANI

2,013
4
35
69

score 0 · Answer 3 · edited May 23 '17 at 12:06

0

The IndexOutOfRangeException indicates that there isn't an "Id" column being returned to the SqlDataReader.

The first step to resolving your problem will likely be discovering what columns are actually being returned. Here's an example of how to do so: Check for column name in a SqlDataReader object

edited May 23 '17 at 12:06

Community

1
1

answered May 18 '15 at 23:11

Colin

4,025
21
40

System.IndexOutOfRangeException when trying to return 'select count (Id)'

3 Answers3