Hello,

I am having trouble getting records to insert and update properly. This is not a consistent problem. It is intermittent.
The page will allow me to correctly update 2 or 3 times generally, however after that it will double up the data in the insert and update.
Here is the code I am using.

Set objConn=Server.CreateObject("ADODB.Connection")

      objConn.ConnectionString = "Provider=msdaora.1;data source=db_name;user id=user_name;password=pwd"
      objConn.Open
       Dim strRecords,objCmd
       Set objCmd = Server.CreateObject("ADODB.Command")

       Set objCmd.ActiveConnection = objConn
      objCmd.CommandText="select id from next_number_gen where table_name='part_trans'"

       Set objRS = objCmd.execute
Do While Not objRS.EOF
   response.write "show me the object: " & objRS("id")& "<BR>"
   dblTrans_ID=objRS("id")
   response.write "read next number gen: " & objRS("id") & "<BR>"
  objRS.MoveNext
Loop
objRS.Close
Set objRS = Nothing

objCmd.CommandText="update part set qty_on_hand='"& iNew_Qty &"' where part_id = '"& strPart_ID &"'"
objCmd.execute

This will create 2 new records with an incremented dblTrans_ID when it decides to fail


objCmd.CommandText="insert into part_trans(trans_id,part_id,cust_id,trans_date,tra ns_qty,trans_type,invoiced) values('" & dblTrans_ID & "', '" & strPart_ID & "', '" & strCust_ID & "',TO_DATE('" & dtTrans_Date & "','DD-MON-YYYY HH24:MI:SS'), '" & iTrans_Qty & "', '" & strTrans_Type & "', '" & strInvoiced & "')"
objCmd.execute


dblTrans_ID = Cint(dblTrans_ID) + 1

This piece of code will update the table with +2 instead of +1 when it decides to fail.

objCmd.CommandText="update next_number_gen set id = '" & dblTrans_ID & "' where table_name = 'part_trans'"
objCmd.execute


objConn.Close

Set objConn = Nothing

I thought it was refreshing the page but I inserted a session variable as a counter and it showed the page only displayed once. Writing out variable tells me the code is working everytime but examining the database shows me the error.
All help would be appreciated. Thanks.

is this all of the code? is there a containing loop you arent showing us? Also, your do while is a waste of processing power (assuming that there are multiple rows being returned) do a SELECT MAX(id) FROM....

This will return the highest number in the datatable aleaviating the need for you to loop through a recordset.

Both of yoru response.writes in that loop should always produce the same value since you dont call MOVENEXT until after both statements have been wrote.

Anything else you can give us to look at?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

There is no loop in this program other than the one notated. The rest of the code deals with assigning variables received from a form. I continue to feel that somehow this page is refreshing but have not found a circumstance that is consistent in causing this anomaly. I originally thought that the users were submitting the form twice on occassion. However I have tested this over and over and have ruled this possibility out as it fails in my testing procedures and I did not submit the form twice. On my form submission page I use the following code to ensure that the page will timeout could this somehow cause this error?

Response.AddHeader "Refresh",CStr(CInt(Session.Timeout + 1) * 1)&";URL=session_timeout_800x600.asp"
In my testing I never allowed this to go to time out value so I have discounted it but maybe I am hasty in doing so.