asked on

Infopath VBA to submit to two 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 I need the VBA code to insert into both tables when the submit button is clicked. I am new to VBA and can not seem to get the following code to insert into the tested_parts table. Data does insert to the lab_log table, but nothing goes into the tested_parts. I also get the "SQL is missing semicolon" error.

Private Sub InternalStartup(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Startup
            AddHandler DirectCast(EventManager.ControlEvents("Submit"), ButtonEvent).Clicked, AddressOf Submit_Clicked
        End Sub

        Public Sub Submit_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
            Dim mainDS As 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
            Dim part_num_tp As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager).Value
            Dim part_name_tp As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_name_tp", NamespaceManager).Value
            Dim lab_num_tp As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@lab_num_tp", NamespaceManager).Value
            Dim supplier_tp As String = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@supplier_tp", NamespaceManager).Value

            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

            sb.Append("INSERT INTO tested_parts ")
            sb.Append("([lab_num_tp], [part_num_tp], [part_name_tp], [supplier_tp]) ")
            sb.Append("VALUES ")
            sb.Append("(@lab_num_tp, @part_num_tp, @part_name_tp, @supplier_tp)")

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

            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


        End Sub

Open in new window

Scott McDaniel (EE MVE )

Can you set a breakpoint and show exactly what the cmd.CommandText value is just before you run the ExecuteNonQuery line? To do that, set the breakpoint and then when the code breaks, type this in the Immediate window:

?cmd.CommandText

Post that here ...

Also - what's the reason for closing and then immediately reopening the conn variable? You execute the first cmd.ExecuteNonQuery, then close the connection, then rebuild the cmd and then reopen the connection. I can't imagine you're saving anything by closing and reopening the conn object, and that may be what's causing your issue. Try commenting out the "conn.Close" in the Finally block, as well as the conn.Open in second Try block

jaspence

ASKER

Can you set a breakpoint and show exactly what the cmd.CommandText value is just before you run the ExecuteNonQuery line? To do that, set the breakpoint and then when the code breaks, type this in the Immediate window:

Setting the cmd.ExecuteNonQuery() code as the breakpoint the Immediate Window with
?cmd.CommandText states "Unable to evaluate the expression."

Try commenting out the "conn.Close" in the Finally block, as well as the conn.Open in second Try block .

I tried this, still data record only goes into the lab_log table and nothing posted to the second table tested_parts. There was no reason for opening and closing the connection other than that was my last attempt and trouble shooting this alone :)

Scott McDaniel (EE MVE )

Then set a breakpoint before that line. All we need to see is the value of cmd.commandtext.

jaspence

ASKER

Below is what I get:

"INSERT INTO lab_log ([lab_num], [part_num], [part_name], [entry_date], [due_date], [schedule], [supplier], [who], [requestor], [comment]) VALUES (@lab_num, @part_num, @part_name, @entry_date, @due_date, @schedule, @supplier, @who, @requestor, @comment)"

jaspence

ASKER

The previous is after the first Insert section (lab_log) and the below is after the section Insert section (tested_parts)

"INSERT INTO lab_log ([lab_num], [part_num], [part_name], [entry_date], [due_date], [schedule], [supplier], [who], [requestor], [comment]) VALUES (@lab_num, @part_num, @part_name, @entry_date, @due_date, @schedule, @supplier, @who, @requestor, @comment)INSERT INTO tested_parts ([lab_num_tp], [part_num_tp], [part_name_tp], [supplier_tp]) VALUES (@lab_num_tp, @part_num_tp, @part_name_tp, @supplier_tp)"

Scott McDaniel (EE MVE )

You aren't referring to your Parameters correctly:

cmd.Parameters.AddWithValue("@lab_num", lab_num)
cmd.Parameters.AddWithValue("@part_num", part_num)

Note the "@" character ...

jaspence

ASKER

The @ parameter represents the value that is defined and entered into the Infopath form. If it was an error with the @ character why does the lab_log data get entered into the database table wiht the @ character and the tested_parts data does not?

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jaspence

ASKER

Okay I am now able to enter data into both tables, however, I had to "re-activate" the connection open and close so that there are two separate connections and created a new cmd_tp for the tested_parts table.
Now since this the tested_parts section is an optional field in Infopath when I try to submit only data to the lab_log table with nothing going into the tested_parts optional section it gives me NullReference Esxception at the first Dim part_num_tp instance since there is nothing there on the form. How can I get around this?

Scott McDaniel (EE MVE )

You'd have to check for Null values first, something like:

Dim nod As XMLNode '/ or whatever Object is needed
nod = mainDS.SelectSingleNode( "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager)

If Not nod Is Nothing Then
'/ the node was found, so check the value
End If

jaspence

ASKER

Sorry, but can you expand on that a little? Where do I enter the "Dim nod ..." above in my code? Assuming it has to go in before the other Dim sections. Do I need to check for null values on all @tested_parts data ? part_num_tp, part_nam_tp, lab_num_tp, supplier_tp, prod_code_tp are all text fields in my form. So if part_num_tp is blank (i.e. optional section is not opended) then do not enter any data into tested_parts and only enter the data into lab_log.

'Tested Parts Section

Dim part_num_tp As String = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager).Value
Dim part_name_tp As String = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_name_tp", NamespaceManager).Value
Dim lab_num_tp As String = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@lab_num_tp", NamespaceManager).Value
Dim supplier_tp As String = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@supplier_tp", NamespaceManager).Value
Dim prod_code_tp As String = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@prod_code_tp", NamespaceManager).Value

Scott McDaniel (EE MVE )

You'd use code like that INSTEAD of your "Dim part_num_tp" line - essentially, you check for Null values before you try to read those values, so something like this:

Dim part_num_tp as String = ""
Dim part_name_tp as string = ""
etc etc

Dim nod As XMLNode

nod = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager)

If Not nod Is Nothing Then
part_num_tp = nod.Value
End If

nod = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_name_tp", NamespaceManager)

If Not nod is Nothing Then
part_name_tp = nod.Value
End If

etc etc

Then check those before you fill the second part of the INSERT:

If part_num_tp.Length > 0 Then
'/ there's a value in part_num_tp, so do whatever needs to be done here
etc etc
End If

jaspence

ASKER

Now I get the following error for all lines where nod, nod1, nod2 are defined:

Value of type 'System.Xml.XPath.XPathNavigator' cannot be converted to 'System.Xml.XmlNode'.

How can I correct this?

Scott McDaniel (EE MVE )

I assumed you were dealing with an XML file. If not, then you'll have to determine what sort of object you're dealing with, and figure out how to determine if the value in the "XPathnavigator" is NULL.

You may be able to do this:

If Not 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

End If

jaspence

ASKER

I played around a little bit with your last response and I get the error below that runs on the line:
part_num_tp = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp",

"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."

I inserted a break and the ?part_num_tp value after the If Then statement holds the correct value when the tested_parts section is selected or not selected, but I get the error when the tested_parts section is not selected.

CURRENT CODE:
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 Convert.IsDBNull(mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager)) Then
part_num_tp = ""
Else
part_num_tp = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager).Value
End If

jaspence

ASKER

Update: I am still getting the same error and have done multiple internet searches on using VBA to work with optional sections in an Infopath form. When information is entered into the Optional section submitted data is entered properly with no errors. When the optional section is not used, and no data is submitted to the optional section database table, I get the Null reference 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."

jaspence

ASKER

I am still stuck on this NullReference error. Any additional help would be much appreciated.
Thanks!

jaspence

ASKER

Initial question was answered. New question will be initiated to work subsequent problem.