"sherifffruitfly" <sherifffruit***@gmail.com> wrote in message
news:1170700876.596771.317760@l53g2000cwa.googlegroups.com...
> Hi all,
>
> I'm trying to save datasets (well, datatables in them) back to the
> main database. When the (key value in the) record in the dataset to be
> saved already exists in the main db, I get a "uniqueness constraint
> violated" exception.
>
> I would like my record-writing routine to overwrite pre-existing db
> records, if encountered, with the records in the dataset/table. Do I
> have to actually manually code a find-record-for-this-key-and-delete-
> if-found routine myself? Or is there something as simple as (making
> sh#t up here) a DataAdapter.Insert.Overwrite = true property
> somewhere?
>
> Thanks for any suggestions,
>
> cdj
>

"sherifffruitfly" <sherifffruit***@gmail.com> wrote in message
news:1170704400.344529.234330@v45g2000cwv.googlegroups.com...
> On Feb 5, 11:09 am, "Scott M." <s...@nospam.nospam> wrote:
>> What you are describing is simply doing an update.  You will need to
>> write
>> the SQL commandText for this (UPDATE ..... WHERE .....) yourself.
>
> Oh. Ok. Then would it be considered acceptable practice to simply
> instantiate a new Command, put the Update... Where... text in it,
> parametrize the fields, and call Command.ExecuteNonQuery() ? (i.e., in
> lieu of a dataAdapter?)
>
> Thanks!
>
> cdj
>

On 5 Feb 2007 10:41:16 -0800, "sherifffruitfly" <sherifffruit***@gmail.com>
wrote:

>Hi all,
>
>I'm trying to save datasets (well, datatables in them) back to the
>main database. When the (key value in the) record in the dataset to be
>saved already exists in the main db, I get a "uniqueness constraint
>violated" exception.
>
>I would like my record-writing routine to overwrite pre-existing db
>records, if encountered, with the records in the dataset/table. Do I
>have to actually manually code a find-record-for-this-key-and-delete-
>if-found routine myself? Or is there something as simple as (making
>sh#t up here) a DataAdapter.Insert.Overwrite = true property
>somewhere?
>
>Thanks for any suggestions,
>
>cdj

> On 5 Feb 2007 10:41:16 -0800, "sherifffruitfly" <sherifffruit***@gmail.com>
> wrote:
>
>
>
> >Hi all,
>
> >I'm trying to save datasets (well, datatables in them) back to the
> >main database. When the (key value in the) record in the dataset to be
> >saved already exists in the main db, I get a "uniqueness constraint
> >violated" exception.
>
> >I would like my record-writing routine to overwrite pre-existing db
> >records, if encountered, with the records in the dataset/table. Do I
> >have to actually manually code a find-record-for-this-key-and-delete-
> >if-found routine myself? Or is there something as simple as (making
> >sh#t up here) a DataAdapter.Insert.Overwrite = true property
> >somewhere?
>
> >Thanks for any suggestions,
>
> >cdj
>
> I think an explanation of unique constraints is in order.
>
> The error occurs because you are trying to update a unique key for the table
> with data that already exists in that column in that table.  A unique constraint
> is applied to a column or columns to prevent duplicate data within that column
> in that table.  If the table belongs to you and you don't need that behavior,
> remove the constraint.
>
> If the table belongs to the DBA or some other user you will need to ask them to
> remove the unique constraint from that column in that table.  Be aware that they
> probably had a reason to declare the column with a unique constraint and will
> not change it, because your changing the data probably violates a business rule.
>
> An example of why unique constraints are used:
>
> Consider a database which has a table for storing email user names and the
> designer of the table wanted to make sure user names could not be duplicated.
> The table would have a unique constraint on the column that holds the user name
> data, so that there could not be two users named sherifffruitfly.  If you were
> to try to re-register at gmail as sherifffruitfly you would receive a message
> telling you that name already exists.  The reason the system would know the name
> existed would probably be by trapping a unique constraint violation.
>
> Good luck with your project,

On 10 Feb 2007 16:54:37 -0800, "sherifffruitfly" <sherifffruit***@gmail.com>
wrote:

>On Feb 6, 5:01 am, Otis Mukinfus <phoney.email.addr***@phoney.com>
>wrote:
>> On 5 Feb 2007 10:41:16 -0800, "sherifffruitfly" <sherifffruit***@gmail.com>
>> wrote:
>>
>>
>>
>> >Hi all,
>>
>> >I'm trying to save datasets (well, datatables in them) back to the
>> >main database. When the (key value in the) record in the dataset to be
>> >saved already exists in the main db, I get a "uniqueness constraint
>> >violated" exception.
>>
>> >I would like my record-writing routine to overwrite pre-existing db
>> >records, if encountered, with the records in the dataset/table. Do I
>> >have to actually manually code a find-record-for-this-key-and-delete-
>> >if-found routine myself? Or is there something as simple as (making
>> >sh#t up here) a DataAdapter.Insert.Overwrite = true property
>> >somewhere?
>>
>> >Thanks for any suggestions,
>>
>> >cdj
>>
>> I think an explanation of unique constraints is in order.
>>
>> The error occurs because you are trying to update a unique key for the table
>> with data that already exists in that column in that table.  A unique constraint
>> is applied to a column or columns to prevent duplicate data within that column
>> in that table.  If the table belongs to you and you don't need that behavior,
>> remove the constraint.
>>
>> If the table belongs to the DBA or some other user you will need to ask them to
>> remove the unique constraint from that column in that table.  Be aware that they
>> probably had a reason to declare the column with a unique constraint and will
>> not change it, because your changing the data probably violates a business rule.
>>
>> An example of why unique constraints are used:
>>
>> Consider a database which has a table for storing email user names and the
>> designer of the table wanted to make sure user names could not be duplicated.
>> The table would have a unique constraint on the column that holds the user name
>> data, so that there could not be two users named sherifffruitfly.  If you were
>> to try to re-register at gmail as sherifffruitfly you would receive a message
>> telling you that name already exists.  The reason the system would know the name
>> existed would probably be by trapping a unique constraint violation.
>>
>> Good luck with your project,
>
>Thanks.
>
>I'm not trying to change the data in the key cols.
>
>I'm trying to change the data in the non-key cols.