I have this code for add Production data. Since now I have implement inventory module, I start to embed qty update function to every related modules by the help of this SO member. Now it comes to production module that quite complicate because this function must select whether to use INSERT or UPDATE sql to do proper work, which I've tried but failed to make it work. So could you please suggest me with my edited code. Thank you very much.Below is my edited code that fail:
It can use "UPDATE" but fail when use "INSERT" SQL\[code\]<% pd_id = Request.form("pd_id") pdtn_st_date = Request.form("pdtn_st_date") pdtn_st_note = Request.form("pdtn_st_note") pdtn_st_pdNote = Request.form("pdtn_st_pdNote") qty_fabric_used = Request.form("qty_fabric_used") if qty_fabric_used <> "" then qty_fabric_used = YardToCm(qty_fabric_used) else qty_fabric_used = 0 end if unit_per_fb = Request.form("unit_per_fb") if unit_per_fb = "" then unit_per_fb = 0 end if is_finished = Request.form("is_finished") if is_finished <> "" then is_finished = True else is_finished = False end if fb_sheet = Request.Form("fb_sheet") if fb_sheet = "" then fb_sheet = 0 end if fb_width = Request.Form("fb_width") if fb_width = "" then fb_width = 0 end if edit_pdtn_startup = Request.form("edit_pdtn_startup") pdtn_st_qty_act_dz = Request.form("pdtn_st_qty_act") if pdtn_st_qty_act_dz <> "." then pdtn_st_qty_act = DztoPcs(pdtn_st_qty_act_dz) end if '<--------------- Update qty to Inventory table here ------->Dim con Dim rsInventory set con=Server.CreateObject("ADODB.Connection")con.Provider="Microsoft.Jet.OLEDB.4.0"con.Open(Server.Mappath("../database/tkp.mdb")) Set rsInventory = con.Execute("SELECT * FROM tbl_inventory WHERE pd_id = '" & pd_id & "'" ) if rsInventory.EOF then con.Execute("INSERT INTO tbl_inventory (inv_qty_act, inv_date, pd_id) VALUES (inv_qty_act + " & pdtn_st_qty_act & ", date() ,'" & pd_id & "'" )' con.Execute("INSERT INTO tbl_inventory ( pd_id) VALUES ( '" & pd_id & "'") else con.Execute("UPDATE tbl_inventory SET inv_qty_act = inv_qty_act + " & pdtn_st_qty_act & ", inv_date = date() WHERE pd_id = '" & pd_id & "'" ) end if '<---------- ----------------------------------------------------------------------------------------> call checkBlank(pd_id) if SomethingError <> "yes" then Call DBConnOpen() Set Rs = Server.CreateObject("ADODB.Recordset") Set Rs.ActiveConnection = Conn strSQL = "SELECT * FROM tbl_pdtn_startup" if edit_pdtn_startup = "Y" then pdtn_st_id = Request.form("pdtn_st_id") strSQL = strSQL & " WHERE pdtn_st_id =" & pdtn_st_id & "" Rs.Open strSQL, Conn, 1, 3 pdtn_st_qty_act_dz = Request.form("pdtn_st_qty_act") if pdtn_st_qty_act_dz <> "." then pdtn_st_qty_act = DztoPcs(pdtn_st_qty_act_dz) end if end if if edit_pdtn_startup <> "Y" then Rs.Open strSQL, Conn, 1, 3 Rs.AddNew end if Rs.Fields("pdtn_st_date") = pdtn_st_date Rs.Fields("pdtn_st_note") = pdtn_st_note Rs.Fields("pd_id") = pd_id Rs.Fields("pdtn_st_pdNote") = pdtn_st_pdNote Rs.Fields("pdtn_st_qty_act") = pdtn_st_qty_act Rs.Fields("qty_fabric_used") = qty_fabric_used Rs.Fields("unit_per_fb") = unit_per_fb Rs.Fields("is_finished") = is_finished Rs.Fields("fb_width") = fb_width Rs.fields("fb_sheet") = fb_sheet Rs.Update Rs.Close if edit_pdtn_startup = "Y" then response.redirect "production_view.asp?pdtn_st_id=" & pdtn_st_id else strSQL = "SELECT * FROM tbl_pdtn_startup order by pdtn_st_id desc" Rs.Open strSQL, Conn, 1, 3 latest_id = Rs("pdtn_st_id") Rs.close response.redirect "production_addSzCl.asp?pdtn_st_id=" & latest_id end if Call DBConnClose() else call writeInputError end ifsession("pdtn_st_id") = pdtn_st_id %>\[/code\]
It can use "UPDATE" but fail when use "INSERT" SQL\[code\]<% pd_id = Request.form("pd_id") pdtn_st_date = Request.form("pdtn_st_date") pdtn_st_note = Request.form("pdtn_st_note") pdtn_st_pdNote = Request.form("pdtn_st_pdNote") qty_fabric_used = Request.form("qty_fabric_used") if qty_fabric_used <> "" then qty_fabric_used = YardToCm(qty_fabric_used) else qty_fabric_used = 0 end if unit_per_fb = Request.form("unit_per_fb") if unit_per_fb = "" then unit_per_fb = 0 end if is_finished = Request.form("is_finished") if is_finished <> "" then is_finished = True else is_finished = False end if fb_sheet = Request.Form("fb_sheet") if fb_sheet = "" then fb_sheet = 0 end if fb_width = Request.Form("fb_width") if fb_width = "" then fb_width = 0 end if edit_pdtn_startup = Request.form("edit_pdtn_startup") pdtn_st_qty_act_dz = Request.form("pdtn_st_qty_act") if pdtn_st_qty_act_dz <> "." then pdtn_st_qty_act = DztoPcs(pdtn_st_qty_act_dz) end if '<--------------- Update qty to Inventory table here ------->Dim con Dim rsInventory set con=Server.CreateObject("ADODB.Connection")con.Provider="Microsoft.Jet.OLEDB.4.0"con.Open(Server.Mappath("../database/tkp.mdb")) Set rsInventory = con.Execute("SELECT * FROM tbl_inventory WHERE pd_id = '" & pd_id & "'" ) if rsInventory.EOF then con.Execute("INSERT INTO tbl_inventory (inv_qty_act, inv_date, pd_id) VALUES (inv_qty_act + " & pdtn_st_qty_act & ", date() ,'" & pd_id & "'" )' con.Execute("INSERT INTO tbl_inventory ( pd_id) VALUES ( '" & pd_id & "'") else con.Execute("UPDATE tbl_inventory SET inv_qty_act = inv_qty_act + " & pdtn_st_qty_act & ", inv_date = date() WHERE pd_id = '" & pd_id & "'" ) end if '<---------- ----------------------------------------------------------------------------------------> call checkBlank(pd_id) if SomethingError <> "yes" then Call DBConnOpen() Set Rs = Server.CreateObject("ADODB.Recordset") Set Rs.ActiveConnection = Conn strSQL = "SELECT * FROM tbl_pdtn_startup" if edit_pdtn_startup = "Y" then pdtn_st_id = Request.form("pdtn_st_id") strSQL = strSQL & " WHERE pdtn_st_id =" & pdtn_st_id & "" Rs.Open strSQL, Conn, 1, 3 pdtn_st_qty_act_dz = Request.form("pdtn_st_qty_act") if pdtn_st_qty_act_dz <> "." then pdtn_st_qty_act = DztoPcs(pdtn_st_qty_act_dz) end if end if if edit_pdtn_startup <> "Y" then Rs.Open strSQL, Conn, 1, 3 Rs.AddNew end if Rs.Fields("pdtn_st_date") = pdtn_st_date Rs.Fields("pdtn_st_note") = pdtn_st_note Rs.Fields("pd_id") = pd_id Rs.Fields("pdtn_st_pdNote") = pdtn_st_pdNote Rs.Fields("pdtn_st_qty_act") = pdtn_st_qty_act Rs.Fields("qty_fabric_used") = qty_fabric_used Rs.Fields("unit_per_fb") = unit_per_fb Rs.Fields("is_finished") = is_finished Rs.Fields("fb_width") = fb_width Rs.fields("fb_sheet") = fb_sheet Rs.Update Rs.Close if edit_pdtn_startup = "Y" then response.redirect "production_view.asp?pdtn_st_id=" & pdtn_st_id else strSQL = "SELECT * FROM tbl_pdtn_startup order by pdtn_st_id desc" Rs.Open strSQL, Conn, 1, 3 latest_id = Rs("pdtn_st_id") Rs.close response.redirect "production_addSzCl.asp?pdtn_st_id=" & latest_id end if Call DBConnClose() else call writeInputError end ifsession("pdtn_st_id") = pdtn_st_id %>\[/code\]