469,612 Members | 2,671 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,612 developers. It's quick & easy.

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

JnrJnr
88
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...

Expand|Select|Wrap|Line Numbers
  1. SqlCommand getProductKeys = new SqlCommand("select ProductKey from Ordered_Products where OrderKey = '" + cbxOrderNumber.SelectedItem + "' ", sqlcon);
  2.             SqlDataReader readProdKeys = getProductKeys.ExecuteReader();            
  3.             while (readProdKeys.Read())
  4.             {
  5.                 Key = readProdKeys["ProductKey"].ToString();
  6.                 foreach (var item in Key)
  7.                 {
  8.                   MessageBox.Show("Product Key " + item.ToString());   
  9.                 }
  10.  
  11.             }
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

Expand|Select|Wrap|Line Numbers
  1. SqlCommand getProducts = new SqlCommand("select Product from Products where ProductID = '" + Key + "' ", sqlcon);
  2.             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!
Sep 13 '10 #1

✓ answered by 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_products).

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
Expand|Select|Wrap|Line Numbers
  1. SELECT Products.Product 
  2.   FROM Products 
  3.   JOIN Ordered_Products
  4.     ON Products.ProductID = Ordered_Products.ProductKey
  5.   WHERE Ordered_Products.OrderKey = ... cbxOrderNumber.SelectedItem ...
  6.  

2 3362
Christian Binder
218 Expert 100+
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_products).

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
Expand|Select|Wrap|Line Numbers
  1. SELECT Products.Product 
  2.   FROM Products 
  3.   JOIN Ordered_Products
  4.     ON Products.ProductID = Ordered_Products.ProductKey
  5.   WHERE Ordered_Products.OrderKey = ... cbxOrderNumber.SelectedItem ...
  6.  
Sep 13 '10 #2
JnrJnr
88
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...

Expand|Select|Wrap|Line Numbers
  1.  //GET QUANTITY
  2.             sqlcon.Open();
  3.             SqlCommand getQuantity = new SqlCommand("select Quantity from QuantityTable where OrderKey = '" + cbxOrderNumber.SelectedItem + "' ", sqlcon);
  4.             lblQuantityOrderHist.Text = getQuantity.ExecuteScalar().ToString();
  5.             sqlcon.Close();
  6.  
  7.             //GET PRODUCTS 
  8.  
  9.             //first get amount of items with the specific id
  10.             sqlcon.Open();
  11.             SqlCommand getKeysQuant = new SqlCommand("select count(ProductKey) as amount from Ordered_Products where OrderKey = '" + cbxOrderNumber.SelectedItem + "' ", sqlcon);
  12.             IndexCount = int.Parse(getKeysQuant.ExecuteScalar().ToString());
  13.             sqlcon.Close();
  14.  
  15.             int[] index = new int[IndexCount];
  16.             //put all items in array
  17.             for (int i = 0; i != IndexCount; )
  18.             {
  19.                 sqlcon.Open();
  20.                 SqlCommand getProductKeys = new SqlCommand("select ProductKey from Ordered_Products where OrderKey = '" + cbxOrderNumber.SelectedItem + "' ", sqlcon);
  21.                 SqlDataReader readProdKeys = getProductKeys.ExecuteReader();
  22.                 while (readProdKeys.Read())
  23.                 {
  24.                     index[i] = int.Parse(readProdKeys["ProductKey"].ToString());
  25.                     i++;
  26.                 }
  27.  
  28.                 sqlcon.Close();
  29.                 break;
  30.             }
  31.  
  32.             foreach (var item in index)
  33.             {
  34.                 sqlcon.Open();
  35.                 SqlCommand getProducts = new SqlCommand("select Product from Products where ProductID = '" + item + "' ", sqlcon);
  36.                 SqlDataReader readProducts = getProducts.ExecuteReader();
  37.                 while (readProducts.Read())
  38.                 {
  39.                     string product = readProducts["Product"].ToString();
  40.                     if (LBproductsOrderHist.Items.Contains(product))
  41.                     {
  42.  
  43.                     }
  44.                     else
  45.                     {
  46.                         LBproductsOrderHist.Items.Add(product);
  47.                     }
  48.  
  49.                 }
  50.                 sqlcon.Close();
  51.             }
Sep 14 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by is_grizzly_adams | last post: by
reply views Thread by Carl Vondrick | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.