InfoPath Dev

First off: I'm running IP2003 with VS.net 2003

I have a repeating table bound to a table in my Main Data Connection.
I created a custom select statement that gets all the rows where id = N.
The select statement works, and the fields fill auto-magically.

The problem comes when I try to read these fields with code:
Fields that have an empty string or have data are found fine. Any field that received a DBNULL from the database is not found by my loop. Also if I change the field that was DBNULL to something else, it is still not found by my loop.

I'm guessing that since repeating table fields are created dynamically, when a DBNULL is received the field is not created.
If I had to, I could just make it so when a row is inserted into the DB table all the values are set to blank instead of DBNULL, but I'd rather not do that.

any suggestions? I was hoping there'd be a method i could call that would just update the XML structures.

Hey Mike and welcome to the forum!

I don't quite understand this problem - do you think you could paste the code here so we could look at it? Also - you might want to use SelectNodes instead of a loop with SelectSingleNode() as it sounds like you're doing.

'Code to traverse Table and append to a string:

Dim oNodeList As IXMLDOMNodeList
Dim oNode As IXMLDOMNode
Dim item As IXMLDOMNode
Dim output As String
Dim xpath As String = "dfs:myFields/dfs:dataFields/d:table/d:foreignkeytable"
Dim k As Int16 = 0

oNodeList = thisXDocument.DOM.selectNodes(xpath)

If (oNodeList.length > 0) Then
For Each oNode In oNodeList
output &= k & " = "
For Each item In oNode.attributes
output &= "{"& item.nodeName & " : " & item.text & "}"
Next
output &= vbcrlf
Next
MsgBox(output)
End If

This is pretty straightforward code, and it traverses a repeating table fine. The data fields are stored by infopath in attributes instead of nodes. Here's an example, taken from my template.xml file

<dfs:myFields (scheme goes here but i deleted it to save space.>
<dfs:dataFields>
<d:table ID="" ACCOUNTNO="" RECID="">
<d:foreignkeytable ID="" Field1="" Field2="" Field3="" Field4="" Field5=""></d:foreignkeytable>
</d:table>
</dfs:dataFields>
</dfs:myFields>


My repeating table is the collection of columns from my 'foreignkeytable' where 'ID' matches a certain number.
N is a variable ID number.

so my select statement looks like:
"SELECT Field1, Field2, Field3, Field4, Field5
FROM "dbo"."foreignkeytable"
WHERE ID = 'N'


====================================================
Almost everything works. The select statement works and causes the repeating table to be filled with values from the database. The repeating table alters its size accordingly.

The loop to traverse works fine. I believe the output for 2 rows would look like:
"1 {Field1 : [value]}{Field2 : [value]}{Field3 : [value]}{Field4 : [value]}
"2 {Field1 : [2ndrowvalue]}{Field2 : [2ndrowvalue]}{Field3 : [2ndrowvalue]}{Field4 : [2ndrowvalue]}

The problem is:
If say row1,Field2 was retrieved from the Database as a 'DBNULL'. The output would be:
"1 {Field1 : [value]}}{Field3 : [value]}{Field4 : [value]}
"2 {Field1 : [2ndrowvalue]}{Field2 : [2ndrowvalue]}{Field3 : [2ndrowvalue]}{Field4 : [2ndrowvalue]}

Notice Field2 isn't there. Why?
Well the for each loop obviously isn't seeing the Field2 node from the collection.
I tried replacing the for each loop with a for k = 0 to attributes.length - 1 and Field2 still did not show up.

I also tried changing the value of Field2 in my Infopath form to something else, but Field2 was not displayed.
Then I tried changing the value in my DataBase table to just blank "".
I closed and reopened the form, (which ran my select statement again) and then ran my traversal loop, And it DID output Field2 this time.

And it's not just field2, if any node has DBNULL it won't show up.

My guess is, since repeating tables are created dynamically, when a DBNULL is retrieved from the database there is no node created.
This implies that infopath creates it's nodes individually and then adds them to a row, instead of creating a whole row and then filling the nodes.

Most importantly, I was hoping there was a function that would update the node structure based on what fields were filled in the open Form. Know of any functions like this? If not I can't really think of any other solutions. I just plan to initialize all my Database fields blank strings instead of allowing them to be NULL.


Also if you want to try this out:
-Create a table in a database, i'm using MS-SQL server... Access should probably be the same.
-Connect your infopath form to the database, create a repeating table.
-Run some sort of select statement so that the table gets filled.
-Traverse the table with my loop and look at the output.