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.WriteValue("-1")


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKOEvidenceID, namespaceUri)


evidenceChild.WriteValue("-1")


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKEID, namespaceUri)


evidenceChild.WriteValue(escalationIDMapping)


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKEDetail, namespaceUri)


evidenceChild.WriteValue(escalationEvidence)


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKECreatedBy, namespaceUri)


evidenceChild.WriteValue(currentUser())


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKECreatedDate, namespaceUri)


evidenceChild.WriteValue(currentDateTime())


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKELastUpdatedBy, namespaceUri)


evidenceChild.WriteValue(currentUser())


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKELastUpdatedDate, namespaceUri)


evidenceChild.WriteValue(currentDateTime())


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKETypeID, namespaceUri)


evidenceChild.WriteValue("1")


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKETypeName, namespaceUri)


evidenceChild.WriteValue("Escalation SR#")


evidenceChild.WriteEndElement()


evidenceChild.WriteStartElement(prefix, mainEvidenceKEDeleted, namespaceUri)


evidenceChild.WriteValue(False)


evidenceChild.WriteEndElement()


evidenceChild.WriteEndElement()


End Using


End If


Loop


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:

               Try

                   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)

                       NewAct.WriteEndElement()

                   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

    <Root>

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

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

           <Description>Blah Blah</Description>

           <Status>Blah</Status>

           <Details>

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

Skip to main content