How to select/get multiple items in SQL database table using the item's foreign keys.

[JnrJnr]

I have two SQL database tables. One contains various products(with unique primary keys) and the other contains information related to the products aswel as the product's foreign keys.

What I want to do is first get all the foreign keys(using SQL queries/select statements) and "store" them so that I can then, according to those foreign keys, get the actual product related to each foreign key.
My database is all fine but my problem is getting the products.

Here I get all the foreign keys from my table...

SqlCommand getProductKeys = new SqlCommand("select ProductKey from Ordered_Products where OrderKey = '" + cbxOrderNumber.SelectedItem + "' ", sqlcon);
            SqlDataReader readProdKeys = getProductKeys.ExecuteReader();            
            while (readProdKeys.Read())
            {
                Key = readProdKeys["ProductKey"].ToString();
                foreach (var item in Key)
                {
                  MessageBox.Show("Product Key " + item.ToString());   
                }
 
            }

Now that I have the foreign keys I cant seem to get it right to select(get) the products.
Here is the statement to get the products

SqlCommand getProducts = new SqlCommand("select Product from Products where ProductID = '" + Key + "' ", sqlcon);
            LBproductsOrderHist.Items.Add(getProducts.ExecuteScalar());

What Iv'e tried
...Iv'e tried putting the getProducts command in the foreach statemant in the getProductKeys command but I get the exception of "The connection was not closed. The connection's current state is open"
(I know you have to close a connection after you are done with the command but I cant close the connection while I'm still bussy getting information in the SQL_dataReader)
Obviousely it looks like you can not have a SQLcommand in another SQLcommand if the first command has not closed.

My presumption
I know I have to (or can) use an array to "store" the foreign keys so that I can get the products using the getProducts command without any exceptions.

Any tips or help will be appreciated!

[Christian Binder]

I think the problem is, that you are querying the first result-set while you want to query another, so your SqlConnection is busy. I'd try to open a second connection for the inner query (products) which hopefully doesn't interfere with the outer query (ordered_produc ts).

But I think, it would be better to optimize your query, so it returns the products with one query using join.
e.g. like this

SELECT Products.Product 
  FROM Products 
  JOIN Ordered_Products
    ON Products.ProductID = Ordered_Products.ProductKey
  WHERE Ordered_Products.OrderKey = ... cbxOrderNumber.SelectedItem ...
 

[JnrJnr]

Thank you Christian Binder for you reply.
Although I did also find a way to make it work which is a bit longer and takes a bit longer to run, I think opening a second connection for the inner query (products) was the quickest way to go.

Just for interest's sake, here's what I did...

 //GET QUANTITY
            sqlcon.Open();
            SqlCommand getQuantity = new SqlCommand("select Quantity from QuantityTable where OrderKey = '" + cbxOrderNumber.SelectedItem + "' ", sqlcon);
            lblQuantityOrderHist.Text = getQuantity.ExecuteScalar().ToString();
            sqlcon.Close();
 
            //GET PRODUCTS 
 
            //first get amount of items with the specific id
            sqlcon.Open();
            SqlCommand getKeysQuant = new SqlCommand("select count(ProductKey) as amount from Ordered_Products where OrderKey = '" + cbxOrderNumber.SelectedItem + "' ", sqlcon);
            IndexCount = int.Parse(getKeysQuant.ExecuteScalar().ToString());
            sqlcon.Close();
 
            int[] index = new int[IndexCount];
            //put all items in array
            for (int i = 0; i != IndexCount; )
            {
                sqlcon.Open();
                SqlCommand getProductKeys = new SqlCommand("select ProductKey from Ordered_Products where OrderKey = '" + cbxOrderNumber.SelectedItem + "' ", sqlcon);
                SqlDataReader readProdKeys = getProductKeys.ExecuteReader();
                while (readProdKeys.Read())
                {
                    index[i] = int.Parse(readProdKeys["ProductKey"].ToString());
                    i++;
                }
 
                sqlcon.Close();
                break;
            }
 
            foreach (var item in index)
            {
                sqlcon.Open();
                SqlCommand getProducts = new SqlCommand("select Product from Products where ProductID = '" + item + "' ", sqlcon);
                SqlDataReader readProducts = getProducts.ExecuteReader();
                while (readProducts.Read())
                {
                    string product = readProducts["Product"].ToString();
                    if (LBproductsOrderHist.Items.Contains(product))
                    {
 
                    }
                    else
                    {
                        LBproductsOrderHist.Items.Add(product);
                    }
 
                }
                sqlcon.Close();
            }