当我在订单详细信息中插入一个包含多个项目的订单时,只插入最后一个订单详细信息项目。
我使用以下代码插入数据库
Try
order_id = value.ID
If order_id = 0 Then
cmd = New SQLiteCommand(insert_order, conn)
cmd.Parameters.AddWithValue("@customer_id", value.CustomerID)
cmd.Parameters.AddWithValue("@status", value.Status)
cmd.Parameters.AddWithValue("@deliver", value.Deliver)
cmd.Parameters.AddWithValue("@created", Now.ToString("yyyy-MM-dd"))
cmd.Parameters.AddWithValue("@deleted", "0")
order_id = Convert.ToInt32(cmd.ExecuteScalar())
Else
cmd = New SQLiteCommand(update_order, conn)
cmd.Parameters.AddWithValue("@customer_id", value.CustomerID)
cmd.Parameters.AddWithValue("@status", value.Status)
cmd.Parameters.AddWithValue("@deliver", value.Deliver)
cmd.Parameters.AddWithValue("@created", Now.ToString("yyyy-MM-dd"))
cmd.Parameters.AddWithValue("@deleted", "0")
cmd.Parameters.AddWithValue("@id", order_id)
cmd.ExecuteNonQuery()
'// delete previous order details
cmd = New SQLiteCommand(delete_order_details, conn)
cmd.Parameters.AddWithValue("@id", order_id)
cmd.ExecuteNonQuery()
End If
'// Add order details
For Each itm As typOrderDetail In value.orderDetails
cmd = New SQLiteCommand(insert_order_details, conn)
cmd.Parameters.AddWithValue("@cloth_id", itm.ClothID)
cmd.Parameters.AddWithValue("@order_id", order_id)
cmd.Parameters.AddWithValue("@color", itm.Color)
cmd.Parameters.AddWithValue("@desc", itm.Desc)
cmd.Parameters.AddWithValue("@qty", itm.Qty)
cmd.Parameters.AddWithValue("@cost", itm.Cost)
cmd.Parameters.AddWithValue("@service_id", itm.ServiceID)
cmd.Parameters.AddWithValue("@deleted", "0")
cmd.ExecuteNonQuery()
Next
sqlTrans.Commit()
'If bSave Then sqlTrans.Commit() Else sqlTrans.Rollback()
Catch ex As Exception
'sqlTrans.Rollback()
Throw
Finally
cmd.Dispose()
conn.Dispose()
End Try
这是sql字符串
插入订单([customer_id]、[status]、[delivery]、[created]、[deleted])VALUES(@customer_id、@status、@delivery、@created、@deleted);选择last_insert_rowid();
插入订单_详细信息([order_id]、[service_id]、[cloth_id]、[desc]、[deleted]、[qty]、[color]、[cost])值(@order_id、@service_id、@cloth_id、@desc、@deleted、@qty、@color、@cost)
请问我做错了什么?
不了解如何在代码中处理事务。试试这个(在C#中,对不起):
using(SqliteTransaction tr = con.BeginTransaction()){
foreach(var itm in value.orderDetails) {
//construct the command, add parameters, etc.
...
//link the command to the transaction
cmd.Transaction = tr;
//execute the command
cmd.ExecuteNonQuery();
}
tr.Commit();
}
不能100%确定这会修复它,但对于所有订单详细信息,请保留相同的SQLiteCommand对象。在循环之外声明它,只需更新参数值并在循环中运行查询。