locked
SqlDataSource/UpdateCommand: How to update a primary key field? RRS feed

  • Question

  • User260076833 posted

    Hello,

    I have a GridView based on a SqlDataSource. There is a field "num", which is the primary key of the underlying table. The GridView's DataKeyNames field is also set to "num".
    So far so good.

    Now, I would like to let the users change the primary key field. I would take care of existing and conflicting keys in the OnRowUpdating event handler. But I don't know how to distinguish the primary key's old and new values within the SqlDataSource's Updatecommand?

    The UpdateCommand is this:

     UpdateCommand="UPDATE my_items SET num=@num,attr1=@attr1,attr2=@attr2,attr3=@attr3 WHERE (num=@num);"

    As mentioned above, I would like to have different values for "@num" in the assignment ("SET num=@num") and in the WHERE condition ("WHERE (num=@num);").

    How can I do this?

    Thanks
    Magnus

    Friday, February 10, 2017 10:30 AM

Answers

All replies

  • User-1509636757 posted

    Updating a Primary key is not a good idea, Instead you should use a second field to what you want to achieve in above post.

    Reference link: sql server - How to update primary key

    Friday, February 10, 2017 12:21 PM
  • User260076833 posted

    Hello,

    sorry for repeating my question: How can you update the primary key of a table using SqlDataSource with UpdateCommand?

    It's clear that you should avoid updating primary keys, but please keep the question as is.
    The link you provided also deals with foreign keys, which don't exist in my scenario.

    Magnus

    Friday, February 10, 2017 12:46 PM
  • User753101303 posted

    Hi,

    Just use two parameters rather than a single parameter ie it would be :

    UPDATE my_items SET num=@newNum,attr1=@attr1,attr2=@attr2,attr3=@attr3 WHERE (num=@oldNum)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 10, 2017 1:33 PM
  • User-1509636757 posted

    Yeoman

    The link you provided also deals with foreign keys, which don't exist in my scenario.

    The link provided, is just to impress the idea of not updating the Primary key field and instead use a second field for this purpose that user can change its value. The Foreign key scenario (or any other) you can disregard. It would be interesting to know how developer would handle database tables operations. Perhaps, I would have chosen another field (with Unique key constraint) rather than allowing end user to alter database table's Primary key field. Just a thought that you can disregard at your will.

    Friday, February 10, 2017 1:51 PM
  • User439307170 posted

    I think it is cleaner to use the codebehind function (onclick etc.) to do that. It is much cleaner and easy to control instead of using UpdateCommand in the gridview.

    Thursday, February 16, 2017 5:30 AM
  • User260076833 posted

    Hello PatrieceSc,

    thank you very much. This works perfectly!

    Magnus

    Tuesday, February 21, 2017 12:07 PM