Problem with repeat the update within short period

From: C Chang <cschang_at_maxinter.net>
Date: Thu, 12 Dec 2002 01:49:48 -0500
Message-ID: <3DF8318C.444E@maxinter.net>

My ASP as

open connection obj
..
If Request.Form("submit") = "Cancel" Then

    orderID = Request.Form("qOrder")     Set sbQuoteXfer = Server.CreateObject("ADODB.Command")

      sbQuoteXfer.ActiveConnection = objCon
      sbQuoteXfer.CommandText = "XferQuote"
      sbQuoteXfer.CommandType = adCmdStoredProc
	
    sbQuoteXfer.Parameters.Append sbQuoteXfer.CreateParameter         

("p_orderID",adVarChar,adParamInput,20)

 sbQuoteXfer.Parameters.Append
sbQuoteXfer.CreateParameter("p_status",adVarChar,adParamInput,2)

    sbQuoteXfer.Parameters.Append
sbQuoteXfer.CreateParameter("p_advMsg",adVarchar,adParamOutput,240)

    sbQuoteXfer.Parameters("p_orderID") = orderID     sbQuoteXfer.Parameters("p_status") = 'N'     objCon.BeginTrans
    sbQuoteXfer.Execute
    objCon.CommitTrans
    Set sbQuoteXfer = Nothing
    objCon.Close
    set objCon = Nothing     

 Else             

  Set sbQuoteXfer = Server.CreateObject("ADODB.Command")

      sbQuoteXfer.ActiveConnection = objCon
      sbQuoteXfer.CommandText = "XferQuote"
      sbQuoteXfer.CommandType = adCmdStoredProc
	

  sbQuoteXfer.Parameters.Append

sbQuoteXfer.CreateParameter("p_orderID",adVarChar,adParamInput,20)
sbQuoteXfer.Parameters.Append
sbQuoteXfer.CreateParameter("p_status",adVarChar,adParamInput,2)

  sbQuoteXfer.Parameters.Append
sbQuoteXfer.CreateParameter("p_advMsg",adVarchar,adParamOutput,240)   sbQuoteXfer.Parameters("p_orderID") = orderID   sbQuoteXfer.Parameters("p_status") = 'X'   objCon.BeginTrans
  sbQuoteXfer.Execute
  objCon.CommitTrans
   ..
  Set sbQuoteXfer = Nothing
    objCon.Close
    set objCon = Nothing

End If
.. rest of the HTML form.

and my oracle procedure on 8.1.6 ( set on NT 4) as

PROCEDURE XferQuote

      (	p_orderID    IN   VARCHAR2,
            p_status     IN   VARCHAR2,
            p_advMsg     OUT  VARCHAR2) IS
        
       CURSOR c_quote_xfer IS
          SELECT * 
          FROM  quotes
          WHERE order_id = p_orderID
          FOR UPDATE;
       quote_xfer_rec    c_quote_xfer%ROWTYPE;
       
   BEGIN
       OPEN c_quote_xfer ;
       FETCH c_quote_xfer INTO quote_xfer_rec; 
       IF c_quote_xfer%NOTFOUND THEN
          p_AdvMsg:= p_orderID || ' NOT FOUND.';        
       ELSIF c_quote_xfer.approved = 'Y' THEN
          p_AdvMsg:= p_orderID || ' HAS BEEN PROCESSED.';      
       ELSE
          UPDATE quotes
          SET approved = p_status     
          WHERE  CURRENT OF c_quote_xfer;
          COMMIT;
          p_advMsg := NULL;
           
       END IF;
       CLOSE  c_quote_xfer;
  EXCEPTION
      ...

  END XferQuote;

What happen is When i open the page first time I would get 'X' status ( check under SQL mode)for the record and click on "CANCEL" return to 'N' state. But If the page reopen within next 2 minutes, the status will not turn in 'X' untill my previous logon session disappeared from Oracle
(actuall 30 some seconds) after that. I am totally lost. I check with
v$locked_objects, no any lock existed. Is it because some connection left behind prevents the procedure to run? ( or blame ASP side) Can anyone give me a suggestion? Thanks.

C Chang Received on Thu Dec 12 2002 - 00:49:48 CST