Update Command

  • Thread starter Thread starter Steven Licciardi
  • Start date Start date
S

Steven Licciardi

I am using parameters etc when populating a dataset using a DataAdapter. I
have an Update command of the folllowing form :

UPDATE projects SET
ID=@ID,Address1=@Address1,Address2=@Address2,Postcode=@Postcode WHERE ID=@ID

Where ID is my primary key. When I change Addres1 etc.. then use
DataAdapter.Update(changes) everything is updated nicely, however if I
change the ID value of one of the rows then use adapter.update(changes) I
get a concurrency violation exception, presumably this occurs because I am
trying to update the ID value when ID is in the WHERE clause. Is there
anything I can do to correct this problem?

Thanks,

Steven
 
Hi Steven,

You should use primary key in where clause.
Is it ID your pk?
If yes, then you really shouldn't change it.
If no, then pick a pk for where clause.

HTH,
 
Hi,
You should use one parameter for the ID in the SET clause and different for
the ID in WHERE clause:

UPDATE projects SET
ID=@ID_new,Address1=@Address1,Address2=@Address2,Postcode=@Postcode WHERE
ID=@ID_old

Then remember to set properly SourceVersion property in both parameters.
Its "Current" for the @ID_new and "Original" for the @ID_old.

Hope this helps
Maciek
 
Thanks for the responses, in this instance the CommandBuilder worked,
although if things get more complicated I will revert to building my own
command strings and adopt the ID_new/ID_old approach.

Thanks,

Steven
 
You are attempting to change the primary key on the fly?? I have NEVER seen
this done. Why do you need to do this?

Jeff
 
Back
Top