Click here to Skip to main content
14,877,803 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I have 2 datatables which contain following datas

Datatable1
SC   SUB     EXM
1	1449-2	AB101
1	1449-2	AC066
17	1449-2	AD071
37	1449-3	AD073


Datatabl2
IC   SUB     EXM
5	1449-2	AB101
4	1449-2	AC066
2	1449-2	AD071
21	1449-2	CD028



From above 2 datatale i want to merge with all columns. Here i want output like
IC  SC   SUB     EXM
5	1   1449-2	AB101
4	1   1449-2	AC066
2	17  1449-2	AD071
21	0   1449-2	CD028
0   37  1449-3	AD073


If values not have in corresponding SUB and EXM for IC or SC as 0

What I have tried:

I tried like this

VB
Dim ds As DataSet = New DataSet()
            ds.Tables.Add(DT1)
            ds.Tables.Add(DT2)
            ds.Relations.Add("TheRelation", DT1.Columns("EXM"), DT2.Columns("EXM"))
            objDTPT.Columns.Add("SC", System.Type.[GetType]("System.String"), "Parent.SC")


Not works what i except and also cant add 0 in not have values

I tried this way also,

VB
Dim JoinResult =
          From o In DT1.AsEnumerable
          Join a In DT2.AsEnumerable
          On a.Field(Of String)("EXM") Equals o.Field(Of String)("EXM")
          Select New With
        {
          Key .SUB = a.Field(Of String)("SUB"),
          Key .EXM = o.Field(Of String)("EXM"),
          Key .IC = a.Field(Of String)("IC"),
          Key .SC = o.Field(Of String)("SC")
        }


getting error as
Unable to cast object of type 'System.Int32' to type 'System.String'.


Thanks and Regards
Aravind
Posted
Updated 28-Feb-21 22:48pm
v3

211 questions and you can't even work out an error message like that for yourself?
You need to go back and learn the basics properly ... whatever you think you are doing, you don't understand enough to be trying to do things like this ...

Look at the error message:
Unable to cast object of type 'System.Int32' to type 'System.String'.
It couldn't be much clearer.
One or more of your datatable items is not a string, it's an integer - probably either IC or SC - and the system will not allow a cast from int to string, you would need to use ToString or better keep them as integer values ...
   
Comments
Aravindba 1-Mar-21 2:31am
   
yes thanks, i changed SC and IC as integer, but this also not merge, bcz only common fields it will return, totally 5 rows, from that one row is different in both datatable, so it will return as 3 rows, but i want 5 rows.
Take a look at below example:

VB.NET
#Region "sample data creation"
 im dt1 As DataTable = New DataTable()
Dim dt2 As DataTable = New DataTable()

dt1.Columns.AddRange(New DataColumn() _
	{ _
		New DataColumn("SC", Type.GetType("System.Int32")), _
		New DataColumn("SUB", Type.GetType("System.String")), _
		New DataColumn("EXM", Type.GetType("System.String")) _
	})

dt1.Rows.Add(New Object(){1, "1449-2", "AB101"})
dt1.Rows.Add(New Object(){1, "1449-2", "AC066"})
dt1.Rows.Add(New Object(){17, "1449-2", "AD071"})
dt1.Rows.Add(New Object(){37, "1449-3", "AD073"})

dt2.Columns.AddRange(New DataColumn() _
	{ _
		New DataColumn("IC", Type.GetType("System.Int32")), _
		New DataColumn("SUB", Type.GetType("System.String")), _
		New DataColumn("EXM", Type.GetType("System.String")) _
	})

dt2.Rows.Add(New Object(){5, "1449-2", "AB101"})
dt2.Rows.Add(New Object(){4, "1449-2", "AC066"})
dt2.Rows.Add(New Object(){2, "1449-2", "AD071"})
dt2.Rows.Add(New Object(){21, "1449-2", "CD028"})
#End Region

'create result datatable
Dim resultdt As DataTable = New DataTable()
'add columns
resultdt.Columns.AddRange(New DataColumn() _
	{ _
		New DataColumn("IC", Type.GetType("System.Int32")), _
		New DataColumn("SC", Type.GetType("System.Int32")), _
		New DataColumn("SUB", Type.GetType("System.String")), _
		New DataColumn("EXM", Type.GetType("System.String")) _
	})

'get data from dt2 and insert into resultdt
For Each dr2 As DataRow In dt2.Rows
	Dim rr As DataRow = resultdt.NewRow
	For Each dc As DataColumn In dt2.Columns
		rr(dc.ColumnName) = dr2(dc.ColumnName)
	Next dc
	Dim result = dt1.AsEnumerable() _
		.Where(Function(x) x("SUB") = dr2("SUB") And x("EXM") = dr2("EXM")) _
		.Select(Function(x) x("SC")) _
		.SingleOrDefault()
	rr("SC") = If(result = Nothing, 0, result) 
	resultdt.Rows.Add(rr)
Next dr2

'find missing rows from dt1
Dim missingRows = dt1.AsEnumerable() _
	.Where(Function(x) resultdt.AsEnumerable().Any(Function(y) x("SUB") <> y("SUB") And x("EXM") <> y("EXM"))) _
	.ToList()
'add rows into resultdt
For Each rr As DataRow In missingRows
	resultdt.Rows.Add(New Object(){0, rr("SC"), rr("SUB"), rr("EXM")})
Next rr
   
Comments
Aravindba 1-Mar-21 22:30pm
   
Thanks, its working
Maciej Los 1-Mar-21 23:38pm
   
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900