InfoPath Dev

I've lurked upon this site for some time and love the wealth of knowledge it brings, but I have finally ran into a question that I can seem to get answered.

I'm trying to create a form that allows users to update data in an access table. That part I have down pat (its really easy). But, if the item that they are trying to update (monthly reporting information, with month to date data) does not exist in that table (its a new report month or has never been reported on) I would still like to auto populate the repeating tables.

I've found help on this site to get the information from the table that holds this data and can shove it into a repeating table that ISN'T the main data source. However, when I try and populate a repeating table with fields from the Main Data source, VB starts to yell at me. Schema non-data type errors abound. I've tried the nill fix and others found around the internet but to no avail.

So, I hit upon the idea of using the same submit connection that my main data source has to submit the data that is populated in the repeating table to the database just using an insert statement. For the life of me I can't get that to work either. And i may be crazy, but I swear the command that the submit connection uses is a select statement.

Has anyone been able to solve this problem before? Am following a pipe dream? Did I forget to declare one simple thing someplace?

Hi--

I think a bit more detail on what you are trying would be helpful.

First, are you trying to populate your main data source with data from a secondary data source? It sounds as if you are -- XPathNodeIterator is what you'll need, and I think there should be lots of code samples around (possibly not for VB though -- if you aren't commited to VB, changing to C# might get you more code samples).

As to changing the command of a data connection, I've not tried to change to an Insert statement -- how is it "not working"? Errors? Just no data in the old DB?

So you know, we have a product that can copy tables from one data source to another in your form -- then you don't have to write code at all. It is called qRules, and you can learn about it here. But you'll find there are coders on this forum who will be happy to lend you a hand if you post what you are doing and what your errors are.

Yes, Main (Access database table) from secondary (yet another Access database table). I've tried a few different methods, but sadly none seemed to work. I get the code snipets to work easily on a things that are not from my Main data source, custom feilds and the like. I'm trying to fill a repeating table of the main data source with data. Do I need to iterate through the repeating table rows as well?

Replacing the SQL statement produces nothing in the database, but I know that the code executes the SQL correctly as it does SELECTs just fine.

 I've read about qRules and it seems like a wonderful progam that I would be very happy to have. Sadly, I work where I work.

Again, I really think others will be able to help you if you post your code. If you are trying to fill a repeating table in the main data source, you'll need to create a node, fill it, and append it.

I've found a whole new error after some minor changes (which I think brought me closer to my answer), but I'm still stuck. I've removed some minor pieces that just grab variables as I know those parts work when not trying to enter the data into "dataFields".

Dim secDSNav As XPathNavigator = DataSources("Sub Agreements").CreateNavigator()

Dim FilterSLA As Integer = slaNav.SelectSingleNode("//my:SLA_ID_User", NamespaceManager).Value

' Retrieve the rows of the secondary data source

Dim M_Report_ID As Integer = slaNav.SelectSingleNode("//my:maxID", NamespaceManager).Value

' Loop through the rows of the secondary data source and fill the repeating table

Dim M_ID As Integer = rows.Current.SelectSingleNode( "@Sub_Agreement_ID", NamespaceManager).Value

Dim M_Desc As String = rows.Current.SelectSingleNode( "@Measurement_Desc", NamespaceManager).Value

Dim M_Type As String = rows.Current.SelectSingleNode( "@Measurement_Type", NamespaceManager).Value

Dim M_Cond As String = rows.Current.SelectSingleNode( "@Measurement_Condition", NamespaceManager).Value

Dim M_Goal As String = rows.Current.SelectSingleNode( "@Measurement_Goal", NamespaceManager).Value

' Add the item to the repeating table

Dim doc As XmlDocument = New XmlDocument()Dim group As XmlNode = doc.CreateElement("Measurement_Reporting_Table", NamespaceManager.LookupNamespace("d"))

 

Dim field As XmlNode = doc.CreateElement("Measurement_ID", NamespaceManager.LookupNamespace("d"))

Dim node As XmlNode = group.AppendChild(field)

node.InnerText = M_ID

 

field = doc.CreateElement("Measurement_Report_ID", NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = M_Report_ID

 

field = doc.CreateElement("SLA_ID", NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = SLA_ID

 

field = doc.CreateElement("Measurement_Goal", NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = M_Goal

 

field = doc.CreateElement("Measurement_Desc", NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = M_Desc

 

field = doc.CreateElement("Measurement_Type",NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = M_Type

doc.AppendChild(group)

MainDataSource.CreateNavigator().SelectSingleNode( "/dfs:myFields/dataFields",NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator())

End While

Everything works great until 'MainDataSource.CreateNavigator()...' when I receive a 'Object reference not set to an instance of an object' error.

I've found a whole new error after some minor changes (which I think brought me closer to my answer), but I'm still stuck. I've removed some minor pieces that just grab variables as I know those parts work when not trying to enter the data into "dataFields".

Dim secDSNav As XPathNavigator = DataSources("Sub Agreements").CreateNavigator()

' Retrieve the rows of the secondary data source

' Loop through the rows of the secondary data source and fill the repeating table

Dim M_ID As Integer = rows.Current.SelectSingleNode( "@Sub_Agreement_ID", NamespaceManager).Value

Dim M_Desc As String = rows.Current.SelectSingleNode( "@Measurement_Desc", NamespaceManager).Value

Dim M_Type As String = rows.Current.SelectSingleNode( "@Measurement_Type", NamespaceManager).Value

Dim M_Cond As String = rows.Current.SelectSingleNode( "@Measurement_Condition", NamespaceManager).Value

Dim M_Goal As String = rows.Current.SelectSingleNode( "@Measurement_Goal", NamespaceManager).Value

' Add the item to the repeating table

Dim doc As XmlDocument = New XmlDocument()Dim group As XmlNode = doc.CreateElement("Measurement_Reporting_Table", NamespaceManager.LookupNamespace("d"))

 

Dim field As XmlNode = doc.CreateElement("Measurement_ID", NamespaceManager.LookupNamespace("d"))

Dim node As XmlNode = group.AppendChild(field)

node.InnerText = M_ID

 

field = doc.CreateElement("Measurement_Report_ID", NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = M_Report_ID

 

field = doc.CreateElement("SLA_ID", NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = SLA_ID

 

field = doc.CreateElement("Measurement_Goal", NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = M_Goal

 

field = doc.CreateElement("Measurement_Desc", NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = M_Desc

 

field = doc.CreateElement("Measurement_Type",NamespaceManager.LookupNamespace("d"))

node = group.AppendChild(field)

node.InnerText = M_Type

doc.AppendChild(group)

MainDataSource.CreateNavigator().SelectSingleNode( "/dfs:myFields/dataFields",NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator())

End While

Everything works great until 'MainDataSource.CreateNavigator()...' when I receive a 'Object reference not set to an instance of an object' error.

Looks to me like your XPath is incorrect in that second to the last line. Shouldn't it be "/dfs:myFields/dfs:dataFields"?

I've tried that as well, that returns 'Schema validation found non-data type errors'. Not including 'dfs:' was found in another forum, maybe that didnt work as well.

With the 'schema validation...' I've found fragments indicating that order is important (which makes perfect sense). I went back and made sure things were in the order they are in the database. It now returns the 'Object reference not set to an instance of an object' error again. Progress?

 The operation I'm attempting to perform is possible right? I can add data to a repeating table, that is based on the 'dataFields' from a database with an autonumbering Primary Key. It has to be right? I can do it by just selecting the values and whatnot.

What code line do you get the Schema validation error on? Same line? You need to include the "dfs" namespace because it is part of the XPath to the node you are trying to select.

Getting different errors is not necessarily a sign of progress.

Are any of the fields from your database not null -- that is, they have to have to have a value?

Here are some links that may help you:

http://blogs.msdn.com/b/infopath/archive/2006/11/28/the-xsi-nil-attribute.aspx
http://msdn.microsoft.com/en-us/library/bb509311(v=office.12).aspx
http://support.microsoft.com/kb/982247

Yes, the primary key of the table is an autonumber. But this problem doesn't show up when I do the same action (in principle) manually. Thanks for the links I'll investigate a bit more.

Frustration seems inherit in my attempt to make this problem work.

I tested all of the fields that I am trying to modify for the nil issue, but to no avail. 

I created a quick test -- a DB bound form, table has an ID column, and I cloned from sampledata.xml and appended w/out a problem -- as you can see, this is not in the "my" namespace:

XmlDocument sampleData = new XmlDocument();

sampleData.Load(this.Template.OpenFileFromPackage("sampledata.xml"));

XPathNavigator newProject = sampleData.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/d:Projects", NamespaceManager).Clone();

MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields", NamespaceManager).AppendChild(newProject);

This snippet (along with a couple others) got that piece of my project working! Thank You!

Lucky for me though requirements changed a bit and I had to rewrite the whole thing to submit to a web service. Other threads solve all of those questions, but I come back to a question that occurs when inserting values into the repeating table.

I have  double type field that has data inserted into it, unless there is no data to enter, then I want it to be empty (no value). It always comes up with a red asterick (*). This is a problem as there is potential for alot of these fields (I'm filling in rows from another table that holds SLA agreements, potential of 50 rows)

Anyone know how to enter no value into a number or date field and not have it get all angry at me?