InfoPath Dev

I have a view in which a user can query data by an account number. The name followed by rows of data (date of transaction, check amount, check number, client), in a repeating table, are displayed underneath the name section. I need to know how to save the data in the repeating table if someone changes a field in the repeating table. I have an update button under the repeating table with the following code. Any help would be greatly appreciated.

Dim Nav As XPathNavigator = Me.CreateNavigator()

Dim rows As XPathNodeIterator = Nav.Select("/dfs:myFields/dfs:dataFields/d:tblUnappliedCash", NamespaceManager)

Conn.Command = "UPDATE tblUnappliedCash SET " & _

"CKAmount ='" & GetValue("/dfs:myFields/dfs:dataFields/d:tblUnappliedCash/@CKAmount", Nav) & "', " & _

"ClientNumber ='" & GetValue("/dfs:myFields/dfs:dataFields/d:tblUnappliedCash/@ClientNumber", Nav) & "' " & _

"WHERE (FACS_AC = '" & GetValue("/dfs:myFields/dfs:queryFields/q:tblUnappliedCash/@FACS_AC", Nav) & "')"

End While

End Sub

Hi -- is there a problem with the current code? Or is it that you need to do this only if a field changes, not if someone clicks the update button?

I need do this this only if a field changes.

Hmmm. But I don't think you'd want to do this on a field changed event, since that may cause more update statements than you really need to run. I guess what I'm trying to understand is if you want to run the update statement any time a field changes in the table, or if when your users click the button you only want to run the update statements if fields have changed.

You could add another boolean field or attribute to your repeating group, called 'IsChanged' and then set rules on your fields in the repeating group to set that field to true when they change. Then, in your button code you could filter to only get the rows that have changed.

Looking at your original code, I do see a problem -- you are using absolute XPath to your fields instead of relative. I don't usually use MoveNext, so I'm not familiar with the syntax, but in C# using a foreach, I'd do something like this:

foreach(XPathNavigator row in rows)
{
  Conn.Command = "UPDATE tblUnappliedCash SET "  +
  "DateOfTransaction ='" + row.SelectSingleNode("@DateOfTransaction", Nav) + "', " +
  "CKAmount ='" + row.SelectSingleNode("@CKAmount", Nav) + "', " +

  "CKNumber ='" + row.SelectSingleNode("@CKNumber", Nav) + "', " +
  "ClientNumber ='" + row.SelectSingleNode("@ClientNumber", Nav) + "' " +

  "WHERE (FACS_AC = '" + row.SelectSingleNode("@FACS_AC", Nav) + "')"

  Conn.Execute()
}

That is just some quick copy/paste pseudo-code, so forgive me any obvious errors, just wanted to give you the idea. Your current code will loop through the rows and keep running update statements for the first values.

Well I got the update part working fine. Here is the code that I used to get that working.

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

Dim Conn As AdoQueryConnection = Me.DataConnections("Save Unapplied Cash")

Dim iCounter As Integer = 1

Conn.Command = "UPDATE tblUnappliedCash SET " & _

"CKAmount ='" & GetValue("/dfs:myFields/dfs:dataFields/d:tblUnappliedCash[" & iCounter & "]/@CKAmount", Nav) & "', " & _

"ClientNumber ='" & GetValue("/dfs:myFields/dfs:dataFields/d:tblUnappliedCash[" & iCounter & "]/@ClientNumber", Nav) & "' " & _

"WHERE (UnappliedCashID = '" & GetValue("/dfs:myFields/dfs:dataFields/d:tblUnappliedCash[" & iCounter & "]/@UnappliedCashID", Nav) & "')"

End While

End Sub

Now I would like to have another column to my repeating table where there is a delete button for every row. Do you know how I might go about coding that button?