Infopath VBA to submit to poptional databases on Submit_Clicked

I have two MS Access tables that comprise the Infopath Main Connection datasource. The lab_log table is the parent table and all data is inserted into that table. The second table tested_parts is related to the lab_log table by lab_num and is an optional section on the infopath form. Data is entered in both sections on the form and the VBA code inserts correctly into both tables when the submit button is clicked. When I only enter into the lab_log section and do not activate the tested_parts optional section I get the error:

"NullReferenceException was unhandled by user code, Object reference not set to an instance of an object. A first chance exception of type 'System.NullReferenceException' occurred."

Sample of my current code that inserts into both tables based on_click of Submit button, but errors out when optional section is not selected:

 Public Sub Submit_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
            Dim mainDS As XPath.XPathNavigator = MainDataSource.CreateNavigator()
            Dim lab_num As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@lab_num", NamespaceManager).Value
            Dim type As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@type", NamespaceManager).Value
            Dim part_num As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@part_num", NamespaceManager).Value
            Dim part_name As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@part_name", NamespaceManager).Value
            Dim prod_code As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@prod_code", NamespaceManager).Value
            Dim entry_date As DateTime = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@entry_date", NamespaceManager).Value
            Dim due_date As DateTime = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@due_date", NamespaceManager).Value
            Dim schedule As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@schedule", NamespaceManager).Value
            Dim supplier As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@supplier", NamespaceManager).Value
            Dim who As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@who", NamespaceManager).Value
            Dim requestor As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@requestor", NamespaceManager).Value
            Dim comment As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@comment", NamespaceManager).Value

            'Tested Parts Section

            Dim part_num_tp As String = ""
            Dim part_name_tp As String = ""
            Dim lab_num_tp As String = ""
            Dim supplier_tp As String = ""
            Dim prod_code_tp As String = ""

            If Not Convert.IsDBNull(mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager)) Then
                part_num_tp = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager).Value
                part_name_tp = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_name_tp", NamespaceManager).Value
                lab_num_tp = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@lab_num_tp", NamespaceManager).Value
                supplier_tp = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@supplier_tp", NamespaceManager).Value
                prod_code_tp = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@prod_code_tp", NamespaceManager).Value
            Else

            End If

            Dim conn As New OleDbConnection( _
              "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=U:\MCL\datasources\newweb_log.accdb;")

            Dim cmd As OleDbCommand = conn.CreateCommand()
            cmd.CommandType = System.Data.CommandType.Text
            Dim sb As New System.Text.StringBuilder()
            sb.Append("INSERT INTO lab_log ")
            sb.Append("([lab_num], [part_num], [part_name], [entry_date], [due_date], [schedule], [supplier], [who], [requestor], [comment]) ")
            sb.Append("VALUES ")
            sb.Append("(@lab_num, @part_num, @part_name, @entry_date, @due_date, @schedule, @supplier, @who, @requestor, @comment)")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.AddWithValue("lab_num", lab_num)
            cmd.Parameters.AddWithValue("part_num", part_num)
            cmd.Parameters.AddWithValue("part_name", part_name)
            cmd.Parameters.AddWithValue("entry_date", entry_date)
            cmd.Parameters.AddWithValue("due_date", due_date)
            cmd.Parameters.AddWithValue("schedule", schedule)
            cmd.Parameters.AddWithValue("supplier", supplier)
            cmd.Parameters.AddWithValue("who", who)
            cmd.Parameters.AddWithValue("requestor", requestor)
            cmd.Parameters.AddWithValue("comment", comment)

            Try
                conn.Open()
                cmd.ExecuteNonQuery()

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

            Finally
                conn.Close()

            End Try

            If part_num_tp.Length > 0 Then

                Dim cmd_tp As OleDbCommand = conn.CreateCommand()
                cmd_tp.CommandType = System.Data.CommandType.Text
                sb = New System.Text.StringBuilder()
                sb.Append("INSERT INTO tested_parts ")
                sb.Append("([lab_num_tp], [part_num_tp], [part_name_tp], [supplier_tp], [prod_code_tp]) ")
                sb.Append("VALUES ")
                sb.Append("(@lab_num_tp, @part_num_tp, @part_name_tp, @supplier_tp, @prod_code_tp)")

                cmd_tp.CommandText = sb.ToString()
                cmd_tp.Parameters.AddWithValue("lab_num_tp", lab_num_tp)
                cmd_tp.Parameters.AddWithValue("part_num_tp", part_num_tp)
                cmd_tp.Parameters.AddWithValue("part_name_tp", part_name_tp)
                cmd_tp.Parameters.AddWithValue("supplier_tp", supplier_tp)
                cmd_tp.Parameters.AddWithValue("prod_code_tp", prod_code_tp)

                Try
                    conn.Open()
                    cmd_tp.ExecuteNonQuery()

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

                Finally
                    conn.Close()

                End Try
            Else
                If part_num_tp.Length = 0 Then
                    part_num_tp = ""

                End If
            End If

        End Sub

Open in new window

jaspenceAsked:
Who is Participating?
 
JimFiveConnect With a Mentor Commented:
Before line 36 add:
            If Not Convert.IsDBNull(mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts", NamespaceManager)) Then

Open in new window


And then add another End If at line 50.

Also, why do you have an Else with no statements at line 48?
0
 
JimFiveCommented:
This is basically a guess but I suspect that you need to check that:"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts is not null before you check that "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp is null in line 36 of the posted code.
0
 
jaspenceAuthor Commented:
What code would I have to insert to test that?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PatHartmanCommented:
You have defined a bunch of variables as strings and in the Dim statements are setting them to a value.  If any of these controls are null, you will get an error because you can't set a string to null.  Either change the definition from String to Variant or add code to verify that each field is not null before trying to copy it to a string.
0
 
jaspenceAuthor Commented:
What code would I have to insert to change that. I tried changing the string to variant and it autocorrected to Object. After I run the debugging using Object and not String I still get the same error on the same line.
0
 
PatHartmanCommented:
It wouldn't let you change
Dim lab_num As String

to

Dim lab_num As Variant?
0
 
jaspenceAuthor Commented:
Nope it auto changes variant to object:

Dim lab_num_tp As Object = ""
0
 
jaspenceAuthor Commented:
Added before line 36:
If Not Convert.IsDBNull(mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts", NamespaceManager)) Then 

Open in new window

and added End If after line 50

Still get the same error on the same line 39.
0
 
PatHartmanCommented:
Try separating the Dim from the rest of the statement.
Dim lab_num As Variant
lab_num = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@lab_num", NamespaceManager).Value

Open in new window

0
 
jaspenceAuthor Commented:
I tried separating the Dim from the rest of the statement as suggested. The "Variant" automatically changed to "Object" again. I was able to submit using this code, but still receiving the error at the optional section at line 39.
To reference a previous response:
"Either change the definition from String to Variant or add code to verify that each field is not null before trying to copy it to a string."
Since I cant change to variant (auto corrects to Object) how can I verify that each field is not null before trying to copy it to a string?
0
 
livanescuCommented:
wouldn't it be simple to disable in InfoPath UI the Submit button if one section is missing and display instead in UI a message like: "please add missing data for 'tested_parts' ..." ?
this way you validate before submission...
just a thought...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.