Re: Duplicate Record with .ADDNEW


<mamin@xxxxxxxxxxxxxxxxx> wrote in message
news:1163212296.011947.39880@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I forgot to mention one thing

I added following code in top before...

rsOrderItems.open "tblOrderItems", ConnOrder, adOpenStatic,
adLockOptimistic
rsOrderItems.AddNew

to check there is not duplicate entry is being entered due to previous
logic...but I had same issue..

I execute same program on local SQL server db over and over and I had
no issue where as on production server of SQL Server it is causing
error. I beleive .ADDNEW method is creating duplicate entries on
production server.



Following below code was added to make sure there is no duplcate entery
being added by bad logic

========================================

postemp = InStr(pItemListTemp, arrAmazonStoreCart(i, 0))

If postemp = 0 Then 'If no duplicate productid found process item

pItemListTemp = pItemListTemp & "," & arrAmazonStoreCart(i, 0)

<snipped>


Short Answer.
Assuming "tblOrderItems" is short of "Select * From tblOrderItems" then
DON'T USE .AddNew/.Update. Create a specific Insert statement instead. (To a
lesser degree never use '*' if you can avoid it. But that's another story.
<g>)

Long Answer:
There are multple known issues with this construct - all relating back to
ADO creating and caching its own Insert/Update SQL statements behind the
scenes, and using its own best judgment in doing so, which as you observed
can often be inappropriate.

Providers can also make a difference (ODBC drivers in particular are prone
to problems with this construct), which might be why the Server behaves
differently than the Local. Using the same providers in both cases?

There may be locking scenarios brought about by performing a Select that
wouldn't be necessarily if you were only doing an Insert. I would expect a
running Production Server to have more 'locks', 'triggers', etc. on a table
than your local database.

Cursors can also be a problem. Server-side cursors have more trouble with
AddNew/Update than Client-side. (Makes sense in a way, since a database has
NO clue what they mean <g>).

You may be able to fix your current problem with a change in Provider,
Cursor, Locks, etc. But there may be other performance/concurrency issues
related with using the assortment you have at the moment. Best to just avoid
AddNew/Update.

Also using the AddNew/Update construct adds a lot of over-head and provides
poor clues when things go wrong. A simple insert statement uses far less
resources and is easier to control and debug.

hth
-ralph


.

Relevant Pages

  • Re: Duplicate Record with .ADDNEW
    ... OrderitemsSQl = "Insert into tblOrderitems (Orderid,productid, ... Server had Provider=SQLOLEDB.1 ... no issue where as on production server of SQL Server it is causing ... AddNew/Update than Client-side. ...
    (microsoft.public.vb.database.ado)
  • RE: Duplicating Certificate Templates
    ... I have a similar issue and I would like to confirm if we need a CA server ... smart card logon in SBS network. ... Based on my research, I agree with you, we cannot duplicate a certificate ... we do not need to duplicate certificate template when we just want ...
    (microsoft.public.windows.server.sbs)
  • Re: duplicate name exists on network
    ... > Domain) I still received the error message that a duplicate computer name ... > The DHCP server is an NT server. ... I know how to turn this off on the machines, ... >>> A duplicate name has been detected on the TCP network. ...
    (microsoft.public.win2000.active_directory)
  • Re: Image URL?
    ... I did not put MYWEBSITE under Inetput. ... Production Server c:\MYWEBSITE\userControls ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Double click
    ... > likely a duplicate, you should be able to just discard the duplicate, and ... > record they're submitting.) ... >>> request has been received by the server. ...
    (microsoft.public.inetserver.asp.db)
Loading