如何执行一次四个查询,然后检查成功或失败?

我需要执行四个查询,然后如果成功,则必须返回true,否则返回false。

查询影响数据库,但函数返回false

Private Function save_to_data() Dim success As Boolean = False Dim conn As OleDbConnection = GetDbConnection() Dim total_due As Decimal = sanitize(txt_total_due.Text) Dim amount_paid As Decimal = sanitize(txt_due.Text) Dim discount As Decimal = sanitize(txt_discount.Text) Dim balance As Decimal = sanitize(txt_balance.Text) Dim cmdfoods As New OleDbCommand("UPDATE foods SET status='billed' WHERE customer_id = " & lbl_id.Text & "", conn) Dim cmdservices As New OleDbCommand("UPDATE services SET status = 'billed' WHERE customer_id = " & lbl_id.Text & "", conn) Dim cmdreservations As New OleDbCommand("UPDATE reservations SET nights = 4 WHERE customerid = " & lbl_id.Text & "", conn) Dim bill As New OleDbCommand("INSERT INTO bills(customer_id, accomendation, food, service, total_due, amount_paid, discount, balance, transaction_date) VALUES " & _ "(" & lbl_id.Text & ", " & accomendation_total & ", " & food_total & ", " & service_total & ", " & total_due & ", " & amount_paid & " " & _ ", " & discount & ", " & balance & ", '" & Date.Now & "')", conn) conn.Open() If cmdfoods.ExecuteNonQuery And cmdservices.ExecuteNonQuery And cmdreservations.ExecuteNonQuery And bill.ExecuteNonQuery Then success = True Else success = False End If conn.Close() Return success End Function 

还有一些其他的代码问题(sql注入,在几个命令之间共享一个连接),但这是一个正确的方向:

 Try conn.Open() cmdfoods.ExecuteNonQuery() cmdservices.ExecuteNonQuery() cmdreservations.ExecuteNonQuery() bill.ExecuteNonQuery() success = True Catch success = False Finally conn.Close() End Try 

更完整的解决方案:

 Private Function save_to_data() Dim sql As String = _ "UPDATE foods SET status='billed' WHERE customer_id = ? ;" & _ "UPDATE services SET status = 'billed' WHERE customer_id = ? ;" & _ "UPDATE reservations SET nights = 4 WHERE customerid = ? ;" "INSERT INTO bills(" & _ "customer_id, accomendation, food, service, total_due, amount_paid, discount, balance, transaction_date" & _ ") VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?);" Using conn As OleDbConnection = GetDbConnection(), _ cmd As New OleDbCommand(sql, conn) cmd.Parameters.Add("food_customer_id", OleDbType.Integer).Value = lbl_id.Text cmd.Parameters.Add("serv_customer_id", OleDbType.Integer).Value = lbl_id.Text cmd.Parameters.Add("res_customer_id", OleDbType.Integer).Value = lbl_id.Text cmd.Parameters.Add("bill_customer_id", OleDbType.Integer).Value = lbl_id.Text cmd.Parameters.Add("accomendataion", OleDbType.VarChar).Value = accomendation_total cmd.Parameters.Add("food_total", OleDbType.Decimal).Value = food_total cmd.Parameters.Add("service_total", OleDbType.Decimal).Value = service_total cmd.Parameters.Add("total_due", OleDbType.Decimal).Value = total_due cmd.Parameters.Add("amount_paid", OleDbType.Decimal).Value = amount_paid cmd.Parameters.Add("discount", OleDbType.Decimal).Value = discount cmd.Parameters.Add("balance", OleDbType.Decimal).Value = balance cmd.Parameters.Add("transaction_date", OleDbType.Date).Value = Date.Now conn.Open() Return (cmd.ExecuteNonQuery() > 0) End Using End Function 

ExecuteNonQuery不会返回一个布尔值,而是一个表示受插入/删除/更新语句影响的行的整数。

 If cmdfoods.ExecuteNonQuery() > 0 AndAlso cmdservices.ExecuteNonQuery() > 0 AndAlso cmdreservations.ExecuteNonQuery() > 0 AndAlso bill.ExecuteNonQuery() > 0 Then success = True Else success = False End If 

请参阅: https : //stackoverflow.com/a/5349149/3185357