changing a primary key value

  • Thread starter Thread starter Alan Baljeu
  • Start date Start date
A

Alan Baljeu

I have an SQL table with a 3 column key, PK = (a,b,c). I read that into a DataTable:

SELECT * from MYTABLE where a=1 AND b=2

I edit this in a grid, hiding columns 'a' and 'b'. During editing, column c values may
change, plus new records may be added. After editing, I fill in columns 'a' and 'b'
(values 1 and 2 respectively).

How do I send this data back to the server? Can I do this using a DataAdapter? In my
case, a CommandBuilder won't do the job.

The sticking point is how to write the UPDATE statement as it must use the original value
of column c of the row. I know how to write one update statement for one row, but I can't
figure out the procedure to use a DataAdapter.

If I can't use the DataAdapter, how can I do a full update manually?
 
Yes, you can use a DataAdapter. When you set up the DataAdapter's
UpdateCommand, the parameter for column C should use the
DataRowVersion.Original. (The default is Current)

Like this:

SqlParameter parm = new SqlParameter("@c",SqlDbType.Int);
parm.SourceColumn = "c";
parm.SourceVersion = DataRowVersion.Original;

This tells the DataAdapter and UpdateCommand to use the original value of
column c to pass to the stored procedure.

Hope this helps!

// John Papa
// http://codebetter.com/blogs/john.papa
 
John Papa said:
Yes, you can use a DataAdapter. When you set up the DataAdapter's
UpdateCommand, the parameter for column C should use the
DataRowVersion.Original. (The default is Current)

Like this:

SqlParameter parm = new SqlParameter("@c",SqlDbType.Int);
parm.SourceColumn = "c";
parm.SourceVersion = DataRowVersion.Original;

This tells the DataAdapter and UpdateCommand to use the original value of
column c to pass to the stored procedure.
I think I have it figured. To perform update I need to do
"UPDATE x SET c=@c WHERE a=@olda AND b=@oldb AND c=@oldc"

and set @c as you describe, with the others as normal.

Thanks JP.

Alan.
 
Back
Top