Getting problem where it appears conn.execute are being executedtwice

Discussion in 'ASP General' started by Jason, Jan 10, 2008.

  1. Jason

    Jason Guest

    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>
     
    Jason, Jan 10, 2008
    #1
    1. Advertising

  2. Jason

    Jeff Dillon Guest

    Re: Getting problem where it appears conn.execute are being executed twice

    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

    "Jason" <> wrote in message
    news:...
    >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>
     
    Jeff Dillon, Jan 16, 2008
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ante Perkovic

    Re: reading sqldatareader after conn.close()

    Ante Perkovic, Jul 2, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    427
    Ante Perkovic
    Jul 2, 2003
  2. Simon Harris

    Conn.Close & Conn.Dispose

    Simon Harris, May 31, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    5,999
    Karl Seguin
    Jun 1, 2005
  3. Replies:
    2
    Views:
    239
  4. PJ

    conn.execute

    PJ, Jan 12, 2004, in forum: ASP General
    Replies:
    1
    Views:
    229
    Bob Barrows
    Jan 12, 2004
  5. michaaal

    Silly Question - SQL conn.execute

    michaaal, May 16, 2004, in forum: ASP General
    Replies:
    2
    Views:
    167
    michaaal
    May 16, 2004
Loading...

Share This Page