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...
Expand|Select|Wrap|Line Numbers
- 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());
- }
- }
Here is the statement to get the products
Expand|Select|Wrap|Line Numbers
- SqlCommand getProducts = new SqlCommand("select Product from Products where ProductID = '" + Key + "' ", sqlcon);
- LBproductsOrderHist.Items.Add(getProducts.ExecuteScalar());
...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!