Solved

Infopath VBA to submit to poptional databases on Submit_Clicked

Posted on 2014-01-16
12
484 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 39788786
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
ID: 39789378
What code would I have to insert to test that?
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39789418
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jaspence
ID: 39789498
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 36

Expert Comment

by:PatHartman
ID: 39789715
It wouldn't let you change
Dim lab_num As String

to

Dim lab_num As Variant?
0
 
LVL 15

Accepted Solution

by:
JimFive earned 500 total points
ID: 39789722
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
ID: 39789724
Nope it auto changes variant to object:

Dim lab_num_tp As Object = ""
0
 

Author Comment

by:jaspence
ID: 39789772
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 36

Expert Comment

by:PatHartman
ID: 39789819
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
ID: 39794150
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
ID: 39935478
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 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