How to merge two datatable with all values ?

Question

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 28-Feb-21 20:01pm

Aravindba

Updated 28-Feb-21 22:48pm

Maciej Los

v3

Add a Solution

2 solutions

Solution 1

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 ...

Posted 28-Feb-21 20:09pm

OriginalGriff

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2021-02-28T22:48:00

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