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 code that goes into both tables based on_click of Submit button:
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