Getting problem where it appears conn.execute are being executedtwice

J

Jason

I have a problem with an ASP page that appears to be doing the execute
statements twice. All my response.write and debugging processes do
not identify that the condition that allows these multiple writes to
occur. Any suggestions? (The area where I suspect the problem exists
is commented around... but I cannot verify why the work is being
double executed.)

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%option explicit%>
<%
Dim pageName
pageName ="purchaseOrderReceiptSave"
%>
<!--#include file="connections/dbconnect.inc" -->
<!--#include file="includes/security.inc" -->
<body onLoad="poReceiptSaveForm.submit();" bgcolor="#000000">
<!--<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

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

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

******************************************* BELOW IS THE AREA
SUSPECTED ***********************
'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 & "'"

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

******************************************* ABOVE IS THE AREA
SUSPECTED ***********************

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.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>
 
J

Jeff Dillon

You never set more to false? If you have the full version of SQL Server,
consider running SQL Profiler to see the actual SQL being sent

Jeff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top