Re: Duplicate Record with .ADDNEW


<goadswapnil@xxxxxxxxx> wrote in message
news:1163403862.936003.144180@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hey Guys you are not concentrating on the real source of the problem.

Um, Swapnil, is it? Did you read my post before you replied to it? For
someone who writes with such an authoritative tone, you wrote some pretty
screwy things.

The first and foremost is that there's a serious flaw in the schema of
this table.

What led you to declare this I can't imagine, the DDL for his tables appears
no where in these posts, you are guessing -- and one aspect of the T-SQL
he's generating to insert strongly suggests you guessed wrong; that being
the statement::

SELECT @@identity

following the INSERT statement. If the table does *not* have an IDENTITY
column, this statement is worthless. If, otoh, the table *does* have an
IDENTITY column, the conclusions to which you lept are way weak at absolute
best.

Further, INSERT INTO table (f1, f2) VALUES(1, 2) will *not* cause multiple
rows to be inserted, even if the table has no indexes or unique column, and
even if multiple rows exist that cannot be distinguished from each other.
Set-based inserts can do unpredictable things, but INSERT [...] VALUES()
will not insert more than one row. (Unless it is executed twice, which is
what I suspect.)


btw, to the OP, 4 notes:
1.) The SCOPE_IDENTITY() function is typically preferred over @@identity,
which can return wrong answers in certain situations.

2.) If you add the statement SET NOCOUNT ON as the first statement in your
insert SQL block, you won't need to call NextRecordset, and overhead will be
reduced.

3.) To reduce overhead even more, you could:
Parameterize the INSERT statement
(requires use of a Command obj)
and/or
Return the identity in an output parameter
(using a stored proc is the easiest way)

4.) It would be a good idea for you to add a datetime column, with its
default set to getdate(), this gives you the time of day each row was
inserted, with no effort other than adding the field. This can be a very
helpful reality check.


It is mandatory to have a unique field in the database.

Good design: yes; "mandatory": no Temp tables get created all the time,
with no indexes, no constraints, and no guarntee of row-level uniqueness.
If the database server will allow you to create a table without something,
it is by definition, optional. Your use of the word "mandatory" incorrect.

My observation
regarding the code says that it is related to the firing of events.
i.e. the database updation code is running twice.

Whatever...

Whenever you code
events you are unable to debug them unless you have the DoEvents
statement executed.

Poppy***. DoEvents yields the processor, it's typically used inside of
loops to avoid an unresponsive UI. It has nothing to do with debugging
event procedures. In VB the Stop statement sets a hard-coded breakpoint
that will kick you into the debugger, stopped at that line (given that you
are running it in the debugger, otherwise it crashes your app.)

This is the reason why you are unable to find why
this code is updating data twice.

The reaon is that it's intermittent, and he can't reproduce the problem in
tests.

If you would have primary key for the
table there is no possibility of duplicate records.

That's not suffieiently detailed to be true, but let's assume that what you
meant was for the OP to define a uniqueness constraint for at least one
field imported from the XML. That would prevent duplicate imports, sure
enough, and I agree it would be good design as well, but it skirts the
larger issue (that I described in my previous post.)

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.

Just so you know, to me personally, your definitive phrasing is abrasive --
where do you see event handlers anywhere in the OP's posts? He runs the exe
from a batch, for cryin out loud, there probably isn't even a button click
event in this mix. In fact the OP stated that the code is implemented in a
module -- object variables in modules cannot be declared WithEvents.

So here you are, off on some BS tangent about events, saying things like,
"The solution is to..." and also, "...this would certainly solve your
problem," when the actual case is that the OP never even once used the word
"event", and even indicated that his code is implemented in a container, and
of a design that makes event related issues extremely unlikely, if not
impossible.

So as I was saying, your definitive phrasing is abrasive, doubly so in the
context of your clue-impaired analysis, and further compounded by the lack
of technical accuracy that pervades almost every statement in your post.


-Mark



"Sometimes I sits and thinks. Other times I just sits." -Unknown
Philosopher




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







.