Merging DataTables with multiple columns - Adds rows with same PK value instead of merging rows

I have 2 datatables

Table 0:

Col Time    Col A    Col B

Table 1:

Col Time    Col C    Col D

Desired Merged Table Result (final Table 0):

Cole Time  Col A  Col B  Col C  Col D

(with all same Time rows in the same row) 

  

Via merge, I would like a merged table where all rows from either table with the same time (my PK column) all appear on the same row.  The table structure does get output correctly, but those rows with identical times from Table 1 (and 2, 3, etc.)  appear as an additional row instead of merged on the same row with Table 0.  

The below code works great if I just have single columns but as soon as I add multiple columns it just adds an additional row - doesn't merge it on the same row.

 My  merge code is as follows (Note: my objDS.Tables(0) already has a pk created on "Time" column):

 For j = 1 to DTCount -1  ' DTCount is number of DataTables

   Try

     Dim pk1(0) as DataColumn

     pk1(0) = objDS.Tables(j).Columns("Time")

     objDS.Tables(j).Tables(j).PrimaryKey = pk1

     objDS.Tables(0).Merge(objDS.Tables(j))

   Catch ex as Exception

   End Try

 

Next 

 

 

 

0
solelongboard
3/14/2007 7:15:41 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

3 Replies
796 Views

Similar Articles

[PageSpeed] 40

Can't you do this in SQL instead?? Would appear much easier to me doing it that way!!!

SELECT Table1.ColTime, Table1.ColA, Table1.ColB, Table2.ColC, Table2.ColD

FROM Table1 LEFT OUTER JOIN ON Table1.ColTime = Table2.ColTime

UNION

SELECT Table2.ColTime, Table1.ColA, Table1.ColB, Table2.ColC, Table2.ColD

FROM Table2 LEFT OUTER JOIN ON Table2.ColTime = Table1.ColTime

WHERE Table1.ColTime IS NULL

0
James_2JS
3/14/2007 11:50:33 PM

The merge should work, are you sure the pk are equal?

 I tested this:

 DataSet ds = new DataSet("ds");

DataTable t = new DataTable("table");

DataTable t2 = new DataTable("table2");

t.Columns.Add("time", typeof (int));

t.Columns.Add("t", typeof (string));

t.PrimaryKey = new DataColumn[]{t.Columns[0]};

t2.Columns.Add("time", typeof(int));

t2.Columns.Add("t2", typeof(string));

t2.PrimaryKey = new DataColumn[] { t2.Columns[0] };

t.Rows.Add(new object[] {1, "test"});

t.Rows.Add(new object[] { 2, "test2" });

t2.Rows.Add(new object[] { 1, "test3" });

t2.Rows.Add(new object[] { 3, "test4" });

ds.Tables.Add(t);

ds.Tables.Add(t2);

ds.Tables[0].Merge(ds.Tables[1]);

result for table in position 0:

time       t                 t2

1           test             test3

2           test2           null

3           null             test4

 

0
rumbafum
3/15/2007 11:30:11 AM
Thanks rumbafum - I took your queue and built a simplified version (code is below)  of my tables and proved that merge does work even for dissimilar 
columns, i.e. a  [DateTime] [Decimal]  [String]  column table will  merge with another table of the same structure and with a pk on DateTime  it will successfully 
put same DateTime row data  on the same row in the resulting merged table. 
My actual code is much more complex because I have to build my tables via looping thru input variables and my column headings are dependent  on 
input variables as well as the number of input variables.  I actually merge table(1), table(2), etc. with table(0) via a loop.  I do get a merged table but it simply adds 
rows even when there is a match on my pk DateTime column. 
At this point, I'm probably in need of some type of checks I can do on my pk to help me determine why the merge in my actual code is not working. 
 
WORKING code demonstrating merging 2 tables with 3 different column types:
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<HTML>
<HEAD>
<title>Demonstration of merging 3 column datatables with differenct DataTypes</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/loadcss.css" rel="stylesheet" type="text/css">
<script language="vb" runat="server">
'<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
'<link href="styles/loadcss.css" rel="stylesheet" type="text/css">
'Page load event of the web page'

Protected Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim ds As DataSet = New DataSet("ds")
Dim t1 As DataTable = New DataTable("table")
Dim t2 As DataTable = New DataTable("table2")
Dim dc As DataColumn
Dim dr As DataRow


dc = New DataColumn("date_time", GetType(DateTime))
t1.Columns.Add(dc)
dc = New DataColumn("date_time", GetType(DateTime))
t2.Columns.Add(dc)
dc = New DataColumn("f1", GetType(Decimal))
t1.Columns.Add(dc)
dc = New DataColumn("f2", GetType(Decimal))
t2.Columns.Add(dc)

'Add string columns
dc = New DataColumn("s1", GetType(String))
t1.Columns.Add(dc)
dc = New DataColumn("s2", GetType(String))
t2.Columns.Add(dc)

dr = t1.NewRow()
dr("date_time") = "03/19/2007 00:00" ' Now
dr("f1") = 3.45
dr("s1") = "Hello"
t1.Rows.Add(dr)


dr = t2.NewRow()
dr("date_time") = "03/19/2007 00:00" ' Now
dr("f2") = 14.2
dr("s2") = "Jaco"
t2.Rows.Add(dr)

ds.Tables.Add(t1)
ds.Tables.Add(t2)


'Only ordinal ds references now . . .

Dim pk1(0) As DataColumn
pk1(0) = ds.Tables(0).Columns(0) ' ordinal assignment
ds.Tables(0).PrimaryKey = pk1

pk1(0) = ds.Tables(1).Columns(0)
ds.Tables(1).PrimaryKey = pk1

ds.Tables(0).Merge(ds.Tables(1))

dg01.DataSource = ds.Tables(0)
dg01.DataBind()


End Sub


</script>

</HEAD>
<body>
<br /><br /><br />
<span class="contentheader">Merge test - referencing datasets</span><br>

<asp:DataGrid ID="dg01" runat="server"
AutoGenerateColumns="True"
Width="50%"
HorizontalAlign="Left" ItemStyle-Font-Size="Large" >


<HeaderStyle BackColor="#99CCFF" ForeColor="Black"
HorizontalAlign="Center" Font-Bold="True" />


</asp:DataGrid>



</body>
</HTML>
0
solelongboard
3/20/2007 3:15:22 PM
Reply: