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

> 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@discussions.microsoft.com> wrote in message
> news:2F6D72A6-373D-44FB-85CF-B9B58F3FEBAD@microsoft.com...
>> 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?
>>
>>
>>
>>
>>
>

"Ken Abe" <KenAbe@discussions.microsoft.com> wrote in message
news:34335C98-F0C1-4B6B-9AFA-721547EA2C71@microsoft.com...
> Thank you all for your input. Based on the assumption that
> SqlCommandBuilder
> is probably the culprit, I went a head an implement my own SP which
> handles
> both insert and update. It does a query on the two integers that make up
> the
> primary key and performs either an insert or an update accordingly:
>
> if (select count(*) from t where key1 = @key1 and key2 = @key2) > 0
> <update>
> else
> <insert>
>
> All I achieved is to slow down the application. The Primary Key
> constraint
> violation still happens.
>
> If aggregate primary keys are problematic, what's an alternative approach
> to
> solving this kind of problem, aside from not using ADO.net?

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