The index outside of the range Exception

Question

I'm trying to fill my list on Visual Studio side with help of stored procedure.

I'm using the following stored procedure:

CREATE PROCEDURE [dbo].[GetColletcion]
AS
BEGIN   
         select  CollectionType.Name ,GlassesCollection.Name 
    from    GlassesCollection
    inner join CollectionType
    on GlassesCollection.CollectionType=CollectionType.CollTypeID
END

Here's the code-behind:

protected void Button1_Click(object sender, EventArgs e)
        {

            List<GlassesCollection> list = new List<GlassesCollection>();
            using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=ISeeOptic;Integrated Security=SSPI"))
            {

                GlassesCollection gln = new GlassesCollection();
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader;

                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetColletcion";

                conn.Open();
                reader = cmd.ExecuteReader();
                  while (reader.Read())
                    {
                        gln.Name = (string)reader["CollectionType.Name"];
                        gln.CollectionType = (string)reader["GlassesCollection.Name"];

                        list.Add(gln);
                    }


                reader.Close();
                conn.Close();
            }

        }

But when it comes to this row:

gln.Name = (string)reader["CollectionType.Name"];

I get this Exception:

Exception Details: System.IndexOutOfRangeException: CollectionType.Name

The index outside of the range, although in database more than one record. How can I solve my problem?

Answer 1

It would be best to use column aliases and refer to the columns by those instead.

CREATE PROCEDURE [dbo].[GetColletcion]
AS
BEGIN   
         select  CollectionType.Name As TypeName ,GlassesCollection.Name As GlassesName
    from    GlassesCollection
    inner join CollectionType
    on GlassesCollection.CollectionType=CollectionType.CollTypeID
END

Then use

(string)reader["TypeName"];
(string)reader["GlassesName"];

If you cannot change your stored procedure, then you can use the oridinal position:

(string)reader[0]; //CollectionType.Name
(string)reader[1]; //GlassesCollection.Name

Answer 2

I corrected your typo, too. (GetCollection)

CREATE PROCEDURE [dbo].[GetCollection]
AS
BEGIN   
         select  CollectionType.Name AS CollectionType_Name, GlassesCollection.Name AS GlassesCollection_Name 
    from    GlassesCollection
    inner join CollectionType
    on GlassesCollection.CollectionType=CollectionType.CollTypeID
END

Code behind:

protected void Button1_Click(object sender, EventArgs e)
    {
        List<GlassesCollection> list = new List<GlassesCollection>();
        using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=ISeeOptic;Integrated Security=SSPI"))
        {

            GlassesCollection gln = new GlassesCollection();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetCollection";

            conn.Open();
            reader = cmd.ExecuteReader();
              while (reader.Read())
                {
                    gln.Name = (string)reader["GlassesCollection_Name"];
                    gln.CollectionType = (string)reader["CollectionType_Name"];

                    list.Add(gln);
                }


            reader.Close();
            conn.Close();
        }

    }