System.IndexOutOfRangeException on SQLDataReader Value Using C#

Question

I have a SQLDataReader that returns three integers. However, there are occasions when two of the integers will return null values.

To get round this I wrote the following:

int shoppingCartHeadID = 0;
int billID = 0;
int delID = 0;

conn.Open();
reader = comm.ExecuteReader();
if (reader.Read())
{
       shoppingCartHeadID = Convert.ToInt32(reader["shoppingCartHeadID"]);

       if (!reader.IsDBNull(billID))
       {
            billID = Convert.ToInt32(reader["billID"]);
       }

       if (!reader.IsDBNull(delID))
       {
            delID = Convert.ToInt32(reader["delID"]);
       }                
}
reader.Close();

Unfortunately I'm still getting the error message. Any suggestions?

PS I also tried this with no luck

if (reader["billID"] != null)

how does it work if you try to access the reader's values by integer index instead of column name? lile 0, 1 or 2... ? — Davide Piras, Oct 10 '11 at 11:01
I hope you have got those names rite .. are you using the proper alias in the query ? — V4Vendetta, Oct 10 '11 at 11:07

score 5 · Accepted Answer · answered Oct 10 '11 at 11:05

I would try to access by index instead of column name, just in case you are passing a not existing column name.

Also, make sure you wrap your reader with a using block so in any case even if there is an exception your reader will be properly closed and disposed, for example in this way:

...
using(var reader = comm.ExecuteReader())
{
    if (reader.Read())
    {
           shoppingCartHeadID = Convert.ToInt32(reader[0]);

           if (!reader.IsDBNull(1))
           {
                billID = Convert.ToInt32(reader[1]);
           }

           if (!reader.IsDBNull(2))
           {
                delID = Convert.ToInt32(reader[2]);
           }
    }
}

score 0 · Answer 2 · answered Oct 10 '11 at 11:38

0

Use GetXXXX(colIndex) method.

if (!reader.IsDBNull(0)) // I presume that billid at 0 ordinal  
 {
    billID = reader.GetInt32(0);
 }
if (!reader.IsDBNull(1)) // I presume that delId at 1 ordinal
 {
    delID = reader.GetInt32(1);
 }

answered Oct 10 '11 at 11:38

KV Prajapati

93,659
19
148
186

System.IndexOutOfRangeException on SQLDataReader Value Using C#

2 Answers2

Linked