how to join 2 tables in a data set
-
Hi all I have 2 tables in the same data set like this:
string sqlCus="select * from Customers";
string sqlOrder="select * from Orders";SqlDataAdapter daCus=new SqlDataAdapter(sqlCus,conn);
SqlDataAdapter daOrder=new SqlDataAdapter(sqlOrder,conn);DataSet ds=new DataSet();
daCus.Fill(ds,"Customers");
daOrder.Fill(ds,"Orders");now I want to get the OrderID column in Orders table and ContactName (CustomerName) in the Customers table according to the CustomerID column in the Orders table then show both column in a DataGridView control, how to do that? note: not use stored procedure and Linq
-
Hi all I have 2 tables in the same data set like this:
string sqlCus="select * from Customers";
string sqlOrder="select * from Orders";SqlDataAdapter daCus=new SqlDataAdapter(sqlCus,conn);
SqlDataAdapter daOrder=new SqlDataAdapter(sqlOrder,conn);DataSet ds=new DataSet();
daCus.Fill(ds,"Customers");
daOrder.Fill(ds,"Orders");now I want to get the OrderID column in Orders table and ContactName (CustomerName) in the Customers table according to the CustomerID column in the Orders table then show both column in a DataGridView control, how to do that? note: not use stored procedure and Linq
You could do one query and join the tables on the query string sql = "select ContactName, Orders.OrderID from Customers Left Outer Join Order ON CustomerID = Order.CustomerID"; The above should work, you may just have to change the column names to what you want. You could also add an Order by statement in the end of that if you wanted. This link may help you in the future with joining tables in sql queries. http://en.wikipedia.org/wiki/Join_(SQL)[^] hope that helps you. Matthew Vass QA Analyst mvass@hostmysite.com http://www.hostmysite.com?utm_source=bb[^]
-
You could do one query and join the tables on the query string sql = "select ContactName, Orders.OrderID from Customers Left Outer Join Order ON CustomerID = Order.CustomerID"; The above should work, you may just have to change the column names to what you want. You could also add an Order by statement in the end of that if you wanted. This link may help you in the future with joining tables in sql queries. http://en.wikipedia.org/wiki/Join_(SQL)[^] hope that helps you. Matthew Vass QA Analyst mvass@hostmysite.com http://www.hostmysite.com?utm_source=bb[^]
-
Thank you for your answer, but what I wanted to know is how to join 2 tables in the same data set using data relation. I do know how to join 2 tables using sql's select command, but this one is not. Could you please point me out for this? Thanks
I'm sorry the first answer did not help you, I'm guessing you have a reason to populate two separate datasets and then parse through them to get your info. You may want to look at this article, it explains how to use select/sort things using your dataset and a datatable. http://msdn.microsoft.com/en-us/library/system.data.datatable.select.aspx[^] Also you will most likely have to do a dataset copy then dataset.add to add the columns to the copied dataset in order to get the results you want. Not sure if this is exactly what your looking for but hope this points you in the right direction.
Matthew Vass QA Analyst mvass@hostmysite.com HostMySite.com