Solved

Create new Datatable from two linked datatables

Posted on 2013-05-17
7
272 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

860 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