Inserting and updating records in classic asp

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,trans_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.

When you say “double up the data” do you mean that it is creating two records in your database instead of one?

Checkout APPEND. Also you may want to consider the database after each write. assuming the record set was defined as rs, and the connection was defined as conn.

rs.close
set rs = nothing
set conn = nothing