asp-classic - 如何插入另一个更新SQL命令以在这里代码中执行,该命令使用另一个SQL命令删除一个或者多个记录

  显示原文与译文双语对照的内容
0 0

我有这样的代码,当用户从复选框中选择顺序时,从订单表中删除一个或者多个记录。 既然我有库存模块,我希望我的库存记录更新数量( 。将产品数量添加到当前库存) 一旦订单被取消。

实际上,如果代码一次执行一个记录是不应该出现问题的。 但是,这个删除代码是为了删除多个记录,我的技能是无法为它的添加另一个更新Sql命令。 那么你能帮我。 我欣喜若狂啊我

下面是我的现有代码。

<%
 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
%>

这是我要添加到的SQL语句。 因为它需要执行 pd_id,所以在执行上面的sql语句之前应该执行它。

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 &"'" ) 



( 工作代码)
提供 @John 解决方案后,可以将数量更新为一个/多个记录的数据库。

下面是消除了添加')的工作代码'

<%
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
%>
时间:原作者:1个回答

0 0

我建议你在循环中执行库存反转,如下所示:

<%
 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") 
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 &");" 
 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") 
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()
Set Rs = Server.CreateObject("ADODB.Recordset")
response.write strSql
conn.Execute strSql
conn.Execute strSql2
Call DBConnClose()
response.redirect return_page
%>
原作者:
...