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