i need to update orders table and its orders details when order is edited, here is what i have. But the issue here is that although am using a transaction, the order details items are not deleting from the database, instead the new values are being added to the old.
is there something am doing wrong?
Dim cmd As New SQLiteCommand(sqlInvInsert, conn)
conn.Open()
Dim myTrans As SQLiteTransaction = conn.BeginTransaction()
Try
If value.id > 0 Then
cmd = New SQLiteCommand(sqlInvUpdate, conn)
cmd.Parameters.AddWithValue("@id", value.id)
End If
cmd.Parameters.AddWithValue("@customer", value.customer)
cmd.Parameters.AddWithValue("@vessel", value.vessel)
cmd.Parameters.AddWithValue("@size", value.size)
cmd.Parameters.AddWithValue("@blno", value.blno)
cmd.Parameters.AddWithValue("@commodity", value.commodity)
cmd.Parameters.AddWithValue("@pol", value.pol)
cmd.Parameters.AddWithValue("@pod", value.pod)
Dim InvID As Integer
If value.id > 0 Then
InvID = value.id
cmd = New SQLiteCommand("Delete From Items Where order_id=@id", conn)
cmd.Parameters.AddWithValue("@id", value.id)
cmd.ExecuteNonQuery()
Else
InvID = Convert.ToInt32(cmd.ExecuteNonQuery())
End If
cmd = New SQLiteCommand(My.Resources.itm_insert, conn)
For Each itm As InvoiceItem In value.items
cmd.Parameters.AddWithValue("@item", itm.Item)
cmd.Parameters.AddWithValue("@desc", itm.Desc)
cmd.Parameters.AddWithValue("@amount", itm.Amount)
cmd.Parameters.AddWithValue("@order_id", InvID)
cmd.ExecuteNonQuery()
Next
myTrans.Commit()
Catch ex As Exception
myTrans.Rollback()
Throw
Finally
cmd.Dispose()
conn.Dispose()
End Try