How to fill one datagrid from multiple tables!!! Please help me!!!

microsoft.public.dotnet.framework.adonet

9 Nov 2005 9:06 AM

Letnak

Hello All,
In the past few weeks I'm trying to find an answer to my query but no
results.
My question is very simple (I think).

I have tow tables width relations between them.
I want to show/fill my datagrid with the join result from the tow
tables.

My Code:
--------------------------

<%@Page Language="VB"%>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>
<script language="vb" runat="server">
Sub Page_Load()
Dim strConnect As String
strConnect = "My DATAConnection"

Dim strSelectBook1 As String
strSelectBook1 = "SELECT * FROM table1"
Dim strSelectBook2 As String
strSelectBook2 = "SELECT * FROM table2"

Dim objDataSet As New DataSet()
Dim objConnect As New OleDbConnection(strConnect)
Dim objCommand As New OleDbCommand()
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.Text
objCommand.CommandText = strSelectBook1
Dim objDataAdapter As New OleDbDataAdapter()

objDataAdapter.SelectCommand = objCommand
objDataAdapter.Fill(objDataSet, "Table1")
objCommand.CommandText = strSelectBook2
objDataAdapter.Fill(objDataSet, "Table2")

Dim objRelation As DataRelation
objRelation = New DataRelation("Tables",
objDataSet.Tables("Table1").Columns("UserID"),
objDataSet.Tables("Table2").Columns("UserID"))
objDataSet.Relations.Add(objRelation)
dgrTables.DataSource = objDataSet.Tables
dgrTables.DataBind()
dgrRelations.DataSource = objDataSet.Relations
dgrRelations.DataBind()
Dim objDataView As New DataView()
objDataView = objDataSet.Tables("Table1").DefaultView
dgrBooksData.DataSource = objDataView
dgrBooksData.DataBind()
objDataView = objDataSet.Tables("Table2").DefaultView
dgrAuthorsData.DataSource = objDataView
dgrAuthorsData.DataBind()

End Sub
</script>
<html>
<body>
<b>Contents of DataSet.Tables("Table1")</b>
<asp:datagrid id="dgrBooksData" runat="server" /><br />
<b>Contents of DataSet.Tables("Table2")</b>
<asp:datagrid id="dgrAuthorsData" runat="server" /><br />

</body>
</html>
----------------------
As you can see I succeeded to fill my grids with the two tables.
I want to fill a new datagrid with the two tables (according) to the
relations.

Please Help Me..

Thank you very much
K

9 Nov 2005 9:31 AM

Cor Ligthert [MVP]

Hi Letnak,

You know this sample on MSDN

http://support.microsoft.com/default.aspx?scid=kb;en-us;308485

I hope this helps,

Cor

Show quote

"Letnak" <itzik.kan***@gmail.com> schreef in bericht
news:1131527176.224160.110790@g44g2000cwa.googlegroups.com...
> Hello All,
> In the past few weeks I'm trying to find an answer to my query but no
> results.
> My question is very simple (I think).
>
> I have tow tables width relations between them.
> I want to show/fill my datagrid with the join result from the tow
> tables.
>
> My Code:
> --------------------------
>
> <%@Page Language="VB"%>
> <%@Import Namespace="System.Data" %>
> <%@Import Namespace="System.Data.OleDb" %>
> <script language="vb" runat="server">
> Sub Page_Load()
> Dim strConnect As String
> strConnect = "My DATAConnection"
>
> Dim strSelectBook1 As String
> strSelectBook1 = "SELECT * FROM table1"
> Dim strSelectBook2 As String
> strSelectBook2 = "SELECT * FROM table2"
>
>
> Dim objDataSet As New DataSet()
> Dim objConnect As New OleDbConnection(strConnect)
> Dim objCommand As New OleDbCommand()
> objCommand.Connection = objConnect
> objCommand.CommandType = CommandType.Text
> objCommand.CommandText = strSelectBook1
> Dim objDataAdapter As New OleDbDataAdapter()
>
> objDataAdapter.SelectCommand = objCommand
> objDataAdapter.Fill(objDataSet, "Table1")
> objCommand.CommandText = strSelectBook2
> objDataAdapter.Fill(objDataSet, "Table2")
>
> Dim objRelation As DataRelation
> objRelation = New DataRelation("Tables",
> objDataSet.Tables("Table1").Columns("UserID"),
> objDataSet.Tables("Table2").Columns("UserID"))
> objDataSet.Relations.Add(objRelation)
> dgrTables.DataSource = objDataSet.Tables
> dgrTables.DataBind()
> dgrRelations.DataSource = objDataSet.Relations
> dgrRelations.DataBind()
> Dim objDataView As New DataView()
> objDataView = objDataSet.Tables("Table1").DefaultView
> dgrBooksData.DataSource = objDataView
> dgrBooksData.DataBind()
> objDataView = objDataSet.Tables("Table2").DefaultView
> dgrAuthorsData.DataSource = objDataView
> dgrAuthorsData.DataBind()
>
> End Sub
> </script>
> <html>
> <body>
> <b>Contents of DataSet.Tables("Table1")</b>
> <asp:datagrid id="dgrBooksData" runat="server" /><br />
> <b>Contents of DataSet.Tables("Table2")</b>
> <asp:datagrid id="dgrAuthorsData" runat="server" /><br />
>
> </body>
> </html>
> ----------------------
> As you can see I succeeded to fill my grids with the two tables.
> I want to fill a new datagrid with the two tables (according) to the
> relations.
>
> Please Help Me..
>
> Thank you very much
> K
>