InfoPath Dev

This is my first time posting here, I'm not really a code monkey by trade but I dabble, and I've also never really used visual basic until a few weeks ago.

Here is my routine that executes when my form is submitted. Essentially, it iterates through my main data source to build a CAML submission to send to my sharepoint list. After it is submitted to the sharepoint list sharepoint assigns the list items individual ID numbers, I am trying to retrieve the ID associated with each list item, and programmatically assign said ID to the ID field in my form so that I can later program the form to update those items by form ID instead of making new list items.

Currently everything works fine except for the bold section. The code runs, but currently only updates the ID of the last list item I make. I believe this is because the iterator is already at the bottom of the list, but I've had no success at telling the iterator to start at the top of the list again. When I do tell the iterator to report where it is when the bolded loop is running, it reports that it is at the last item that ran in the CAML loop. It is entirely possible I don't understand quite how the navigator and iterator function, but I wouldn't come here unless I had already googled and tried repeatedly without success.

        Public Sub FormEvents_Submit(ByVal sender As Object, ByVal e As SubmitEventArgs)
            ' If the submit operation is successful, set
            ' e.CancelableArgs.Cancel = False
            ' Write your code here.
            ' Delete all Method nodes from the CAML Batch XML
            Dim secDSNav As XPathNavigator = DataSources("CustomListCAML").CreateNavigator()
            Dim iter As XPathNodeIterator = secDSNav.Select("/Batch/Method")
            Dim methodNodesCount As Integer = iter.Count

            Dim firstMethodNav As XPathNavigator = _
              secDSNav.SelectSingleNode("/Batch/Method[1]", _
              NamespaceManager)
            Dim lastMethodNav As XPathNavigator = _
              secDSNav.SelectSingleNode("/Batch/Method[" & _
              methodNodesCount.ToString() & "]", _
              NamespaceManager)

            firstMethodNav.DeleteRange(lastMethodNav)

            ' Retrieve the rows of the repeating table
            Dim root As XPathNavigator = MainDataSource.CreateNavigator()
            Dim rows As XPathNodeIterator = root.Select( _
              "/my:EngineeringDevelopmentProject/my:LessonsLearnedSection/my:LessonsLearnedTable/my:Lessons", NamespaceManager)

            ' Loop through the rows of the repeating table
            ' and construct the CAML Batch XML
            Dim counter As Integer = 1
            While rows.MoveNext()
                ' Retrieve the title
                Dim title As String = rows.Current.SelectSingleNode( _
                  "../../../my:ProjectName", NamespaceManager).Value
                Dim datecode As String = rows.Current.SelectSingleNode( _
                  "my:Date", NamespaceManager).Value
                Dim champion As String = rows.Current.SelectSingleNode( _
                  "/my:EngineeringDevelopmentProject/my:Champion/pc:Person/pc:DisplayName", NamespaceManager).Value
                Dim customer As String = rows.Current.SelectSingleNode( _
                  "../../my:Customer", NamespaceManager).Value
                Dim category As String = rows.Current.SelectSingleNode( _
                  "my:Category", NamespaceManager).Value
                Dim description As String = rows.Current.SelectSingleNode( _
                  "my:Description", NamespaceManager).Value
                Dim link1 As String = rows.Current.SelectSingleNode( _
                  "my:Link1", NamespaceManager).Value
                ' Add an item to the CAML Batch XML
                AddMethodNode(counter, title, datecode, champion, customer, category, description, link1)

                ' Increment the counter
                counter = counter + 1
            End While

            ' Submit the rows to the Lists web service to update the custom list
            DataConnections("Lessons Submit").Execute()
            ' Retrieve newly assigned ID values from sharepoint list
            DataSources("Lessons Learned").QueryConnection.Execute()
            Dim rootlessons As XPathNavigator = DataSources("Lessons Learned").CreateNavigator()
            Dim rowslessons As XPathNodeIterator = rootlessons.Select( _
              "/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW", NamespaceManager)
            While rowslessons.MoveNext()
                If rowslessons.Current.SelectSingleNode("d:Title", NamespaceManager).Value.Equals(rows.Current.SelectSingleNode("../../../my:ProjectName", NamespaceManager).Value) Then
                    rows.Current.SelectSingleNode("my:ID", NamespaceManager).SetValue(rowslessons.Current.SelectSingleNode("d:ID", NamespaceManager).Value)
                End If
            End While
            e.CancelableArgs.Cancel = False
        End Sub

        Public Sub AddMethodNode(ByVal id As Integer, ByVal title As String, ByVal datecode As String, ByVal champion As String, ByVal customer As String, ByVal category As String, ByVal description As String, ByVal link1 As String)
            Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
            sb.AppendFormat("<Method ID=""{0}"" Cmd=""New"">", id.ToString())
            sb.AppendFormat("<Field Name=""Title"">{0}</Field>", title)
            sb.AppendFormat("<Field Name=""Date"">{0}</Field>", datecode)
            sb.AppendFormat("<Field Name=""Champion"">{0}</Field>", champion)
            sb.AppendFormat("<Field Name=""Customer"">{0}</Field>", customer)
            sb.AppendFormat("<Field Name=""Category"">{0}</Field>", category)
            sb.AppendFormat("<Field Name=""Description"">{0}</Field>", description)
            sb.AppendFormat("<Field Name=""Link1"">{0}</Field>", link1)
            sb.AppendFormat("</Method>")

            Dim methodXml As XmlDocument = New XmlDocument()
            methodXml.LoadXml(sb.ToString())

            Dim secDSNav As XPathNavigator = DataSources("CustomListCAML").CreateNavigator()
            Dim batchNav As XPathNavigator = secDSNav.SelectSingleNode("/Batch", NamespaceManager)
            batchNav.AppendChild(methodXml.DocumentElement.CreateNavigator())
        End Sub

I was going to write a conditional statement to tell the routine to update instead of create new list items, but if there is a more elegant way to do this- I am all ears.

I am currently trying to combine both loops to send each item as a individual submission, and update the ID at the same time instead of having to iterate back through the entire sharepoint list.

*No success yet

Of course the rows iterator is already at the bottom of the list.  You're already iterating all the way through it in the previous While loop.  You can restart it at the beginning of the list by calling Select() again, but I think you need to inspect the logic you're using because I have a feeling there are some oversights.  You should be able to use two XPathNavigators at the same time.

Just a side comment, but I always find it puzzling that people do this:

Dim lastMethodNav As XPathNavigator = _
              secDSNav.SelectSingleNode("/Batch/Method[" & _
              methodNodesCount.ToString() & "]", _
              NamespaceManager)

This should work just fine:

Dim lastMethodNav As XPathNavigator = _
              secDSNav.SelectSingleNode("/Batch/Method[last()]", NamespaceManager)

I did fix the item you mentioned. About half my code is based on some examples I've seen, and that is where it was inherited from.

I did have some luck with the nested loops method, but either one is a toss up. I can submit all the items individually or together. Currently, with the code as it is above, I get the last ID field updated from the query.

*Still no success telling rows to go back to the top.

This is my current code using nested loops. It correctly fills in my first ID value, but apparently exiting the internal while loop does not allow me to continue storing values when it comes around again. Any ideas?        

        Public Sub FormEvents_Submit(ByVal sender As Object, ByVal e As SubmitEventArgs)
            ' If the submit operation is successful, set
            ' e.CancelableArgs.Cancel = False
            ' Write your code here.
            Dim secDSNav As XPathNavigator = DataSources("CustomListCAML").CreateNavigator()
            Dim iter As XPathNodeIterator = secDSNav.Select("/Batch/Method")
            Dim methodNodesCount As Integer = iter.Count



            ' Retrieve the rows of the repeating table
            Dim root As XPathNavigator = MainDataSource.CreateNavigator()
            Dim rows As XPathNodeIterator = root.Select( _
              "/my:EngineeringDevelopmentProject/my:LessonsLearnedSection/my:LessonsLearnedTable/my:Lessons", NamespaceManager)
            Dim rootlessons As XPathNavigator = DataSources("Lessons Learned").CreateNavigator()
            Dim rowslessons As XPathNodeIterator = rootlessons.Select( _
              "/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW", NamespaceManager)

            ' Loop through the rows of the repeating table
            ' and construct the CAML Batch XML
            Dim counter As Integer = 1
            While rows.MoveNext()
                Dim firstMethodNav As XPathNavigator = _
                  secDSNav.SelectSingleNode("/Batch/Method[1]", NamespaceManager)
                Dim lastMethodNav As XPathNavigator = _
                  secDSNav.SelectSingleNode("/Batch/Method[last()]", NamespaceManager)

                firstMethodNav.DeleteRange(lastMethodNav)
                ' Retrieve the title
                Dim title As String = rows.Current.SelectSingleNode( _
                  "../../../my:ProjectName", NamespaceManager).Value
                Dim datecode As String = rows.Current.SelectSingleNode( _
                  "my:Date", NamespaceManager).Value
                Dim champion As String = rows.Current.SelectSingleNode( _
                  "/my:EngineeringDevelopmentProject/my:Champion/pc:Person/pc:DisplayName", NamespaceManager).Value
                Dim customer As String = rows.Current.SelectSingleNode( _
                  "../../my:Customer", NamespaceManager).Value
                Dim category As String = rows.Current.SelectSingleNode( _
                  "my:Category", NamespaceManager).Value
                Dim description As String = rows.Current.SelectSingleNode( _
                  "my:Description", NamespaceManager).Value
                Dim link1 As String = rows.Current.SelectSingleNode( _
                  "my:Link1", NamespaceManager).Value
                ' Add an item to the CAML Batch XML
                AddMethodNode(title, datecode, champion, customer, category, description, link1)
                ' Submit the rows to the Lists web service and retrieve newly assigned ID values from sharepoint list
                DataConnections("Lessons Submit").Execute()
                DataSources("Lessons Learned").QueryConnection.Execute()
                While rowslessons.MoveNext
                    If rowslessons.Current.SelectSingleNode("d:Title", NamespaceManager).Value.Equals(rows.Current.SelectSingleNode("../../../my:ProjectName", NamespaceManager).Value) Then
                        rows.Current.SelectSingleNode("my:ID", NamespaceManager).SetValue(rowslessons.Current.SelectSingleNode("d:ID", NamespaceManager).Value)
                        Exit While
                    End If
                End While
                ' Increment the counter
                counter = counter + 1
            End While
            e.CancelableArgs.Cancel = False
        End Sub
        Public Sub AddMethodNode(ByVal title As String, ByVal datecode As String, ByVal champion As String, ByVal customer As String, ByVal category As String, ByVal description As String, ByVal link1 As String)
            Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
            Dim id As Integer = 1
            sb.AppendFormat("<Method ID=""{0}"" Cmd=""New"">", id.ToString())
            sb.AppendFormat("<Field Name=""Title"">{0}</Field>", title)
            sb.AppendFormat("<Field Name=""Date"">{0}</Field>", datecode)
            sb.AppendFormat("<Field Name=""Champion"">{0}</Field>", champion)
            sb.AppendFormat("<Field Name=""Customer"">{0}</Field>", customer)
            sb.AppendFormat("<Field Name=""Category"">{0}</Field>", category)
            sb.AppendFormat("<Field Name=""Description"">{0}</Field>", description)
            sb.AppendFormat("<Field Name=""Link1"">{0}</Field>", link1)
            sb.AppendFormat("</Method>")

            Dim methodXml As XmlDocument = New XmlDocument()
            methodXml.LoadXml(sb.ToString())

            Dim secDSNav As XPathNavigator = DataSources("CustomListCAML").CreateNavigator()
            Dim batchNav As XPathNavigator = secDSNav.SelectSingleNode("/Batch", NamespaceManager)
            batchNav.AppendChild(methodXml.DocumentElement.CreateNavigator())
        End Sub