How to insert another UPDATE SQL command to execute in this code, which use another SQL command to delete one or multiple records

Question

I have this code work for deleting one or multiple records from order table when user select order from checkboxes. Now that I have inventory module, I want my inventory record update qty (add the product quantity back to current inventory) once the order had been canceled.

Actually, this shouldn't be problem if the code was implement for delete one record at a time. However, this deleting code is made for deleting multiple records which, with my skill, I couldn't figure out how to add another updating Sql command into. So could you please help me. Thank you very much.

Below is my existing code..

<%
    call navigation
    url = main_area & "?" & "page=" & page_current 
    return_page = "../backend/" &  url
    req_status = request("type")
    if req_status = "restore" then
        req_status = False
    else
        req_status = True
    end if

    record = request("bill_id")
    timestamp = now()
    record = trim(record)
    if len(record) > 3 then   ' multiple records was selected
        arrVals = split(record,",")             


        strSql = ""
        strSql = strSql & "DELETE * FROM tbl_bill_total WHERE "     
        for i = 0 to ubound(arrVals)
             if i = 0 then           
              strSql = strSql & "bill_id IN ("& trim(arrVals(i)) & "    "
             else
              strSql = strSql & ","& trim(arrVals(i)) & ""
             end if
        next
        strSql = strSql & ") "

        strSql2 = strSql2 & "DELETE * FROM tbl_order WHERE "    
        for t = 0 to ubound(arrVals)
             if t = 0 then
              strSql2 = strSql2 & " tbl_order.bill_id IN ("& trim(arrVals(t)) & "    "
             else
              strSql2 = strSql2 & ","& trim(arrVals(t)) & ""
             end if
        next
        strSql2 = strSql2 & "); "       

    else

        strSql  = "DELETE * FROM tbl_bill_total WHERE bill_id=" & record & " "  
        strSql2  = "DELETE * FROM tbl_order WHERE bill_id =" & record & " " 
    end if




Call DBConnOpen()
Set Rs = Server.CreateObject("ADODB.Recordset")
response.write strSql

conn.Execute strSql
conn.Execute strSql2
Call DBConnClose()

response.redirect return_page
%>

and this is the SQL statement that I want to add into. Since it need pd_id to execute, I think this should be execute before execute the above SQL statements.

Set rsOrder = conn.Execute("SELECT * FROM tbl_order WHERE bill_id = " & record & "" )           
pd_id = rsOrder.fields.item("pd_id")
od_qty = rsOrder.fields.item("od_qty")


Set rsInventory = conn.Execute("UPDATE tbl_inventory SET inv_qty_act = inv_qty_act + " & od_qty & ", inv_date =  " & date() & " WHERE pd_id = '" & pd_id & "'"  )

(Working Code)
With @John provided solution now it can update qty back to database for one/multiple records selected.

Below is the working code that had eliminated addition ')'

<%
Call DBConnOpen()
Set Rs = Server.CreateObject("ADODB.Recordset")

    call navigation
    url = main_area & "?" & "page=" & page_current 
    return_page = "../backend/" &  url
    req_status = request("type")
    if req_status = "restore" then
        req_status = False
    else
        req_status = True
    end if

    record = request("bill_id")
    timestamp = now()
    record = trim(record)
    if len(record) > 3 then   ' multiple records was selected
        arrVals = split(record,",")             


        strSql = ""
        strSql = strSql & "DELETE * FROM tbl_bill_total WHERE "     
        for i = 0 to ubound(arrVals)
             if i = 0 then           
              strSql = strSql & "bill_id IN ("& trim(arrVals(i)) & "    "
             else
              strSql = strSql & ","& trim(arrVals(i)) & ""
             end if
        next
        strSql = strSql & ") "

        strSql2 = strSql2 & "DELETE * FROM tbl_order WHERE "    
        for t = 0 to ubound(arrVals)

        Set rsOrder = conn.Execute("SELECT * FROM tbl_order WHERE bill_id = " & arrVals(t) & "")     
            pd_id = rsOrder.fields.item("pd_id")  
            od_qty = rsOrder.fields.item("od_qty")  
                od_qty  = DzToPcs(od_qty)
        conn.Execute("UPDATE tbl_inventory SET inv_qty_act = inv_qty_act + " & od_qty & ", inv_date =  " & date() & " WHERE pd_id = '" & pd_id & "'"  ) 

             if t = 0 then
              strSql2 = strSql2 & " tbl_order.bill_id IN ("& trim(arrVals(t)) & "    "

             else
              strSql2 = strSql2 & ","& trim(arrVals(t)) & ""
             end if
        next
        strSql2 = strSql2 & "); "       


'   response.Write "strSql3 = " & strSql3
    else


        Set rsOrder = conn.Execute("SELECT * FROM tbl_order WHERE bill_id = " & record & " ")             
            pd_id = rsOrder.fields.item("pd_id")  
            od_qty = rsOrder.fields.item("od_qty")  
                od_qty  = DzToPcs(od_qty)   
        conn.Execute("UPDATE tbl_inventory SET inv_qty_act = inv_qty_act + " & od_qty & ", inv_date =  date() WHERE pd_id = '" & pd_id & "'"  ) 

        strSql  = "DELETE * FROM tbl_bill_total WHERE bill_id=" & record & " "  
        strSql2  = "DELETE * FROM tbl_order WHERE bill_id =" & record & " " 
    end if




'Call DBConnOpen() --> move to top line
'Set Rs = Server.CreateObject("ADODB.Recordset") --> move to top line
'response.write strSql2

conn.Execute strSql
conn.Execute strSql2
Call DBConnClose()

response.redirect return_page
%>

How to insert another UPDATE SQL command to execute in this code, which use another SQL command to delete one or multiple records

Answers (1)

Related Questions