CODEWITHSUNDEEP
c#sqlsql-servervisual-studioexecutereader
Cihan Kurt
Cihan Kurt

Reputation: 373

SQL Server Column to Combobox?

I have a table called Product. One of the columns of this table is called Naam. It's the name of the product. When you press on the button, all product names have to be added to the combobox.

So if I have 2 products: Cola & Fanta.

The program has to show only the column Naam in the combobox. Not the other columns.

I have already this for my button, but it doesn't work. 

db.AlleProducten("Select Naam from Product;", Product);
cb_product.Items.Add(Product.Naam);

And this is the method that runs the query:

public void AlleProducten(string commandText, product Product)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(commandText, conn))
    {
        conn.Open();

        using (var rdr = cmd.ExecuteReader())
        {
             if (rdr.HasRows)
             {
                 rdr.Read();
                 Product.Naam = rdr.GetString(1);
                 conn.Close();
             }
        }
    }
}

The error:

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll

Additional information: De index ligt buiten de matrixgrenzen.

The additional information is in Dutch. Translated to English:

The index is located outside of the array bounds.

Upvotes: 2

Views: 1656

Answers (2)

Steve
Steve

Reputation: 216343

The first problem in your code is when you try to retrieve the value at index 1 of your SqlDataReader. Your query has only one column and in NET arrays start at index zero, so you need to retrieve the Naam value using this line 

Product.Naam = rdr.GetString(0);

However, if you have more than one record to extract the Naam value then you need to loop using the SqlDataReader until it return false from the Read method and store the Naam values retrieve in some kind of collection structure.
I suggest to use a List<string>

public List<string> AlleProducten(string commandText)
{
    List<string> names = new List<string>();
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(commandText, conn))
    {
        conn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while(rdr.Read())
                names.Add(rdr.GetString(0));
        }
        conn.Close();
   }
   return names;

}

The code above loops over the returned results of the SqlDataReader and add every Naam to a List of strings and finally returns the list to the caller.
In this way you can assign the return value of the AlleProducten method to the DataSource of the combobox

List<string>result = db.AlleProducten("Select Naam from Product;");
cb_product.DataSource = result;

Upvotes: 3

McNets
McNets

Reputation: 10827

1 - You are out of range due you are using rdr.GetString(1) instead of rdr.GetString(0)

2 - There isn't any ComboBox in your code.

using (var rdr = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        Product.Naam = rdr.GetString(0);
        YourComboBox.Items.Add(Product.Naam); 
    }
}

Take a look at SqlCommand.ExecuteReader documentation.

Upvotes: 1

Related Questions