Modifying index/key values through data adapter

  • Thread starter Thread starter mklapp
  • Start date Start date
M

mklapp

Hello,

I am pulling a group of rows from a SQL Server DB. It
has a multicolumn primary key.

If I change a component of the key, will the Adapter's
Update method be able to get the changes into the DB
(deleting old rows and adding new ones) or will I need to
delete the old rows in one loop, then add new ones in
another loop?

mklapp
 
Hi mklapp,

It depends on how your update/delete command are set.
Anyway, it is a bad idea changing primary key.
 
Understood and agreed. However, I am constrained by time
and the previous versions.

You say it depends on how the commands were set up. I
set them up through the Data Adapter Wizard. I used a
very basic SELECT and let the wizard gen the other
commands.

What are the requirements for a successful outcome?

Thanks,

mklapp
 
mklapp said:
Understood and agreed. However, I am constrained by time
and the previous versions.
Click to expand...

I see.
You say it depends on how the commands were set up. I
set them up through the Data Adapter Wizard. I used a
very basic SELECT and let the wizard gen the other
commands.

What are the requirements for a successful outcome?
Click to expand...

There is a problems with that.
In update command primary key is probably not updated
If you look at the generated code, you'll see how parameters are added to
commands.

Can you post the code for Update command?
 
¤ Hello,
¤
¤ I am pulling a group of rows from a SQL Server DB. It
¤ has a multicolumn primary key.
¤
¤ If I change a component of the key, will the Adapter's
¤ Update method be able to get the changes into the DB
¤ (deleting old rows and adding new ones) or will I need to
¤ delete the old rows in one loop, then add new ones in
¤ another loop?

Values that represent a primary key should never be updated. This is a data integrity issue.

You should add a new row with a different primary key and delete the old row.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Hello

You said:previous versions.

Actually, you perhaps would be doing yourself a favor by paying attention to
Miha and Paul (hi Paul...long time no see :-)) and consider wrapping your
strategy around good relational theory.

The data row is germain to the Key. It exists only for that particular Key.
If any component of the key changes, then it should be treated as a new row,
not an updated row.

This is a basic tenent of 1st Normal form. The mere fact that the key can
change means that the key is too tightly coupled to the data it represents
(personal opinion). Save yourself some heartache down the road, if the key
changes, then create a new row and kill the old one.

Ibrahim
 
Hello and thanks for your input.

Normally, I would not have hesitated to follow the usual
path to addressing this problem. Normally, if I were to
consider de-normalizing a table, it would only be for an
obvious performance advantage(like eliminating an
address/ZipCode table).

In this case, with the demands and nature of the project,
I thought The tool I am using could be leveraged in a
different way. Given that no one has taken the
unconventional approach in this discussion, I have
proceeded with the conventional approach.
 
Back
Top