InfoPath Dev

 Hello

I'm using infopath 2007 and VB.NET

I'm trying to add  add rows programaticly to a repeating table(the numbers of rows should be inputed in a separated text box)  

for example:

if i put 3 in the textbox and press the button i'm supposed to obtened a reapiting table with 3 rows.

I was searching all around the forums but the only solutioin i've found was a jscript code. 

  thanks

Hello:

Here is a sample that will add rows to a repeating table, with the following caveats -- the table will already need to exist on your form with a single blank row. Also, there is no error handling in this code, and nothing to handle for a user entering something that isn't an int into the text box. However, I think it might be enough to get you started...

'get the value of the text box that holds the number of rows you want
Dim xpath As String = "/my:myFields/my:field1"
Dim field As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath, NamespaceManager)

'get the node you wish to have repeated
xpath = "/my:myFields/my:group1"
Dim group As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath, NamespaceManager)

'cast the field's value to an int to know how many rows to add
Dim rows As Integer = field.ValueAsInt

'loop, cloning group
Dim i As Integer
For i = 1 To rows - 1
    Dim newRow As XPathNavigator = group.Clone()
    newRow.InsertAfter(group)
Next i

 Hello Thank you for your prompt answer it helped me a lot.

 finaly this is what i did instead cloning each row

            For i = 0 To rows - 1
                Dim myNamespace As String = NamespaceManager.LookupNamespace("my")
                Using writer As XmlWriter = MainDataSource.CreateNavigator().SelectSingleNode( _
                "/dfs:myFields/my:group1", NamespaceManager).AppendChild()
                    writer.WriteStartElement("group2", myNamespace)
                    writer.WriteElementString("field1", myNamespace, "")
                    writer.WriteElementString("field2", myNamespace, "")
                    writer.WriteElementString("field3", myNamespace, "")
                    writer.WriteEndElement()
                    writer.Close()
                End Using
            Next i

Well i have another question:

 The same form is conected to an Access database.

I'm trying to query each row and display a message if the row exist.

This how i'm planning to loop through the itemps in the table

Dim domNav As XPathNavigator = MainDataSource.CreateNavigator()
Dim rows As XPathNodeIterator = domNav.Select( _
"/my:myFields/my:table/my:row", NamespaceManager)

While rows.MoveNext()
   Dim field1 As String = rows.Current.SelectSingleNode( _
   "my:field1", NamespaceManager).Value
   Dim field2 As String = rows.Current.SelectSingleNode( _
   "my:field2", NamespaceManager).Value
   Dim field3 As String = rows.Current.SelectSingleNode( _
   "my:field3", NamespaceManager).Value
End While

my problem is that i dont know to query the database programaticly?

this is a screenshoot of my forms and my tables 

  Thanks in advance

Hello:

It looks as if your data source you wish to query is your main data source, so in VB.NET:

MainDataSource.QueryConnection.Execute()

All you need to do is set the values of the query fields you want to use first. So, for example, let's say you want to query your database and find a row where MATNR = field1, VKORG = field 2 and VTWEG = field 3. Inside your while loop, you could do the following (after the code you already have where you set the values of your variables field1, etc):

'set the query fields
Dim xpath As String = "/dfs:myFields/dfs:queryFields/q:MVKE/@MATNR"
Dim matnr As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath, NamespaceManager)
matnr.SetValue(field1.Value)

xpath = "/dfs:myFields/dfs:queryFields/q:MVKE/@VKORG"
Dim vkorg As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath, NamespaceManager)
lastName.SetValue(field2.Value)

xpath = "/dfs:myFields/dfs:queryFields/q:MVKE/@VTWEG"
Dim vtweg As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath, NamespaceManager)
firstName.SetValue(field3.Value)

'get the data connection and execute
MainDataSource.QueryConnection.Execute()

Hope that helps!

Hi 

Thank you for your patience, this helps me a lot and I’m beginning to understand how is going 

There is something that i don’t understand 

Why do you use the expression:

firstName.SetValue(field3.Value)

Regarding to field3.value gives me an error saying .value it is not a member of string

and 

MainDataSource.QueryConnection.Execute()

I have one more question and it is the last one :) this is my problem the picture says more then the words.

well as  you can see instead having 3 lines with answers of the queries i have only one the last one.

What i want to make is for each line od data on iutput obtain an answer on output(if there is a record on the database ) 

**i tried to use the same code (adapted) for  adding rows programaticly for the second repating table witch is bind to an access database

and this is the code 

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

            Dim xpath1 As String = "/dfs:myFields/my:field4"

            Dim field As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath1, NamespaceManager)

            Dim i As Integer

            Dim rows As Integer

            '%%%%First Table

            rows = field.ValueAsInt

            i = 0

            For i = 0 To rows - 1

                Dim myNamespace As String = NamespaceManager.LookupNamespace("my")

                Using writer As XmlWriter = MainDataSource.CreateNavigator().SelectSingleNode( _

                "/dfs:myFields/my:group1", NamespaceManager).AppendChild()

                    writer.WriteStartElement("group2", myNamespace)

                    writer.WriteElementString("field1", myNamespace, "")

                    writer.WriteElementString("field2", myNamespace, "")

                    writer.WriteElementString("field3", myNamespace, "")

                    writer.WriteEndElement()

                    writer.Close()

                End Using

‘%%%Second Table

                Dim myNamespace1 As String = NamespaceManager.LookupNamespace("d:")

                Using writer As XmlWriter = MainDataSource.CreateNavigator().SelectSingleNode( _

   "/dfs:myFields/dfs:dataFields", NamespaceManager).AppendChild()

                    writer.WriteStartElement("MVKE", myNamespace1)

                    writer.WriteElementString("MATNR", myNamespace1, "")

                    writer.WriteElementString("VKORG", myNamespace1, "")

                    writer.WriteElementString("VTWEG", myNamespace1, "")

                    writer.WriteEndElement()

                    writer.Close()

                End Using

            Next i

            '%%%%%%%%%%%%%End Table %%%%%%%%%%%%%%%%%%%%%

        End Sub

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

            '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%Loop every line %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

            Dim domNav As XPathNavigator = MainDataSource.CreateNavigator()

            Dim rows1 As XPathNodeIterator = domNav.Select( _

            "/dfs:myFields/my:group1/my:group2", NamespaceManager)

            While rows1.MoveNext()

                Dim field1 As String = rows1.Current.SelectSingleNode( _

                "my:field1", NamespaceManager).Value

                'set the query fields

                Dim xpath2 As String = "/dfs:myFields/dfs:queryFields/q:MVKE/@MATNR"

                Dim matnr As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath2, NamespaceManager)

                matnr.SetValue(field1)

                Dim field2 As String = rows1.Current.SelectSingleNode( _

                "my:field2", NamespaceManager).Value

                xpath2 = "/dfs:myFields/dfs:queryFields/q:MVKE/@VKORG"

                Dim vkorg As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath2, NamespaceManager)

                vkorg.SetValue(field2)

                Dim field3 As String = rows1.Current.SelectSingleNode( _

                "my:field3", NamespaceManager).Value

                xpath2 = "/dfs:myFields/dfs:queryFields/q:MVKE/@VTWEG"

                Dim vtweg As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath2, NamespaceManager)

                vtweg.SetValue(field3)

                'get the data connection and execute

                MainDataSource.QueryConnection.Execute()

            End While

            ''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%End loop every line %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

        End Sub

 Thanks in advance !

Hello:

To your first question, that was an oversight on my part -- I didn't realize that your variable field3 was a string, holding the value of the node you had selected. So those lines should be:

matnr.SetValue(field1)
vkorg.SetValue(field2)

and so forth.

To your second question -- we can create an XPathNodeIterator for d:MVKE, and check the count. If it is 0, we returned 0 results. So, the code following the execute line could look like this:

'get the data connection and execute
MainDataSource.QueryConnection.Execute()

'get the repeating node that holds the data returned from the query
xpath = "/dfs:myFields/dfs:dataFields/d:MVKE"
Dim mvke As XPathNodeIterator = MainDataSource.CreateNavigator().Select(xpath, NamespaceManager)

'get the count, so we know how many rows were returned
Dim count As Integer = mvke.Count 'if it is 0 do one thing, else do another
If count = 0 Then
    MessageBox.Show("Data does not exist")
Else
    MessageBox.Show("Data already exists")
End If

If you wanted to, you could even add your submit event to the if code block, so that if the data does not exist, it gets submitted. It seems as if you may have edited your post since I started this reply, 'cause when I preview, I see another question, so I am going to post this and then reply again to talk about the repeating table you are returning.

Okay -- the reason you have only row of data for your query is with each execute you are retrieving only the records related to that particular row's query. So, let's say your first query was the SQL equivalent of "Select * from MVKE where MATNR = '000000.....16' and VKORG = 'EG01' ... " and that query retrieves a single row. That row would then be displayed in the fields connected to your data fields from your data source. Now, you loop through the next row in your group 2 table, and you run another query... with the SQL equivalent of "Select * from MVKE where MATNR = '000000.....17' and VKORG = 'EG02' ... " . The result set returned from that will overwrite the result set from your previous query.

Since query fields are not a repeating group, you may decide to create another group (like your group2) with fields that you can set to the value of the returned data. So, you would execute your query, write the data fields into your new table, and then execute your query again for the next row, write those returned data fields into a new row in your new table and so forth.

I don't know if there is a way to modify the query that is executed for your main data connection. If there is, you may be able to modify to execute a single query for all the rows in your group2 table and thus return all the rows you are seeking to your data fields -- something like "Select * from MVKE where (MATNR = '0000....16' and VKORG = 'EG01') or (MATNR = '0000....17' and VKORG = 'EG02')" and so forth.

Hope that helps, let me know if I am not being clear enough.

 Thank you very muchfor your precisous advices.

Finaly i succeedto do what i was planning to do.

Btw, i was wondering if there is a way to submit programaticly each row to a sharepoint list?

programaticly i dont know...but you can do that in the table properties

 Tab Data

---> Allow user to add/delete items

Another way is to limit the rows number in the table...

I am so happy you have your form working the way you want! As far as submitting your rows to a SharePoint list, you could first set up a data connection to the list, then, in your code, set the data fields in the list to the values you want to submit, and submit. I am not sure of the syntax, but here is a link to the InfoPath Developer Reference on MSDN: http://msdn.microsoft.com/en-us/library/aa941030.aspx and here is a blog post that may help you set up the web service data connection: http://weblog.vb-tech.com/nick/archive/2007/01/17/2100.aspx

I also want to programmatically add rows in my repeating table, and have tried these two methods:

1.

XPathNavigator nav = MainDataSource.CreateNavigator();

Getting Error: System.Xml.XPath.XPathException, Namespace Manager or XsltContext needed.

I am unable to configure this error.

I did add rows like this , i hope it will be useful for you also, what this code do is: We have a repeating table, a dropdown or text box or else(must be string but you can change it), and a button namely add button. When you click add button it creates a new repeating table row and adds the value of filed to that row. To delete any row just use repeating table properties. Good luck :)

//Button click to add rows to our table
public void CTRL31_5_Clicked(object sender, ClickedEventArgs e)
        {
            XPathNavigator root = this.CreateNavigator();
            XmlNamespaceManager ns = this.NamespaceManager;

            string SOMETHING = root.SelectSingleNode("HERE TYPE THE PATH OF THE FIELD YOU WANT TO SEE IN ROW", ns).Value;

            XPathNavigator RepeatingTable = root.SelectSingleNode("PATH OF THE REPEATING TABLE", ns);
            XPathNavigator counter = root.SelectSingleNode("PATH OF THE COUNTER FIELD", ns);
            XPathNodeIterator Values = root.Select("PATH OF THE FOLDER INSIDE REPEATING TABLE FOLDER", ns);

            int Counter = counter.ValueAsInt;
            string MyURI = RepeatingTable.NamespaceURI;
            string PREFIX = RepeatingTable.Prefix;

            if (Counter == 0)
            {
                RepeatingTable.AppendChild(CreateXMLTableRow(SOMETHING, MyURI, PREFIX));
                Counter = 1;
                root.SelectSingleNode("PATH OF THE COUNTER FIELD", ns).SetValue(Convert.ToString(Counter));
            }
            else if(Counter == 1 )
            {
                while (Values.MoveNext())
                {
                    string Rows = Values.Current.SelectSingleNode("my:YOUR ROW FIELD NAME", ns).Value;
                    if (SOMETHING == Rows)
                        return;
                }
                RepeatingTable.AppendChild(CreateXMLTableRow(SOMETHING, MyURI, PREFIX));             
            }
        }

And here is the CreateXMLTABLEROW:
NOTE: A = YOUR ROW FIELD NAME and B = PATH OF THE FOLDER INSIDE REPEATING TABLE FOLDER

 private XPathNavigator CreateXMLTableRow(string value, string spreadsheet_URI, string prefix)
        {
       
            XmlDocument Row = new XmlDocument();
            XPathNavigator Row_Navigator = Row.CreateNavigator();

            Row_Navigator.AppendChildElement(prefix, "root", spreadsheet_URI, String.Empty);
            Row_Navigator.MoveToChild("root", spreadsheet_URI);
            Row_Navigator.AppendChildElement(prefix, "A", spreadsheet_URI, String.Empty);
            Row_Navigator.MoveToChild("A", spreadsheet_URI);
            Row_Navigator.AppendChildElement(prefix, "B", spreadsheet_URI, value);
            Row_Navigator.MoveToChild("B", spreadsheet_URI);
            Row_Navigator.MoveToParent();

            return Row_Navigator;
        }
 

Hello:

For the error in sample 1, try swapping the nodes in the last line: iter.Current.InsertAfter(newRow);

For the error in sample 2, you have no NameSpaceManager in your XPathNavigator Group.

MainDataSource.CreateNavigator().SelectSingleNode("/my:expenseReport/my:group4/my:group5");

Should be:

MainDataSource.CreateNavigator().SelectSingleNode("/my:expenseReport/my:group4/my:group5", NamespaceManager);

thanks for your quick reply.

but tell me one thing what do you mean by

" PATH OF THE FOLDER INSIDE REPEATING TABLE FOLDER "

i m still getting errors and unable to add row programmatically. :(