Solved

Infopath VBA to submit to poptional databases on Submit_Clicked

Posted on 2014-01-16
12
468 Views
Last Modified: 2014-03-20
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

0
Comment
Question by:jaspence
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
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
 

Author Comment

by:jaspence
Comment Utility
What code would I have to insert to test that?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:jaspence
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
It wouldn't let you change
Dim lab_num As String

to

Dim lab_num As Variant?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 15

Accepted Solution

by:
JimFive earned 500 total points
Comment Utility
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
 

Author Comment

by:jaspence
Comment Utility
Nope it auto changes variant to object:

Dim lab_num_tp As Object = ""
0
 

Author Comment

by:jaspence
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:jaspence
Comment Utility
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
 
LVL 6

Expert Comment

by:livanescu
Comment Utility
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

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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

11 Experts available now in Live!

Get 1:1 Help Now