StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to insert another UPDATE SQL command to execute in this code, which use another SQL command to delete one or multiple records
primarykey
Id
12690720
data
AcceptedAnswerId
0
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2012-10-02T12:57:51.310
FavoriteCount
0
LastActivityDate
2012-10-03T12:55:38.833
LastEditDate
2012-10-03T12:55:38.833
LastEditorUserId
947322
OwnerUserId
947322
ParentId
0
PostTypeId
1
Score
0
ViewCount
1530
LastEditorDisplayName
text
Body
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.. <pre><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 %> </code></pre> 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. <pre><code>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 & "'" ) </code></pre> (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 ')' <pre><code><% 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 %> </code></pre>
Tags
<sql><asp-classic><vbscript>
Title
How to insert another UPDATE SQL command to execute in this code, which use another SQL command to delete one or multiple records
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USAlxan
UserOwnerUserId
1. USAlxan
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.