How to execute four queries once and then check success or failure?

Question

I need to execute four queries and then if there is success must return true otherwise false.

The queries affect the database but the function returns 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

Change your `And` to `AndAlso` after you open your connection... Also add `>0` after each `ExecuteNonQuery` as this method returns how many row's are affected... The way you are evaluating the condition's wont work because `ExecuteNonQuery doesn't return a Boolean`. — Trevor, Jun 15 '15 at 15:08
If your database support store procedure, it would be a good idea to put all four queries in one. Especially if you need to rollback. — the_lotus, Jun 15 '15 at 15:15
Does MS Access 2007 support store procedures, am using MS access file with extention of .accdb — Saedawke, Jun 15 '15 at 15:16

Joel Coehoorn · Accepted Answer · 2015-06-16T02:05:57.730

There a number of other problems with the code (sql injection, sharing a connection among several commands), but here's a step in the right direction:

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

A more-complete solution:

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

@Joel Coehoorn -- Will it kick an exception if it doesn't update a row? I was under the impression it only kicks an exception if there's a problem. Not updating a row because the criteria isn't met isn't a "problem", right? — Keith, Jun 15 '15 at 15:35

score 1 · Answer 2 · edited May 23 '17 at 12:22

1

ExecuteNonQuery doesn't return a boolean, but an integer representing rows affected by the insert/delete/update statement.

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

Please see: https://stackoverflow.com/a/5349149/3185357

edited May 23 '17 at 12:22

Community

1
1

answered Jun 15 '15 at 15:19

Keith

1,331
2
13
18

No. I don't read comments unless I'm searching for extra information. I see if I can answer the question and also see if anyone else answered already. Not sure how that's downvote worthy, but to each his own. – Keith Jun 15 '15 at 15:23

How to execute four queries once and then check success or failure?

2 Answers2