Code

Re: Infopath VBA UPDATE not working properly

jaspence — Tue, 28 Jan 2014 19:07:33 GMT

I figured it out. All I was missing was the database connection within each section. I had the connection set up in the Insert section, but not in the Update section :(

Now everything is working correctly :) Thanks for your help.

Re: Infopath VBA UPDATE not working properly

jaspence — Tue, 28 Jan 2014 16:23:49 GMT

I added the @ sign before the parameter names as below and still nothing is being updated in the tested_parts table.

When I add a break to stop the code when running the form such that the code should pass through the UPDATE section all the values (lab_num_tp, alloy_spec, last_tested, index_tp) are holding the correct values from the form, but nothing is being updated in the tested_parts table. When I run the form such that the code passes through the INSERT section the correct values are inserted into the tested_parts table. So Inserts work correctly but updates do not.

If part_num_tp.Length > 0 Then

'If activated

If index_tp.Length > 0 Then

'If previous record exists

Dim cmd_tp As OleDbCommand = conn.CreateCommand()

cmd_tp.CommandType = System.Data.CommandType.Text

sb =

New System.Text.StringBuilder()

sb.Append("UPDATE tested_parts ")

sb.Append(

"SET [lab_num_tp] = @lab_num_tp, [alloy_spec] = @alloy_spec, [last_tested] = @last_tested ")

sb.Append(

"WHERE [index_tp] = @index_tp")

cmd_tp.CommandText = sb.ToString()

cmd_tp.Parameters.AddWithValue(

"@lab_num_tp", lab_num_tp)

cmd_tp.Parameters.AddWithValue(

"@alloy_spec", alloy_spec)

cmd_tp.Parameters.AddWithValue(

"@last_tested", last_tested)

cmd_tp.Parameters.AddWithValue(

"@index_tp", index_tp)

Else

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], [alloy_spec], [last_tested], [DFAR]) ")

sb.Append(

"VALUES ")

sb.Append(

"(@lab_num_tp, @part_num_tp, @part_name_tp, @supplier_tp, @prod_code_tp, @alloy_spec, @last_tested, @DFAR)")

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)

cmd_tp.Parameters.AddWithValue(

"alloy_spec", alloy_spec)

cmd_tp.Parameters.AddWithValue(

"last_tested", last_tested)

cmd_tp.Parameters.AddWithValue(

"DFAR", DFAR)

Re: Infopath VBA UPDATE not working properly

Jimmy — Tue, 28 Jan 2014 15:20:41 GMT

It looks like you're missing the @ symbols before the parameter names in the UPDATE query.

Also please always explain what you mean when you say "it doesn't work".

Infopath VBA UPDATE not working properly

jaspence — Tue, 28 Jan 2014 15:06:28 GMT

I have two main tables that I am entering data into and need to be able to update a record set if one exists for a part number/supplier combination. I have the code working to correctly populate the tested_parts table when a record does not already exist, but can not get the tested_parts table to update when a record does exist. I have a rule, within my Infopath form, which does the query of the tested_parts table based on entered data (supplier_tp and part_num_tp combination) and identifies the record set with the primary key index_tp. If a record set exists I need to update the tested_parts table with lab_num_tp, alloy_spec, last_tested fields associated with the index_tp identified. I have run the debugger and the correct values are being obtained based on the entered data and rules query so I am assuming there is something wrong with my update code and am to much of a noob to catch it.

If part_num_tp.Length > 0 Then

'If activated

If index_tp.Length > 0 Then

'If previous record exists

Dim cmd_tp As OleDbCommand = conn.CreateCommand()

cmd_tp.CommandType = System.Data.CommandType.Text

sb =

New System.Text.StringBuilder()

sb.Append(

"UPDATE tested_parts ")

sb.Append(

"SET [lab_num_tp] = lab_num_tp, [alloy_spec] = alloy_spec, [last_tested] = last_tested ")

sb.Append(

"WHERE [index_tp] = ")

sb.Append(index_tp)

cmd_tp.CommandText = sb.ToString()

cmd_tp.Parameters.AddWithValue(

"lab_num_tp", lab_num_tp)

cmd_tp.Parameters.AddWithValue(

"alloy_spec", alloy_spec)

cmd_tp.Parameters.AddWithValue(

"last_tested", last_tested)

cmd_tp.Parameters.AddWithValue(

"index_tp", index_tp)

Else

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], [alloy_spec], [last_tested], [DFAR]) ")

sb.Append(

"VALUES ")

sb.Append(

"(@lab_num_tp, @part_num_tp, @part_name_tp, @supplier_tp, @prod_code_tp, @alloy_spec, @last_tested, @DFAR)")

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)

cmd_tp.Parameters.AddWithValue(

"alloy_spec", alloy_spec)

cmd_tp.Parameters.AddWithValue(

"last_tested", last_tested)

cmd_tp.Parameters.AddWithValue(

"DFAR", DFAR)

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

End If