I am encountering a similar issue, however mine is almost as if it is executing the conn.execute twice. For a single line detail record, it posts the changes to three tables twice (two inventory adjustments, two part movements, and pretty sure written back to purchase orders twice, but since the number stays the same, there is no visible difference) I have commented around the section that appears to be giving me the trouble with many ****. Any help is greatly appreciated!
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%option explicit%>
<%
Dim pageName
pageName ="purchaseOrderReceiptSave"
%>
<body>
<form name="poReceiptSaveForm" id="poReceiptSaveForm" action="poReceipt.asp" method="post">
<input type="hidden" name="purchase_Order_ID" id="purchase_Order_ID" value="<%=request.form("purchase_Order_ID")%>" />
<%
Dim purchase_Order_Detail_ID, received_Qty, order_Quantity, received_Date, formFieldName
Dim received_By_User, line_Status, sqlPODStmt, myPODConn, myPODRS, more, line, lineOver
Dim poUpdateSQLStmt, lineStatus(50), poStatusValue, poStatus, already_Received_Qty, diff_Qty
Dim purchase_Order_ID, i, po_Status, myConn, sqlPODUStmt, sqlInvMovUStmt, part_ID, qtyOver
Dim myConn1, myConn2, myConn3, myConn4, sqlPODUStmt1
Set myConn1 = Server.CreateObject("ADODB.Connection")
Set myConn2 = Server.CreateObject("ADODB.Connection")
Set myConn3 = Server.CreateObject("ADODB.Connection")
Set myConn4 = Server.CreateObject("ADODB.Connection")
myConn1.Open strConnString
myConn2.Open strConnString
myConn3.Open strConnString
myConn4.Open strConnString
line = 0
po_Status = "C"
qtyOver = false
more = true
purchase_Order_ID = cint(request.form("purchase_Order_ID"))
Do
line = line + 1
line_status = request.form("line_Status" & line)
formFieldName = "received_Qty" & line
if instr(request.form(), formFieldName) > 0 then
purchase_Order_Detail_ID = request.form("purchase_Order_Detail_ID" & line)
sqlPODStmt = "SELECT * FROM purchaseOrderDetails where purchase_Order_Detail_ID = '" & purchase_Order_Detail_ID & "'"
Set myPODConn = Server.CreateObject("ADODB.Connection")
Set myPODRS = Server.CreateObject("ADODB.Recordset")
myPODConn.Open strConnString
myPODRS.Open sqlPODStmt, myPODConn, 3, 1, &H0001
DO while not myPODRS.eof
myPODRS.moveLast
if request.form("received_Qty" & line) <> "" and request.form("line_Status" & line) = "O" and cint(request.form("received_Qty" & line)) >= 0 then
if cint(request.form("received_Qty" & line)) > myPODRS.fields("order_Quantity").value then
qtyOver = true
lineOver = line
else
************** PROBLEM SEEMS TO BE THIS SET EXECUTING TWICE *********
part_ID = request.form("pID" & line)
received_Qty = request.form("received_Qty" & line)
if request.form("already_Received_Qty" & line) = "" then
already_Received_Qty = 0
else
already_Received_Qty = request.form("already_Received_Qty" & line)
end if
diff_Qty = received_Qty - already_Received_Qty
order_Quantity = request.form("order_Quantity" & line)
received_Date = request.form("received_Date" & line)
if trim(received_Date) = "" then
received_Date = date()
end if
received_By_User = request.form("received_By_User" & line)
line_Status = request.form("line_Status" & line)
if cint(Order_Quantity) = cint(received_Qty) then
lineStatus(line - 1) = "C"
line_Status = "C"
else
lineStatus(line - 1) = "O"
end if
'Update purchase order details table
sqlPODUStmt1 = "UPDATE purchaseOrderDetails SET line_Status = '" & line_status & "', received_By_User = '" & received_By_User & "', received_Date = '" & received_Date & "', received_Qty = '" & received_Qty & "' WHERE purchase_Order_Detail_ID = '" & purchase_Order_Detail_ID & "'"
response.write sqlPODUStmt1
myConn1.Execute(sqlPODUStmt1)
'Update part movement table
sqlInvMovUStmt = "INSERT INTO PartsMovements (part_ID, qty, type, date, reason, purchase_Order_ID, purchase_Order_Detail_ID, edit_User) VALUES ('" & part_ID & "', '" & diff_Qty & "', 'add', '" & received_Date & "', 'PO Receipt', '" & purchase_Order_ID & "', '" & purchase_Order_Detail_Id & "', '" & received_By_User & "')"
myConn2.Execute(sqlInvMovUStmt)
'Update part inventory table
Dim myPartInvConn, partInvSQLStmt, myPartInvRs
partInvSQLStmt = "SELECT * FROM PartsInventory where part_ID = '" & part_ID & "'"
Set myPartInvConn = Server.CreateObject("ADODB.Connection")
Set myPartInvRs = Server.CreateObject("ADODB.Recordset")
myPartInvConn.Open strConnString
myPartInvRS.Open partInvSQLStmt, myPartInvConn, 2, 3, &H0001
Dim myUpdatePartInvConn, updatePartInvSQLStmt
Dim current_qoh, new_qoh
myPartInvRS.movefirst
current_qoh = myPartInvRS.fields("quantity_On_Hand").value
new_qoh = current_qoh + diff_Qty
myPartInvRS.fields("quantity_on_hand").value = new_qoh
myPartInvRS.fields("last_Mod_User").value = received_By_User
myPartInvRS.update
myPartInvRS.close
myPartInvConn.close
Set myPartInvRS = nothing
Set myPartInvConn = nothing
end if
************************************************** *******************
else
'Update purchase order details table
if trim(received_Date) = "" then
received_Date = date()
end if
sqlPODUStmt = "UPDATE purchaseOrderDetails SET line_Status = '" & line_status & "', received_By_User = '" & received_By_User & "', received_Date = '" & received_Date & "', received_Qty = '" & received_Qty & "' WHERE purchase_Order_Detail_ID = '" & purchase_Order_Detail_ID & "'"
myConn3.Execute(sqlPODUStmt)
if request.form("line_Status" & line) <> "O" then
lineStatus(line - 1) = "C"
else
lineStatus(line - 1) = "O"
end if
end if
myPODRS.movenext
Loop
myPODRS.close
myPODConn.close
Set myPODRS = Nothing
Set myPODConn = Nothing
else
for poStatusValue = 0 to ubound(lineStatus)
if lineStatus(poStatusValue) = "O" then
po_Status = "O"
exit for
end if
next
if po_Status = "C" then
poUpdateSQLStmt = "UPDATE purchaseOrder SET poStatus='" & po_Status & "' WHERE purchase_Order_ID = '" & purchase_Order_ID & "'"
myConn4.Execute(poUpdateSQLStmt)
end if
Exit Do
end if
Loop While more
myConn1.Close
set myConn1 = Nothing
myConn2.Close
set myConn2 = Nothing
myConn3.Close
set myConn3 = Nothing
myConn4.Close
set myConn4 = Nothing
if qtyOver then
response.redirect("poReceipt.asp?qtyOver=Y&line=" & lineOver )
end if
%>
</form>
</body>
|
