Inserting and updating records in classic asp


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"
   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>”
response.write "read next number gen: " & objRS(“id”) & “<BR>”
Set objRS = Nothing

objCmd.CommandText=“update part set qty_on_hand='”& iNew_Qty &“’ where part_id = '”& strPart_ID &“'”

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 & “')”

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’”


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.

set rs = nothing
set conn = nothing