Re: Violation of PRIMARY KEY constaint...

I don't agree with this statement:

It works in most cases, but there are some issues.

I would state it this way:

It works in a few cases, and there are lots of issues.

;-)

Robin S.
-------------------------------
"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx> wrote in
message news:8DEB0B3A-F674-45DE-81B1-330DB0CD4677@xxxxxxxxxxxxxxxx
First, I really loath usign the CommandBuilder in a production app. It
works in most cases, but there are some issues.

Second, aggregate proimary keys are problematic, even when you have full
control over them. If any code is going to bomb, this is it.

Third, concurrency checking and key violation checking are good things to
add to any application that uses the standard MS FUD, as it is quite easy
to step out and beomce a fringe case, esp. as your apps grow in
complexity and functionality (I know this first hand).

I have not seen your particular error, but I generallly abstract out my
connection to the database with a stored procedure layer. While the
sprocs versus embedded SQL argument is still not over, I find that the
abstraction layer makes it easier to loosely couple apps that work in
conjunction with each other and make it easier to have schema changes
without impacting current applications.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************
"Ken Abe" <KenAbe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2F6D72A6-373D-44FB-85CF-B9B58F3FEBAD@xxxxxxxxxxxxxxxx
Dear Community,

I'm using ADO.net to update a SQL Server 2005 table. The table has a an
aggregate primary key made of two integers. I use the typcial
SqlDataSet/SqlDataAdapter tools to perform updates and inserts, after an
initial call to da.FillSchema(). My select command is 'select * from t'
(where t is the table). My update and insert commands are both generated
by
SqlCommandBuilder.

Before attempting any updates, I call t.Rows.Contains(<key>). Depending
on
the result of the above, I either (1) use t.Rows.Find(<key>) and then
set
some columns that are not part of the primary key or (2) call t.NewRow()
set
the fields and then t.Rows.Add(). After a series of insert/update
actions I
call da.Update(t).

My application works, as do most applications I have written in the past
using this simple model. However, I occasionally (~1/100,000) catch an
"Violation of PRIMARY KEY" exception when calling da.Update(t).

This baffles me since (1) no other programs or human users access the
table,
(2) the updates are all performed in the same thread, (3) the call to
t.Rows.Add() never fails.

Has anyone experienced a similar issue and found the explanation?








.

Relevant Pages

  • RE: How do I handle this??
    ... A primary key field must contain unique values. ... Microsoft Access MVP ... "Duane Hookom" wrote: ... weekly updates" which has no specific meaning. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: How do I handle this??
    ... primary key rather than using the ProjectID again ... "Duane Hookom" wrote: ... weekly updates" which has no specific meaning. ... resources regarding normalization and database design. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Casading Update
    ... If you have cascade updates set, ... the primary key field in Table1 if there are any matching records in Table2. ... If you remove the relationship then yes, you'll destroy data integrity - you ...
    (microsoft.public.access.formscoding)
  • Error in Adding New Record to Table
    ... I have a Form/Sub-Form that updates two tables. ... that are joined to create primary key for record search against both tables, ... The user's uses a command button to "Add New record", ... Exit Sub ...
    (microsoft.public.access.formscoding)
  • Re: Batchupdates performance
    ... The initial updates are very fast cos the locate command uses the primary key as it's search so it zips along nicely, it's just the batch update command thats slow. ...
    (borland.public.delphi.database.ado)