Looping through rows with ExecuteReader

Question

I'm stumped, I am trying to fill 5 textboxes from a database, not all 5 will always have data.

Example:

ID | ItemID | QType

1 | 10 | 2 Boxes

2 | 10 | 6 Boxes

3 | 11 | 1 Case

In this example it would fill QuantityType1TxtBox with 2 Boxes and QuantityType2TxtBox with 6 Boxes, while leaving the other three textboxes blank.

The following error I get when I try to run this code is: Index was outside the bounds of the array.

This error happens on this line: QuantityType2TxtBox.Text = rdr.GetString(1);

SqlCommand cmd = new SqlCommand(@"SELECT QType FROM InventoryQType
                                                WHERE ItemID = '" + itemID + "'", conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
    QuantityType1TxtBox.Text = rdr.GetString(0);
    QuantityType2TxtBox.Text = rdr.GetString(1);
    QuantityType3TxtBox.Text = rdr.GetString(2);
    QuantityType4TxtBox.Text = rdr.GetString(3);
    QuantityType5TxtBox.Text = rdr.GetString(4);
}
rdr.Close();

Answer 1

I'm not sure my switch statement is the right way to do it for this, but I think the rest of this code is useful to show you parameters and using statements.

string sql = @"SELECT QType FROM InventoryQType WHERE ItemID=@id";
using (SqlConnection conn = new SqlConnection("[put your connection string here, or reference to web.config]")) {
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(sql, conn)) {
        cmd.Parameters.Add("@id", System.Data.SqlDbType.VarChar).Value = itemID;
        SqlDataReader rdr = cmd.ExecuteReader();
        int loop = 1;
        while (rdr.Read()) {
            switch(loop){
                case 1:
                    QuantityType1TxtBox.Text = (string)rdr["QType"];
                    break;
                case 2:
                    QuantityType2TxtBox.Text = (string)rdr["QType"];
                    break;
                case 3:
                    QuantityType3TxtBox.Text = (string)rdr["QType"];
                    break;
                case 4:
                    QuantityType4TxtBox.Text = (string)rdr["QType"];
                    break;
                case 5:
                    QuantityType5TxtBox.Text = (string)rdr["QType"];
                    break;
                default:
                    break;
            }
            loop++;
        }
    }
    conn.Close();
}

Also of note, your itemID seems to be a varchar in your example. I assume it is actually an int, so you'll need to change the 'VarChar' part of the Parameter to be 'Int' if that is true.

Answer 2

Lots of ways to do this, but a crude implementation to get you started might look something like:

List<string> values;
while(rdr.Read())
{
    values.Add(rdr.GetString(0));
    if (values.Count == 5) break;
}

if (values.Count > 0) QuantityType1TxtBox.Text = values[0];
if (values.Count > 1) QuantityType2TxtBox.Text = values[1];    
if (values.Count > 2) QuantityType3TxtBox.Text = values[2];
if (values.Count > 3) QuantityType4TxtBox.Text = values[3];
if (values.Count > 4) QuantityType5TxtBox.Text = values[4];

You might want to consider (a) separating your data access code into a separate class / method that returns a list of values, and (b) putting your text boxes into a collection so you don't need repeated code to assign values to them.