Re: Duplicate Record with .ADDNEW


"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
    ... Hey Guys you are not concentrating on the real source of the problem. ... 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: Duplicate Record with .ADDNEW
    ... I have changed code to following: ... 'Debug.Print OrderitemsSQl ... Server had Provider=SQLOLEDB.1 ... duplicate record. ...
    (microsoft.public.vb.database.ado)
  • 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)
Loading