Getting Error "System.IndexOutOfRangeException". Why?

Question

I have the following Webservice:

using (SqlCommand cmd = new SqlCommand(@"SELECT r.NAME, s.NAME FROM REGION r LEFT OUTER JOIN STADT s ON s.REGION_ID = r.ID ORDER BY r.NAME", con))
{
    con.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            if (rdr["r.NAME"] != DBNull.Value && rdr["s.NAME"] != DBNull.Value)
            {
                stadtObject.Add(new STADT()
                {
                    RegionName = rdr["r.NAME"].ToString(),
                    StadtName = rdr["s.NAME"].ToString()
                });
            }
        }
    }
}

I tested the SQL Statement in SQL Server Management Studio and it is working like a charm. But I am getting an error if I invoke the method in the browser:

System.IndexOutOfRangeException: r.NAME
   at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
   at System.Data.SqlClient.SqlDataReader.get_Item(String name)
   at StadtHelper.Stadt() in C:\Users\Yeah\Documents\Visual Studio 2010\Projects\WebService1\WebService1\StadtHelper.cs:line 31
   at WebService1.Service1.Stadt() in C:\Depp\Ushi\Documents\Visual Studio 2010\Projects\WebService1\WebService1\Service1.asmx.cs:line 77

What am I doing wrong?

Answer 1

When r.Name is selected into the DataReader, it reads only the field name - Name. When selecting the fields, it doesn't include the table specifiers (r. and s.)

It can't find "r.Name" or "s.Name" because when evaluating, the reader is simply returning two columns, both just named "Name" So when searching for r.Name, the runtime is saying that "r.Name is not in the list of valid column names" (which is manifested in an IndexOutOfRange exception).

If you want to be able to access it by name, you have to use the AS statement to give the resulting in-memory results of the query distinct field names:

I think I'm stating that badly, but in essence, your code should be modified as such:

using (SqlCommand cmd = new SqlCommand(@"SELECT r.NAME AS rName, s.NAME AS sName FROM REGION r LEFT OUTER JOIN STADT s ON s.REGION_ID = r.ID ORDER BY r.NAME", con))
{
    con.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            if (rdr["rNAME"] != DBNull.Value && rdr["sNAME"] != DBNull.Value)
            {
                stadtObject.Add(new STADT()
                {
                    RegionName = rdr["rNAME"].ToString(),
                    StadtName = rdr["sNAME"].ToString()
                });
            }
        }
    }
}