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);").
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.
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.
नमस्ते,
[KaushaL] BlogTwitter [MS MVP 2008 & 2009] [MCC 2011] [MVP Reconnect 2017]
Don't forget to click "Mark as Answer" on the post that helped you
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.
Member
33 Points
425 Posts
SqlDataSource/UpdateCommand: How to update a primary key field?
Feb 10, 2017 10:30 AM|Yeoman|LINK
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:
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
All-Star
31342 Points
7053 Posts
Re: SqlDataSource/UpdateCommand: How to update a primary key field?
Feb 10, 2017 12:21 PM|kaushalparik27|LINK
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
[KaushaL] Blog Twitter [MS MVP 2008 & 2009] [MCC 2011] [MVP Reconnect 2017]
Don't forget to click "Mark as Answer" on the post that helped you
Member
33 Points
425 Posts
Re: SqlDataSource/UpdateCommand: How to update a primary key field?
Feb 10, 2017 12:46 PM|Yeoman|LINK
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
All-Star
43480 Points
15147 Posts
Re: SqlDataSource/UpdateCommand: How to update a primary key field?
Feb 10, 2017 01:33 PM|PatriceSc|LINK
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)
All-Star
31342 Points
7053 Posts
Re: SqlDataSource/UpdateCommand: How to update a primary key field?
Feb 10, 2017 01:51 PM|kaushalparik27|LINK
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.
[KaushaL] Blog Twitter [MS MVP 2008 & 2009] [MCC 2011] [MVP Reconnect 2017]
Don't forget to click "Mark as Answer" on the post that helped you
None
0 Points
6 Posts
Re: SqlDataSource/UpdateCommand: How to update a primary key field?
Feb 16, 2017 05:30 AM|CoderTom|LINK
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.
Member
33 Points
425 Posts
Re: SqlDataSource/UpdateCommand: How to update a primary key field?
Feb 21, 2017 12:07 PM|Yeoman|LINK
Hello PatrieceSc,
thank you very much. This works perfectly!
Magnus