CODEWITHSUNDEEP
c#asp.netsql
Bashud
Bashud

Reputation: 276

Getting Error "System.IndexOutOfRangeException". Why?

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?

Upvotes: 7

Views: 36277

Answers (4)

Lucian
Lucian

Reputation: 4001

The data reader doesn't know what r.NAME is because the columns that are actually returned are NAME and NAME. You can use aliases to differentiate the two, like this:

SELECT r.NAME as REGION_NAME, s.NAME as STADT_NAME FROM REGION r LEFT OUTER JOIN STADT s ON s.REGION_ID = r.ID ORDER BY r.NAME

And then access the returned data by the alias:

if (rdr["REGION_NAME"] != DBNull.Value && rdr["STADT_NAME"] != DBNull.Value)
                    {
                        stadtObject.Add(new STADT()
                        {
                            RegionName = rdr["REGION_NAME"].ToString(),
                            StadtName = rdr["STADT_NAME"].ToString()
                        });
                    }

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

It doesn't like the alias r, which of course gives you a potential problem as your column names are not unique.

Try something like:

Select r.Name as RName, s.Name as SName ...

Then rdr["RName"] and rdr["SName"] will be goers.

Upvotes: 1

David
David

Reputation: 73554

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()
                });
            }
        }
    }
}

Upvotes: 12

Douglas
Douglas

Reputation: 54877

Table names cannot be used to qualify the field names in a SqlDataReader; the field name, in your case, is "NAME".

To avoid the naming conflict, you should rename the columns in your projection:

SELECT r.NAME AS RegionName, s.NAME AS StateName FROM REGION r …

Then, access the fields using the new names:

if (rdr["RegionName"] != DBNull.Value && rdr["StateName"] != DBNull.Value)

Upvotes: 0

Related Questions