Re: Duplicate Record with .ADDNEW


<mamin@xxxxxxxxxxxxxxxxx> wrote in message
news:1163291509.231486.212700@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Ralph,
Thank you for details. I have changed code to following:
============================================
OrderitemsSQl = "Insert into tblOrderitems (Orderid,productid,
qty,priceperunit,Giftwrap,AmazonOrderItemCode,GiftWrapType) values "
OrderitemsSQl = OrderitemsSQl & "(" & pOrderID & ", '" & pProductID &
"'," & pQuantity & "," & pPricePerUnit & ","
OrderitemsSQl = OrderitemsSQl & "'" & pGiftWrap & "'," &
pAmazonOrderItemCode & ",'" & pGiftWrapType & "');"
OrderitemsSQl = OrderitemsSQl & "SELECT @@IDENTITY;"

'Debug.Print OrderitemsSQl
Set rsOrderItems = ConnOrder.Execute(OrderitemsSQl).NextRecordset
pOrderItemsID = rsOrderItems(0)
=======================================

It is replaced ADDNEW and .UPDATE Method.

After above change problem still exists. After your answer I notices
that

LOCAL had Provider=SQLOLEDB
and
Server had Provider=SQLOLEDB.1

I have made changes to Provider=SQLOLEDB to server to see if problem
still exist. I will let you know if this fixes this issue. If you have
any thought which I can try please let me know. I like to try all
possibility to resolve this logic.

I agree that server became more busier than ever due to holiday season
but I can't imagine that there is no solution.

Thanks
Manoj


I'm am a bit astonished at the moment. Whenever one reports 'strange'
behavior and a '*" or AddNew/Update is present - specifying fields and
changing to a more straightforward method (Insert/Update statements) always
'fixed' the problem.

However, I wouldn't use the Connection.Execute method. There is no reason to
return a recordset. Use the Command object instead.

Anyway, you can be sure that the Insert Statement by itself can't create a
duplicate record. Are you sure the routine is called only once?

I think you need to show more code.

As for SQLOLEDB and SQLOLEDB.1 - the '.1' is just a version number. The
first use says use any version, the latter says use only this version. By
differences in Providers - I meant extreme differences as in using "OLEDB"
in one case and "OLE DB for ODBC" in another. I doubt there is that kind of
difference in these two versions.

-ralph




.

Relevant Pages

  • Re: Duplicate Record with .ADDNEW
    ... I have changed code to following: ... 'Debug.Print OrderitemsSQl ... Server had Provider=SQLOLEDB.1 ... differences in Providers - I meant extreme differences as in using "OLEDB" ...
    (microsoft.public.vb.database.ado)
  • Re: Duplicate Record with .ADDNEW
    ... I like to replace below code with NEW command object. ... 'Debug.Print OrderitemsSQl ... Server had Provider=SQLOLEDB.1 ... duplicate record. ...
    (microsoft.public.vb.database.ado)
  • Re: Duplicate Record with .ADDNEW
    ... 'Debug.Print OrderitemsSQl ... Server had Provider=SQLOLEDB.1 ... Use the Command object instead. ... include something that uniquely identifies each discrete order, in the XML. ...
    (microsoft.public.vb.database.ado)