How to overwrite db records with records from dataset/table?

[sherifffruitfly]

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

 

[Scott M.]

What you are describing is simply doing an update. You will need to write
the SQL commandText for this (UPDATE ..... WHERE .....) yourself.

 

[sherifffruitfly]

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

 

[Scott M.]

Yes.

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

 

[Otis Mukinfus]

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,

Otis Mukinfus

http://www.otismukinfus.com
http://www.arltex.com
http://www.tomchilders.com
http://www.n5ge.com

 

[sherifffruitfly]

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.

 

[Otis Mukinfus]

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.

If you weren't trying to overwrite a constrained column you wouldn't get that
message, but If you say you're not, then I guess you are right.

Good luck with your project,

Otis Mukinfus

http://www.otismukinfus.com
http://www.arltex.com
http://www.tomchilders.com
http://www.n5ge.com