Copying Data from a Secondary Datasource to a Child Table in a Main Datasource

This code sample documents a block of VB.Net code I had to write recently for retrieving a block of data from a secondary datasource, and then copying that data into a child table of a dataset in the main datasource.    I've copied it over as is for my benefit as much as yours - I lost this code recently in a machine rebuild so want to have a copy somewhere!   I believe the syntax shown here will be helpful to many of you. 


Public Sub GetEscalations_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

' Write your code here.

' Define constants

Const secondaryDataSourceName As String = "GetUserEscalations"

Const secondaryListXPath As String = "/dfs:myFields/dfs:dataFields/ns3:GetUserEscalationsResponse/ns3:GetUserEscalationsResult/NewDataSet/Table"

Const secondaryRelativeEscalationIDXPath As String = "EscalationID"

Const secondaryRelativeDataXPath As String = "CompanyName"

Const mainObservationEvidenceName As String = "KeyObservationEvidence"

'Define strings for mapping values to main datasource evidence table.

Const mainEvidenceKOID As String = "KOID"

Const mainEvidenceKOEvidenceID As String = "KOEvidenceID"

Const mainEvidenceKEID As String = "KEID"

Const mainEvidenceKEDetail As String = "KEDetail"

Const mainEvidenceKECreatedBy As String = "KECreatedBy"

Const mainEvidenceKECreatedDate As String = "KECreatedDate"

Const mainEvidenceKELastUpdatedBy As String = "KELastUpdatedBy"

Const mainEvidenceKELastUpdatedDate As String = "KELastUpdatedDate"

Const mainEvidenceKETypeID As String = "KETypeID"

Const mainEvidenceKETypeName As String = "KETypeName"

Const mainEvidenceKEDeleted As String = "KEDeleted"


'Get navigator to the root of the main datasource. There will only ever be one record in the

'main datasource.

Dim mainDOMNavigator As XPathNavigator = MainDataSource.CreateNavigator()

'Explicitly create a pointer to the root element of the KO. This is the pointer that will drive the appending of the child Key Evidences.

Dim mainDOMObservationNavigator As XPathNavigator

mainDOMObservationNavigator = mainDOMNavigator.SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetKOsDataSetByQuickSearchResponse/tns:GetKOsDataSetByQuickSearchResult/NewDataSet/KeyObservation", NamespaceManager)

'Get navigator to the root of the secondary datasource.

Dim secondaryDataSource As DataSource = DataSources.Item(secondaryDataSourceName)

Dim secondaryDOMNavigator As XPathNavigator = secondaryDataSource.CreateNavigator()


'Setting up some other XPathNavigator Pointers

Dim secondaryEscalationIDNavigator As XPathNavigator

Dim secondaryEscalationEvidenceNavigator As XPathNavigator

Dim secondaryListItem As XPathNavigator

Dim escalationIDMapping As String

Dim escalationEvidence As String

'Get all the list items from the secondary datasource.

Dim secondaryListItems As XPathNodeIterator = secondaryDOMNavigator.Select(secondaryListXPath, NamespaceManager)

Do While secondaryListItems.MoveNext()

secondaryListItem = secondaryListItems.Current

'Get observation Id for the list item.

secondaryEscalationIDNavigator = secondaryListItem.SelectSingleNode(secondaryRelativeEscalationIDXPath, NamespaceManager)

escalationIDMapping = secondaryEscalationIDNavigator.Value

'Get the evidence to be copied into the main DOM.

secondaryEscalationEvidenceNavigator = secondaryListItem.SelectSingleNode(secondaryRelativeDataXPath, NamespaceManager)

escalationEvidence = secondaryEscalationEvidenceNavigator.Value


If Nothing IsNot mainDOMObservationNavigator Then

'Append the evidence child

Using evidenceChild As XmlWriter = mainDOMObservationNavigator.AppendChild()

Dim prefix As String = mainDOMObservationNavigator.Prefix

Dim namespaceUri As String = mainDOMObservationNavigator.NamespaceURI

evidenceChild.WriteStartElement(prefix, mainObservationEvidenceName, namespaceUri)

evidenceChild.WriteStartElement(prefix, mainEvidenceKOID, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKOEvidenceID, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKEID, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKEDetail, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKECreatedBy, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKECreatedDate, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKELastUpdatedBy, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKELastUpdatedDate, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKETypeID, namespaceUri)



evidenceChild.WriteStartElement(prefix, mainEvidenceKETypeName, namespaceUri)

evidenceChild.WriteValue("Escalation SR#")


evidenceChild.WriteStartElement(prefix, mainEvidenceKEDeleted, namespaceUri)




End Using

End If


End Sub

Private Function currentDateTime() As String

Dim mainDomNavigator As XPathNavigator = MainDataSource.CreateNavigator()

Dim todayNavigator As XPathNavigator = mainDomNavigator.SelectSingleNode("/dfs:myFields/my:TodaysDate", NamespaceManager)

Return todayNavigator.Value

End Function

Private Function currentUser() As String

Dim mainDomNavigator As XPathNavigator = MainDataSource.CreateNavigator()

Dim userNavigator As XPathNavigator = mainDomNavigator.SelectSingleNode("/dfs:myFields/my:CurrentUser", NamespaceManager)

Return userNavigator.Value

End Function

Comments (2)
  1. BobChauvin says:

    How do I add a row to the top of a repeating section of my document?  I did it in 2003 via code like this:

                   thisXDocument.View.SelectNodes(nodeRowStart, Type.Missing, Type.Missing) ‘ select the firs row in the list.

                   ‘thisXDocument.UI.Alert("Message to user..)

                   thisXDocument.View.ExecuteAction("xCollection::insertBefore", "Activity_10") ‘ insert before the first row in the list.

    but cannot get my VSTA code to work:


                   Dim mainDomNavigator As XPathNavigator = MainDataSource.CreateNavigator()

                   Dim ActivityNavigator As XPathNavigator = mainDomNavigator.SelectSingleNode("/d:WorkOrders/d:Activity[1]", NamespaceManager)

                   Using NewAct As XmlWriter = ActivityNavigator.InsertBefore()

                       Dim prefix As String = ActivityNavigator.Prefix

                       Dim namespaceUri As String = ActivityNavigator.NamespaceURI

                       NewAct.WriteStartElement(prefix, "Activity_10", namespaceUri)


                   End Using

               Catch ex As Exception

               End Try

  2. ericstott says:

    Is there a way to easily copy data from on repeating group to a new group?

    The underlying xml document looks like this


      <Week time="01/01/08">

         <Project name="Project 1" hours="1">

           <Description>Blah Blah</Description>



              <DetailDescription>Blah Blah Blah Blah</DetailDescription>

    repeating at the Week, Project, and Detail level

    What I want do do is when in infopath I create another week, I want to prefill the new week with all of last weeks projects, copying over the name attribute, Description and Status, but leaving everything else blank.

    Say I have 5 projects from last week, when I create a new week, I need 5 new Project records created in this week’s record.

    Is this at all possible? Is this documented somewhere?

    I am sure I am not the first person to come up with this request, but I am obviously the first person not smart enough to find the answer!

Comments are closed.

Skip to main content