Getting an IndexOutOfRangeException when putting sql into an object array

Question

Currently working on a small tool for the company I am working at which shall handle the database. I have several classes handling various SQL functions, but whenever I try to put specific data from the database into a datagridview I get the Exception.

SQL function:

public class OUsersDB
{
    SqlConnection conn = new SqlConnection("Connection Information");

    public SqlDataReader Select()
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT au.ApplicationId, au.UserId, au.UserName, au.MobileAlias, au.LastActivityDate, au.Name, sms.number, am.email FROM aspnet_users AS au " +
            " JOIN aspnet_membership AS am ON au.userid=am.userid " +
            " JOIN smsphonebooks AS sms ON au.name=sms.name";
        SqlDataReader ur = cmd.ExecuteReader();
        return ur;

    }
}

Winform datagrid function

        public void Usersdb()
    {
        OUsersDB oudb = new OUsersDB();
        SqlDataReader ur = oudb.Select();
        myDataUsers.Rows.Clear();
        while (ur.Read())
        {
            object[] objUsers = {
                ur["au_ApplicationId"].ToString(),
                ur["au_UserId"].ToString(),
                ur["au_UserName"].ToString(),
                ur["au_MobileAlias"].ToString(),
                ur["au_LastActivityDate"].ToString(),
                ur["au_Name"].ToString(),
                ur["au_Phone"].ToString(),
                ur["au_Email"].ToString(), };
            myDataUsers.Rows.Add(objUsers);
            conn.Close();
        }

I have a similar sql function and object array futher up in the code which loads just fine, but once reaching this part I get the Exception on the

ur["au_ApplicationId"].ToString(),

It simply says

System.IndexOutOfRangeException: 'au_ApplicationId'

Selecting au.ApplicationId field and trying to get au_ApplicationId from reader. — Renatas M.
– Renatas M., Commented Feb 21, 2019 at 14:16
Shouldn't the column be named "ApplicationId" only? Try removing the "au_". Same for the other columns. — Thomas Voß
– Thomas Voß, Commented Feb 21, 2019 at 14:16
Why not just use SqlDataAdapter.Fill(DataTable)? Not sure why you're filling the DataTable myDataUsers manually here. — Zer0
– Zer0, Commented Feb 21, 2019 at 14:46

Stormhashe · Accepted Answer · 2019-02-21 14:16:41Z

0

Change the obj to read the columns without the alias. You use aliases only to reference the columns in the query, but the output of the query won't have the aliases.

while (ur.Read())
        {
            object[] objUsers = {
                ur["ApplicationId"].ToString(),
                ur["UserId"].ToString(),
                ur["UserName"].ToString(),
                ur["MobileAlias"].ToString(),
                ur["LastActivityDate"].ToString(),
                ur["Name"].ToString(),
                ur["Phone"].ToString(),
                ur["Email"].ToString(), };
            myDataUsers.Rows.Add(objUsers);
            conn.Close();
        }

answered Feb 21, 2019 at 14:16

Stormhashe

7146 silver badges16 bronze badges

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

5 Comments

LazyBeaver Over a year ago

I did try without the alias beforehand, but still got the same response.

Stormhashe Over a year ago

Did you check if this query is returning any rows? use if (reader.HasRows) { before the while

Robin Salih Over a year ago

This is actually wrong, it's the alias you should use to look up the data not the original column name.

Stormhashe Over a year ago

@RobinSalih you are wrong. I am talking about the TABLE alias, not the COLUMN alias. au is the TABLE alias, which is omitted in the end result, while the OP didn't place any COLUMN alias on his query, but rather he's using the original COLUMN names. Read the code better.

Robin Salih Over a year ago

@Stormhashe Sorry, yes you are correct, I thought the columns were being aliased.

NoImagination · Accepted Answer · 2019-02-21 14:23:35Z

0

Check if you get ANY object, maybe you get nothing at all.

answered Feb 21, 2019 at 14:23

NoImagination

1861 silver badge10 bronze badges

1 Comment

Robin Salih Over a year ago

If they have no data returned then it will not enter the while loop where from within the exception is thrown.

Gothrik · Accepted Answer · 2019-02-21 15:10:48Z

0

You can try this:

https://stackoverflow.com/a/8656011/8512753

or remove the table aliases

ur["au_ApplicationId"].ToString(),

becomes

ur["ApplicationId"].ToString(),

and try assigning concrete values to the columns returned in your SQL

SELECT ApplicationId = au.ApplicationId, ...

answered Feb 21, 2019 at 15:10

Gothrik

392 bronze badges

Comments

Zer0 · Accepted Answer · 2019-02-21 15:35:18Z

0

Regarding the IndexOutOfRangeException, that's due to trying to access a column that doesn't exist in the reader. Not sure why you're prefixing columns with "au_" when the SELECT statement is not doing this.

I would use SqlDataAdapter instead and add add this method to OUsersDB.

public void FillTable(DataTable table)
{
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT au.ApplicationId, au.UserId, au.UserName, au.MobileAlias, au.LastActivityDate, au.Name, sms.number, am.email FROM aspnet_users AS au " +
        " JOIN aspnet_membership AS am ON au.userid=am.userid " +
        " JOIN smsphonebooks AS sms ON au.name=sms.name";
    using(var adapter = new SqlDataAdapter(cmd))
        adapter.Fill(table)
}

Change calling code to:

public void Usersdb()
{
    OUsersDB oudb = new OUsersDB();
    myDataUsers.Rows.Clear();
    oudb.FillTable(myDataUsers);
}

Or here's a simple fix, instead. Change your SELECT statement to alias column names as the code is expecting.

SELECT au.ApplicationId as au_ApplicationId, au.UserId as au_UserId, au.UserName as au_UserName, 
au.MobileAlias as au_MobileAlias, au.LastActivityDate as au_LastActivity, au.Name as au_Name, 
sms.number as au_Phone, am.email as au_Email -- REST OF QUERY UNCHANGED

edited Feb 21, 2019 at 15:35

answered Feb 21, 2019 at 14:51

Zer0

7,3841 gold badge22 silver badges34 bronze badges

4 Comments

LarsTech Over a year ago

Doesn't address the issue though. By the way, a DataAdapter will open and close a connection for you, so you don't have to open it explicitly.

Zer0 Over a year ago

@LarsTech You're saying SqlDataAdapter (or base classes) have a finalizer for managed objects? Because the code above doesn't call Dispose. Not sure why this doesn't address the issue. There's a DataTable they are trying to fill with a simple SELECT. This fits the bill.

LarsTech Over a year ago

It doesn't address the index of range issue. I didn't claim the DataAdapter disposes anything. You don't need conn.Open(); in your code above. The DataAdapter will check to see if the connection is open, and if it isn't, it will open it.

Zer0 Over a year ago

@LarsTech I was referring to your claim that it closes the connection without being disposed of. I'll remove the open. I updated the answer to address the exception as well.

Collectives™ on Stack Overflow

Getting an IndexOutOfRangeException when putting sql into an object array

4 Answers 4

5 Comments

1 Comment

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related