SQL Server Column to Combobox?

Question

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.

You have only one column in your query. This column can be retrieved using index 0 not index 1. In NET arrays indexes start at zero — Steve, Jan 07 '17 at 22:52
so because I already Select only 1 column with the query, I have only 1 column? Thought I needed the index of the whole table. — Cihan Kurt, Jan 07 '17 at 22:57
Yes exactly, the SqlDataReader internal array is dimensioned according to the number of columns retrieved. — Steve, Jan 07 '17 at 23:07

Steve · Answer 1 · 2017-01-07T23:06:02.217

3

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;

edited Jan 07 '17 at 23:06

answered Jan 07 '17 at 22:59

Steve

213,761
22
232
286

Great! I was also thinking of using a List. Before the edit you had names.Add(rdr.GetString(1)); and you changed it to 0. The same mistake as mine? :P or does it have a specific reason? – Cihan Kurt Jan 07 '17 at 23:10
No it was a simple copy/paste mistake – Steve Jan 07 '17 at 23:11
Why are you using a while loop instead of a for each loop? It does the same thing, right? At least school teached me that. – Cihan Kurt Jan 07 '17 at 23:22
because we need to check the return value of rdr.Read to stop the loop. How do you do that with a foreach loop? – Steve Jan 07 '17 at 23:25
Counting the colums adding that to int A and run the foreach for int A? – Cihan Kurt Jan 07 '17 at 23:27
You are looping on the rows not on the columns. An undeterminate number of rows (now they are only two (Coca & Fanta) but in future you could have Pepsi or ...choose your favourite beer here.... – Steve Jan 07 '17 at 23:29
My mistake, I meant rows. So if I use: Select Count and int A = cmd.ExecuteNonQuery(); with a foreach loop with Int A it won't be the same? – Cihan Kurt Jan 07 '17 at 23:35
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132599/discussion-between-steve-and-gigitex). – Steve Jan 07 '17 at 23:36

score 1 · Accepted Answer · answered Jan 07 '17 at 22:56

1

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.

answered Jan 07 '17 at 22:56

McNets

10,352
3
32
61

There is a combobox, it's called cb_product. But that one is in my form. The method for executing is in another class. I can't use "System.Windows.Forms" in my class. Thanks to school. I fixed the problem with the index, just a stupid mistake by me. But when I run this code, I only get the first 'Product" in my table. The second one doesn't pop up – Cihan Kurt Jan 07 '17 at 23:00
There is no WHERE clause in your query. `Select Naam from Product Where ProductId = SelectedProduct` – McNets Jan 07 '17 at 23:03
Then u get the selected product. The combobox has to contain all the productnames in the database. So it should run Select Name from Product. If it gets X colums, it should add all X productnames to the combobox. Sorry for my bad explanation – Cihan Kurt Jan 07 '17 at 23:08
It's unclear to me, you said: `Coca & Fanta`, that is 2 products, how do you expect to fetch 2 products using only Naam? – McNets Jan 07 '17 at 23:17
Yes, that was my problem. I am trying to learn using these classes. Sorry. I had to use a loop – Cihan Kurt Jan 07 '17 at 23:19

SQL Server Column to Combobox?

2 Answers2