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

  • Question

  • User260076833 posted


    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?


    Friday, February 10, 2017 10:30 AM


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


    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.


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


    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


    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!


    Tuesday, February 21, 2017 12:07 PM