DataReader IndexOutofRangeException was unhandled by user code

Question

I ran into another issue again. I was trying to get data from the database using DataReader but I got the error when i was testing my code. Can anyone help me out? The error occurred at this line:

chkAssess = readAssess[columnName].ToString();

Below is the code snippet:

public string CheckAssess(string emailAddress, string columnName)
{
    string chkAssess = "";
    SqlDataReader readAssess;
    //readAssess = new SqlDataReader();

    string MgrAssessQry = "SELECT '"+columnName+"' FROM tblAllUsers";
    //MgrAssessQry += " WHERE email ='" + emailAddress + "'";

    SqlCommand cmdReadAssess = new SqlCommand(MgrAssessQry, cn);
    cn.Open();
    readAssess = cmdReadAssess.ExecuteReader();

    while(readAssess.Read())
    {
        // Add the rows
        chkAssess = readAssess[columnName].ToString();         
    }

    return chkAssess;
}

score 2 · Answer 1 · answered Mar 04 '14 at 13:00

try to use column name without ''

select something from table

instead of

select 'something' from table

for security reasons, don't create sql queries in that way (by concatenating strings) - use @parameters instead

2. close the reader at the end

score 2 · Answer 2 · answered Mar 04 '14 at 13:09

Try this:

public string CheckAssess(string emailAddress, string columnName)
{
string chkAssess = "";
SqlDataReader readAssess;
//readAssess = new SqlDataReader();

string MgrAssessQry = "SELECT @Column_Name FROM tblAllUsers";

SqlCommand cmdReadAssess = new SqlCommand(MgrAssessQry, cn);
cmdReadAssess.Parameters.AddWithValue(new SqlParameter("Column_Name", columnName)); 
cn.Open();
readAssess = cmdReadAssess.ExecuteReader();

while(readAssess.Read())
{
    // Add the rows
    chkAssess = readAssess.GetString(0);
}

return chkAssess;
}

score 1 · Answer 3 · edited May 23 '17 at 12:29

You have got several problems here.

Check whether your readAssess has rows like below.

if(readAssess.HasRows)

If it doesn't have rows then trying

chkAssess = readAssess.GetString(0);

would throw this error, as Arrays are index-based.

So your code should be like below

if(readAssess.HasRows)
{
    while(readAssess.Read())
    {
        chkAssess = readAssess.GetString(0);
    }
}

Other problem is you need to close both the reader & the connection afterwards.

readAssess.Close();
cn.Close();

Also your code is potentially vulnerable to SQL Injection.

score 0 · Answer 4 · answered Jun 30 '14 at 06:20

if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int result = Convert.ToInt32(reader.GetString(0)); 
                    Console.WriteLine(result);
                }
            }

The most important thing is check the query first by executing in SQL Server and see if any result is coming or not. Secondly based on the type of output you are receiving cast it to that particular data type (important).Mostly everyone is saving the data in varchar so.

DataReader IndexOutofRangeException was unhandled by user code

4 Answers4