How to show in a datagrid columns of different datatables?

[Luis Esteban Valencia]

I have this
i need to show the datagrid with columns of 2 datatables


Dim myadap As New SqlDataAdapter

myconn.Open()

myadap.TableMappings().Add("Table", "vistaUsuarios")

Dim mysqlcmd As New SqlCommand("select * from vistaUsuarios order by
strnombre asc", myconn)

mysqlcmd.CommandType = CommandType.Text

myadap.SelectCommand = mysqlcmd

myadap.Fill(myds)

myconn.Close()



Dim myadapccos As New SqlDataAdapter

myconn2.Open()

myadapccos.TableMappings.Add("Table", "gn_ccost")

Dim mysqlcmdcos As New SqlCommand("select * from gn_ccost", myconn2)

mysqlcmdcos.CommandType = CommandType.Text

myadapccos.SelectCommand = mysqlcmdcos

myadapccos.Fill(myds)

myconn2.Close()

Dim dr As DataRelation

Dim dc1 As DataColumn

Dim dc2 As DataColumn

dc1 = myds.Tables("vistausuarios").Columns("strIdCentroCosto")

dc2 = myds.Tables("gn_ccos").Columns("cos_ccos")

dr = New System.Data.DataRelation("EmpleadosCentros", dc1, dc2)

myds.Relations.Add(dr)

 

[Guest]

Sorry, I don't totally understand the question. Can you be more specific as
to what you're trying to do? Based on your comments, I'm guessing you're
trying to handle a parent/child relationship in the same grid, in which case
it's usually easier to create a *single* datasource, either by doing a join
on the server side, or by doing the selects as you're doing them and adding
the result of the one as a new datacolumn to the other. You can format the
new data either when you add it to the datasource, or at run-time via
ItemDataBound.

Bill

 

[Curt_C [MVP]]

Can't you use a JOIN in the SQL call to get the two tables to one dataset?

 

[Luis Esteban Valencia]

I cant do the Join on the sql server because one server cant be LINKED to
the another server.

 

[Luis Esteban Valencia]

No becase the servers cant be linked I have full access to one server and
only reading to the another server.

 

[Guest]

If you can't do the join, then just do the two selects as you're doing, but
add the columns from the second dataset to the first in your code.

Is this a parent/child relationship, or a one-to-one? It doesn't really
matter, but if it's a parent/child you need to think about how you want to
traverse the child data (what I'll do in simple cases is add the child data
as a single string to each parent row, e.g. Customer is the parent, Invoices
is the child, and add a string like Invoice 1<br>Invoice 2<br>Invoice 3<br>,
etc. to the parent row).

Bill

 

[Luis Esteban Valencia]

Please tell me the code to do that

Add columns from datattable gn_ccost to the datatable vistaempleados.

Thanks

 

[Guest]

I'm still not sure how your tables are related, but here's one "manual" way
to do what I mean (vb syntax):

' Add a column to your main table. Each row will be initialized to null.
Check out
' overrides of datacolumn() to create columns of certain data type, etc.
Also look
' at using AddRange instead of Add.
Dim dt As DataTable = YourDataTable1
dt.Columns.Add(New DataColumn("MyNewColumn"))

' Loop through the rows in your main table, go get the matching child data,
and add
' it to the new column(s) for that row.
For Each dr As DataRow In dt.Rows
dr("MyNewColumn") = "My additional data for this row"
Next

There are more efficient ways of doing this, but if your datatables are
reasonably small this will be fine. Check out dataset.merge for one. Also,
using datarelations will probably help.

hth,

Bill