InfoPath Dev

Hi everyone,

I want to populate fields in repeating table with information from Sql database, that works fine with this script:

SqlConnection myConnection = new SqlConnection("user id=sa;" +

                                                                   "password=MyPassword;server=MyServer;" +

                                                                   "Trusted_Connection=yes;" +

                                                                   "database=MyDatabase; " +

                                                                   "connection timeout=30");

            myConnection.Open();

            SqlDataReader myReader = null;

            SqlCommand myCommand = new SqlCommand("select * from MyTable where [NOM] like '" + field8 + "'",

                                                   myConnection);

            myReader = myCommand.ExecuteReader();

            while (myReader.Read())

            {

                Mail.SetValue(myReader["EMAIL_ADRESS"].ToString());

                Job.SetValue(myReader["POSITION"].ToString());

            }

And the result is that i have information (Email, Position) for the first row in the repeating table.

But when i add a new attendee and I click the button I don’t get information’s for the second attendee.

Help me please.

Best reagards

Look into the XPathNodeIterator. You need to loop through the repeating node to get the values for each node and build your SQL command accordingly.

 Here is an example from an Access Database. I am not sure if I wrote this or I got it somewhere:

 Imports Microsoft.Office.InfoPath
Imports System
Imports System.Windows.Forms
Imports System.Xml
Imports System.Xml.XPath
Imports mshtml
Imports System.Data.OleDb    '<== had to add reference for System.Data to project first
Imports System.Text            '<== added as well


Namespace FORM_0000_DEV_0006

    Public Class FormCode

        Private Sub InternalStartup(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Startup
            AddHandler EventManager.FormEvents.Loading, AddressOf FormEvents_Loading
        End Sub

        Public Sub FormEvents_Loading(ByVal sender As Object, ByVal e As LoadingEventArgs)
            Dim conn As New OleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=P:\prj_0024_Electronic_Forms_OverView\FORM_0000_DEV_xxxx\FORM_0000_DEV_0006_RetrieveImagesToMemoField_AccessDB\OfficeApplications.accdb;")

            Dim sb As New System.Text.StringBuilder()
            sb.Append("SELECT OfficeApplications.ID, ")
            sb.Append("OfficeApplications.[App Name], ")
            sb.Append("OfficeApplications.[App Color], ")
            sb.Append("OfficeApplications.[App Icon] ")
            sb.Append("FROM OfficeApplications ")

            Dim query As String = sb.ToString()
            Dim cmd As New OleDbCommand(query, conn)

            Try

                conn.Open()
                Dim reader As OleDbDataReader = cmd.ExecuteReader()

                Dim row As Integer = 0

                While reader.Read()

                    Dim appID As String = String.Empty
                    Dim appName As String = String.Empty
                    Dim appColor As String = String.Empty
                    Dim appIcon As String = String.Empty

                    If Not reader.IsDBNull(0) Then              '<== Test each field for NULL before proceeding
                        appID = reader.GetInt32(0).ToString()
                    End If

                    If Not reader.IsDBNull(1) Then              '<== Test each field for NULL before proceeding
                        appName = reader.GetString(1)
                    End If

                    If Not reader.IsDBNull(2) Then              '<== Test each field for NULL before proceeding
                        appColor = reader.GetString(2)
                    End If

                    If Not reader.IsDBNull(3) Then              '<== Test each field for NULL before proceeding

                        Dim buffer As Char() = Nothing
                        Dim chars As Long = reader.GetChars(3, 0, buffer, 0, 0)
                        If chars > 0 Then
                            Dim charArray As Char() = New Char(chars - 1) {}
                            reader.GetChars(3, 0, charArray, 0, CInt(chars))
                            Dim pic As New String(charArray)
                            appIcon = pic
                        End If

                    End If

                    AddSection(appID, appName, appColor, appIcon)

                    row = row + 1

                End While

                reader.Close()

            Catch ex As Exception

                MessageBox.Show(ex.Message, "An error occurred", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Finally

                conn.Close()

            End Try
        End Sub

        Private Sub AddSection(ByVal id As String, ByVal appName As String, ByVal appColor As String, ByVal appIcon As String)

            Dim mainDS As XPathNavigator = MainDataSource.CreateNavigator()

            Dim group1 As XPathNavigator = mainDS.SelectSingleNode("/my:myFields/my:group1", NamespaceManager)

            Dim sb As New StringBuilder()
            sb.Append("<my:group2>")
            sb.Append("<my:appName>")
            sb.Append(appName)
            sb.Append("</my:appName>")
            sb.Append("<my:appColor>")
            sb.Append(appColor)
            sb.Append("</my:appColor>")

            If Not String.IsNullOrEmpty(appIcon) Then
                sb.Append("<my:appIcon>")
                sb.Append(appIcon)
            Else
                sb.Append("<my:appIcon ")
                sb.Append("xsi:nil=""true"" ")                                          '<== for Null value
                sb.Append("xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">")
            End If

            sb.Append("</my:appIcon>")
            sb.Append("<my:ID>")
            sb.Append(id)
            sb.Append("</my:ID>")
            sb.Append("</my:group2>")

            group1.AppendChild(sb.ToString())

        End Sub



    End Class
End Namespace