sqlite不是删除记录 [英] sqlite not deleeting records
本文介绍了sqlite不是删除记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要在订单编辑时更新订单表及其订单详情,以下是我所拥有的。但这里的问题是,虽然我正在使用交易,但订单详细信息项目并未从数据库中删除,而是将新值添加到旧数据中。
有什么问题吗?
Dim cmd As 新 SQLiteCommand(sqlInvInsert,conn)
' conn.SetPassword(DBPassword)
conn.Open()
Dim myTrans 作为 SQLiteTransaction = conn.BeginTransaction()
尝试
如果 value.id> 0 然后
cmd = 新 SQLiteCommand(sqlInvUpdate,conn)
cmd.Parameters.AddWithValue( @ id, value.id)
结束 如果
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 < span class =code-keyword>整数
如果 value.id> 0 然后
InvID = value.id
cmd = 新 SQLiteCommand( 从项目中删除order_id = @ id ,conn)
cmd.Parameters.AddWithValue( @ id,value.id)
cmd.ExecuteNonQuery()
Else
InvID = Convert.ToInt32(cmd.ExecuteNonQuery())
结束 如果
cmd = 新 SQLiteCommand(My.Resources.itm_insert,conn)
对于 每个 itm As InvoiceItem 在 value.items
cmd.Parameters.Add WithValue( @ item,itm.Item)
cmd.Parameters.AddWithValue(< span class =code-string> @ desc,itm.Desc)
cmd.Parameters.AddWithValue( @ amount,itm.Amount)
cmd.Parameters.AddWithValue( @ order_id,InvID)
cmd.ExecuteNonQuery()
下一步
myTrans.Commit()
Catch ex As 异常
myTrans.Rollback()
投掷
最后
cmd.Dispose()
conn.Dispose()
结束 尝试跨度>
用户级联删除选项在数据库级别可用,或者每次执行订单表的更新记录时,都需要删除order_Id的订单详细信息并插入所有记录。
用户级联删除选项在数据库级别可用,或者每次执行订单表的更新记录时,都需要删除order_Id的订单详细信息并插入所有记录。通过这个逻辑,您不必担心最终用户在订单详细信息中所做的更改(例如,是否更新或插入或删除)
我实际上做了整个调试,发现我在没有先从用户界面删除前一个项目的情况下添加项目
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.SetPassword(DBPassword)
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
解决方案
User cascade-delete option available at database level or every time you execute update records for order table, you need to delete order-details for the order_Id and insert all records.
User cascade-delete option available at database level or every time you execute update records for order table, you need to delete order-details for the order_Id and insert all records. By this logic, you need not to worry changes done by the end user in order-details (e.g whether to update or insert or delete)
i actually did a throughout debug and found that i was adding to the items without first deleting the previous from the user interface
这篇关于sqlite不是删除记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文