473,288 Members | 1,743 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

how to bind a DataGrid to a join of two tables in a DataSet?

I know how to create a DataAdapter that loads data from a data source
into a table in a typed DataSet, and how to set the DataSource and
DataMember properties of a DataGrid so that at run time it will load
the data from that table in the DataSet.

Assuming I've got two tables in the DataSet, how can I set the
DataSource property of the DataGrid to be an inner join of the two
tables in the DataSet?

If I want the DataGrid to get data from just one table, I set the
DataSource property to be an instance of the typed DataSet, and the
DataMember property to be the table where I want to get the data. But
if I want to do an inner join, it's not intuitive to me what to do
instead, and none of the examples in my ASP books show how to do it.

I could create the *DataAdapter* so that it does an inner join to load
the data into the DataSet in the first place. But that's inefficient
if I'm already loading the contents of Table1 and Table2 into the
DataSet for use by some other controls on the page, then if I load a
join of Table1 and Table2 into the DataSet as well, then I'd probably
be loading the entire contents of at least one of the tables twice.
Doing a join on data already in memory seems more efficient.

-Bennett
Nov 18 '05 #1
2 8858
Hi,

You can add the relationship between the two tables and then get the
parent/child rows' values when databinding. The following example uses the
Northwind database, maybe it will be of help to you:

void BindGrid()
{
System.Data.DataSet ds =
new System.Data.DataSet();
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
@"server=(local);database=Northwind;user id=user;password=pass");
System.Data.SqlClient.SqlDataAdapter da =
new System.Data.SqlClient.SqlDataAdapter(
@"SELECT CustomerID, CompanyName FROM Customers",
conn);
conn.Open();
da.Fill(ds, "Customers");
da = new System.Data.SqlClient.SqlDataAdapter(
@"SELECT OrderID, OrderDate, CustomerID, ShipAddress FROM Orders",
conn);
da.Fill(ds, "Orders");
ds.Relations.Add("CustID",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
DataGrid1.DataSource = ds.Tables["Orders"];
DataGrid1.DataBind();
}

<asp:datagrid id="DataGrid1" runat="server" autogeneratecolumns="False">
<columns>
<asp:templatecolumn headertext="Order ID"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "OrderID")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Order Date"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "OrderDate", "{0:dd MMM yyyy}")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Customer ID"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "CustomerID")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Company Name"><itemtemplate><%#
(Container.DataItem as
System.Data.DataRowView).Row.GetParentRow("CustID" )["CompanyName"]
%></itemtemplate></asp:templatecolumn>
</columns>
</asp:datagrid>

Hope this helps
Martin
"Bennett Haselton" <be*****@peacefire.org> wrote in message
news:e6**************************@posting.google.c om...
I know how to create a DataAdapter that loads data from a data source
into a table in a typed DataSet, and how to set the DataSource and
DataMember properties of a DataGrid so that at run time it will load
the data from that table in the DataSet.

Assuming I've got two tables in the DataSet, how can I set the
DataSource property of the DataGrid to be an inner join of the two
tables in the DataSet?

If I want the DataGrid to get data from just one table, I set the
DataSource property to be an instance of the typed DataSet, and the
DataMember property to be the table where I want to get the data. But
if I want to do an inner join, it's not intuitive to me what to do
instead, and none of the examples in my ASP books show how to do it.

I could create the *DataAdapter* so that it does an inner join to load
the data into the DataSet in the first place. But that's inefficient
if I'm already loading the contents of Table1 and Table2 into the
DataSet for use by some other controls on the page, then if I load a
join of Table1 and Table2 into the DataSet as well, then I'd probably
be loading the entire contents of at least one of the tables twice.
Doing a join on data already in memory seems more efficient.

-Bennett

Nov 18 '05 #2
Thanks, that was very generous of you to post a complete example with
working code -- I modified the example for my project and it worked as
well!

I was hoping to find a smooth way to do it in the designer, just since
that reduces the chances of coding errors, but sometimes you have to
*GROAN* type.

-Bennett

"Martin Dechev" <de*******@hotmail.com> wrote in message news:<#5**************@TK2MSFTNGP11.phx.gbl>...
Hi,

You can add the relationship between the two tables and then get the
parent/child rows' values when databinding. The following example uses the
Northwind database, maybe it will be of help to you:

void BindGrid()
{
System.Data.DataSet ds =
new System.Data.DataSet();
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
@"server=(local);database=Northwind;user id=user;password=pass");
System.Data.SqlClient.SqlDataAdapter da =
new System.Data.SqlClient.SqlDataAdapter(
@"SELECT CustomerID, CompanyName FROM Customers",
conn);
conn.Open();
da.Fill(ds, "Customers");
da = new System.Data.SqlClient.SqlDataAdapter(
@"SELECT OrderID, OrderDate, CustomerID, ShipAddress FROM Orders",
conn);
da.Fill(ds, "Orders");
ds.Relations.Add("CustID",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
DataGrid1.DataSource = ds.Tables["Orders"];
DataGrid1.DataBind();
}

<asp:datagrid id="DataGrid1" runat="server" autogeneratecolumns="False">
<columns>
<asp:templatecolumn headertext="Order ID"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "OrderID")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Order Date"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "OrderDate", "{0:dd MMM yyyy}")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Customer ID"><itemtemplate><%#
DataBinder.Eval(Container.DataItem, "CustomerID")
%></itemtemplate></asp:templatecolumn>
<asp:templatecolumn headertext="Company Name"><itemtemplate><%#
(Container.DataItem as
System.Data.DataRowView).Row.GetParentRow("CustID" )["CompanyName"]
%></itemtemplate></asp:templatecolumn>
</columns>
</asp:datagrid>

Hope this helps
Martin
"Bennett Haselton" <be*****@peacefire.org> wrote in message
news:e6**************************@posting.google.c om...
I know how to create a DataAdapter that loads data from a data source
into a table in a typed DataSet, and how to set the DataSource and
DataMember properties of a DataGrid so that at run time it will load
the data from that table in the DataSet.

Assuming I've got two tables in the DataSet, how can I set the
DataSource property of the DataGrid to be an inner join of the two
tables in the DataSet?

If I want the DataGrid to get data from just one table, I set the
DataSource property to be an instance of the typed DataSet, and the
DataMember property to be the table where I want to get the data. But
if I want to do an inner join, it's not intuitive to me what to do
instead, and none of the examples in my ASP books show how to do it.

I could create the *DataAdapter* so that it does an inner join to load
the data into the DataSet in the first place. But that's inefficient
if I'm already loading the contents of Table1 and Table2 into the
DataSet for use by some other controls on the page, then if I load a
join of Table1 and Table2 into the DataSet as well, then I'd probably
be loading the entire contents of at least one of the tables twice.
Doing a join on data already in memory seems more efficient.

-Bennett

Nov 18 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Daniel Rossi | last post by:
Hi there i am trying to work out the most efficient way to list say multipl= e categories of entries, the database is quite large about 200 meg.=20 I would like to know if using join tables is...
1
by: geek | last post by:
Hi all, I have a datagrid I want to store the data in the datagrid to a dataset. Can anybody please tell me how to do this. TIA.
1
by: Ramki | last post by:
hi guys, How to Add a Blank row in a datagrid without using dataset Please reply ASAP. Very urgent. Thanks inadvance.
2
by: Mark | last post by:
Is it possible to bind Datagrid web control to data on the client side Thanks
5
by: Mark | last post by:
Can I bind datagrid to an xml string (not to file)? For example, the result of transformation?
1
by: WayneM | last post by:
I have compact framework app that I was trying to test out on a windows form, but I cannot get past the very first step of simply filling a DataGrid from a DataSet. My code is Dim sqlStmt As...
2
by: zwasdl | last post by:
I'm using access to connect to Oracle via ODBC. I can also connect to Oracle via sql*plus. Can I write a query to join tables from different schema? If so, how? Thanks a million! Wei
4
by: kuldeep singh sethi | last post by:
Hi All Plz give me code how to bind datagrid with dataset. And one thing more i want that is i want to submit Roll No . at enquiry.aspx page and now after click on submit i want to redirect that...
0
by: =?Utf-8?B?UHJhYmFrYXIgU2FtaXlhcHBhbg==?= | last post by:
I need to add a new to the asp.net datagrid without using dataset .Since when we use dataset to add row it took more time to load the records.Gimme a Solution .thanks
21
patjones
by: patjones | last post by:
Hi all: My newest project involves creating a small help desk database for our customer service division to use. What will happen is that an employee will call up, explain his/her issue, and...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.