Using a SqlDataReader from a class.

Question

Hi i am currently teaching myself C# and .NET and i would appreciate some help on this issue from you kind guys.

I have created an aspx page that is going to update the quantity of some text boxes. In the code behind the method calls a class that connects to my SQL server database and returns the values to place in the text boxes in a SQLDataReader.

However the only way i can seem to get the code to work is to not close the connection of the reader or the connection to the database.

Ill post my code below for you guys to see and if you need anymore information please ask.

update.aspx

private void LoadQuantity(int ProdID)
        {
            SqlDataReader reader2 = Geordie_Jeans.Classes.AdminControl.GetQuant(ProdID);

            int count = reader2.FieldCount;
            while (reader2.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    if (reader2["PROD_SIZE"].ToString() == "L")
                    {
                        txtLQty.Text = reader2["PROD_QUANT"].ToString();
                    }
                    if (reader2["PROD_SIZE"].ToString() == "M")
                    {
                        txtMQty.Text = reader2["PROD_QUANT"].ToString();
                    }
                    if (reader2["PROD_SIZE"].ToString() == "S")
                    {
                        txtSQty.Text = reader2["PROD_QUANT"].ToString();
                    }
                }
            }
           
        }

admin.cs

public static SqlDataReader GetQuant(int prodID)
        {
            string connectionString =
            WebConfigurationManager.ConnectionStrings["Geordie_Jeans"].ConnectionString;

            SqlConnection con2 = new SqlConnection(connectionString);
            SqlCommand cmd2 = new SqlCommand("PR_GET_QUANTITY", con2);
            cmd2.CommandType = CommandType.StoredProcedure;

            cmd2.Parameters.Add("@ID", SqlDbType.Int).Value = prodID;

            SqlDataReader reader2 = null;

            try
            {
                con2.Open();
                reader2 = cmd2.ExecuteReader();
                return reader2;

            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                if (reader2 != null)
                {
                    reader2.Close();
                }

                // close the connection
                if (con2 != null)
                {
                    con2.Close();
                }
            }

        }

Thanks Boldonglen