Solved

Infopath VBA to submit to two databases on Submit_Clicked

Posted on 2013-12-20
19
603 Views
Last Modified: 2014-01-16
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

0
Comment
Question by:jaspence
  • 11
  • 7
19 Comments
 
LVL 84
ID: 39734520
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
0
 

Author Comment

by:jaspence
ID: 39746936
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 :)
0
 
LVL 84
ID: 39747133
Then set a breakpoint before that line. All we need to see is the value of cmd.commandtext.
0
 

Author Comment

by:jaspence
ID: 39748181
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)"
0
 

Author Comment

by:jaspence
ID: 39748225
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)"
0
 
LVL 84
ID: 39748267
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 ...
0
 

Author Comment

by:jaspence
ID: 39748275
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?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39748296
Sorry - you need to "reset" your sb variable before you create the tested_parts insert statement. Note in your second posting above, you have 2 INSERT statements:

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

try something like this:

sb = New System.Text.StringBuilder()

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)")
0
 

Author Comment

by:jaspence
ID: 39748701
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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 84
ID: 39748787
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
0
 

Author Comment

by:jaspence
ID: 39748956
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
0
 
LVL 84
ID: 39749117
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
0
 

Author Comment

by:jaspence
ID: 39751617
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?
0
 
LVL 84
ID: 39751802
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
0
 

Author Comment

by:jaspence
ID: 39752337
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
0
 

Author Comment

by:jaspence
ID: 39765400
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."
0
 

Author Comment

by:jaspence
ID: 39786136
I am still stuck on this NullReference error. Any additional help would be much appreciated.
Thanks!
0
 

Author Closing Comment

by:jaspence
ID: 39786218
Initial question was answered. New question will be initiated to work subsequent problem.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now