Solved

Create new Datatable from two linked datatables

Posted on 2013-05-17
7
275 Views
Last Modified: 2013-06-06
Hi
I have two Datatables, dtRenewals and dtAgents. There is a field in each called AgentCode.
I'd like to create another table, dtRenewals_WithAgents, which would only contain the renewals that have an AgentCode that is in dtAgents.
How would I do this?
Thanks
0
Comment
Question by:jdhackett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39175319
You can loop through dtAgents, find associated rows from dtrenewals and add to a new datatable.
0
 
LVL 16

Expert Comment

by:Rose Babu
ID: 39176780
0
 
LVL 1

Author Comment

by:jdhackett
ID: 39180564
@srosebabu - those links are for merging two different tables. I don't want to merge the tables, I just want the rows from dtRenewals where the Agent is in dtAgents.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 16

Expert Comment

by:Rose Babu
ID: 39184081
can you provide some sample data for the both data-tables with the required merged data sample?
0
 
LVL 1

Author Comment

by:jdhackett
ID: 39199123
Sample for dtRenewals:
PolicyRef = TEST09
AgentCode = ARC1
IssueDate = 31/04/13

PolicyRef = TEST10
AgentCode = ""
IssueDate = 31/04/13

PolicyRef = TEST11
AgentCode = BAR
IssueDate = 31/04/13


Sample for dtAgents:
AgentCode = ARC1
AgentName = Arch Insurances

AgentCode = ZAP
AgentName = Zap Insurances


In this example, I would only like the row for TEST09.
0
 
LVL 16

Accepted Solution

by:
Rose Babu earned 500 total points
ID: 39204384
Have a look and test the below code....

Dim drTest As DataRow

Dim dtRenewals As New DataTable
dtRenewals.Columns.Add("PolicyRef")
dtRenewals.Columns.Add("AgentCode")
dtRenewals.Columns.Add("IssueDate")

drTest = dtRenewals.NewRow
drTest(0) = "TEST09"
drTest(1) = "ARC1"
drTest(2) = "31 / 4 / 13"
dtRenewals.Rows.Add(drTest)

drTest = dtRenewals.NewRow
drTest(0) = "TEST10"
drTest(1) = ""
drTest(2) = "31 / 4 / 13"
dtRenewals.Rows.Add(drTest)

drTest = dtRenewals.NewRow
drTest(0) = "TEST11"
drTest(1) = "BAR"
drTest(2) = "31 / 4 / 13"
dtRenewals.Rows.Add(drTest)

Dim dtAgents As New DataTable
dtAgents.Columns.Add("AgentCode")
dtAgents.Columns.Add("AgentName")

drTest = dtAgents.NewRow
drTest(0) = "ARC1"
drTest(1) = "Arch Insurances"
dtAgents.Rows.Add(drTest)

drTest = dtAgents.NewRow
drTest(0) = "ZAP"
drTest(1) = "Zap Insurances"
dtAgents.Rows.Add(drTest)


Dim dtRenewals_WithAgents As New DataTable
dtRenewals_WithAgents.Columns.Add("PolicyRef")
dtRenewals_WithAgents.Columns.Add("AgentCode")
dtRenewals_WithAgents.Columns.Add("IssueDate")
dtRenewals_WithAgents.Columns.Add("AgentName")

Dim strPolicyRef As String = String.Empty
Dim strAgentCode As String = String.Empty
Dim strIssueDate As String = String.Empty
Dim strAgentName As String = String.Empty



If dtRenewals.Rows.Count > 0 Then
	For i = 0 To dtRenewals.Rows.Count - 1
		strPolicyRef = dtRenewals.Rows(i).Item("PolicyRef").ToString.Trim
		strAgentCode = dtRenewals.Rows(i).Item("AgentCode").ToString.Trim
		strIssueDate = dtRenewals.Rows(i).Item("IssueDate").ToString.Trim

		For j = 0 To dtAgents.Rows.Count - 1
			If strAgentCode = dtAgents.Rows(j).Item("AgentCode").ToString.Trim Then
				strAgentName = dtAgents.Rows(j).Item("AgentName").ToString.Trim

				drTest = dtRenewals_WithAgents.NewRow
				drTest(0) = strPolicyRef
				drTest(1) = strAgentCode
				drTest(2) = strIssueDate
				drTest(3) = strAgentName

				dtRenewals_WithAgents.Rows.Add(drTest)
			End If
		Next
	Next
	
	' check the dtRenewals_WithAgents data table for the required record(s)

End If

Open in new window

This will get you the records as you wanted.... Let me know the outcome
0
 
LVL 1

Author Comment

by:jdhackett
ID: 39224873
Very neat, thanks.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP.NET Calendar Control 5 25
Find date of 2nd Thursday of each month 3 34
Using this function 4 42
VB.net Is there code behind PowerPivot 2 48
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question