Re: Duplicate Record with .ADDNEW

Hey Guys you are not concentrating on the real source of the problem.
The first and foremost is that there's a serious flaw in the schema of
this table.
It is mandatory to have a unique field in the database. My observation
regarding the code says that it is related to the firing of events.
i.e. the database updation code is running twice.Whenever you code
events you are unable to debug them unless you have the DoEvents
statement executed. This is the reason why you are unable to find why
this code is updating data twice. If you would have primary key for the
table there is no possibility of duplicate records. The solution is to
find where this code is being exactly called. Try commenting the
calling of this procedure from any one of the events. I'm sure that
this would certainly solve your problem.

Regards,
Swapnil Goad.
S.E Keane Inc.

Mark McGinty wrote:
"Ralph" <nt_consulting64@xxxxxxxxx> wrote in message
news:w46dnb7uCuhu5MvYnZ2dnUVZ_vSdnZ2d@xxxxxxxxxxxxxxx

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

Connection.Execute accepts adExecuteNoRecords as an option, just like
Command.Execute. If he's just going to construct ad-hoc SQL and exec it,
what's the difference? If you suggested parameterizing, and/or reusing the
command object, I could see a reason fot the extra code and object overhead,
but from the looks of the OP's code, I'm not sure what he has to gain there.

As for the problem itself, the OP said his exe runs on a timer, to import
xml from files. Without knowledge of the other involved components this is
just a wild guess, but I suspect the issue has nothing to do with ADO at
all; I would examine all expectations of atomicity -- the file handling flow
in general, and file system sharing semantics, in particular, would be a
logical start. Perhaps the file writer occasionally still has an open
(shared) handle to the file, and deleting it intermittently fails? Hard to
say, but I think I'd opt for setting a file system hook to process the input
in real time, as well as avoid taking chances with open handle collisions.

I'm also uneasy with the OP's statement that "sometimes duplicates are
valid." If these are orders from Amazon.com, they absolutely have to
include something that uniquely identifies each discrete order, in the XML.
(If there isn't, no matter what the source of the input, everyone
responsible on the source end needs to be fired immediately!) :-)

And lastly, as I think of it, this xml almost has to be arriving via HTTP or
SMTP... even if neither of those, I'd bet my last beer that it is not
arriving via SMB/LANMan/Windows file sharing... Point being that the
reasons/incentive for using a [relatively low-tech, for the year 2006]
file-based store-and-foreward, vs processing the data when it arrives, might
be worth a look as well. Because if the data hand-off between components is
not sufficiently atomic to prevent multiple imports of individual data
units, it is also quite likely that loss of data units is at least somewhat
possible, if not equally so.


-Mark





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
    ... If the database server will allow you to create a table without something, ... table there is no possibility of duplicate records. ... field imported from the XML. ... Use the Command object instead. ...
    (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)
  • Re: multiple scans 27374 (sub seven?)
    ... >>Could anyone explain to me how is it possible that several different hosts ... >>try to connect to my server on port 27274 in a very short period of time. ... > real source. ... fool it to reveal it's real IP? ...
    (comp.os.linux.security)