Last post Apr 06, 2016 04:47 AM by Yohann Lu
Apr 05, 2016 02:05 PM|amberleaf|LINK
My requirement is a very simple and common one I'd have thought. I want to use a stored procedure to update a row in a gridview instead of a SQL query to eliminate Sql Injections. As it is a multi-user system I want to catch conflicts caused by other user
updates so my stored procedure is as follows:
CREATE PROCEDURE [dbo].[sp_UpdateCompPrice]
-- Add the parameters for the stored procedure here
@UpdatedRows int output
SET Price = @NewPrice
WHERE (Price = @OldPrice);
Select @UpdatedRows = Count(PriceKey)
Where PriceKey = @PriceKey and Price = @NewPrice
Unfortunately I do not know how to run this sp from within an Asp.Net webform. In the form I can add and configure a SqlDataSource to use another stored procedure as the select statement to fill the grid and configure the update statement to use the above
sp but do not know how and when to set the sp parameters. My parameter values are the key value (its included as an integer in the select statement sp), and the old and new values of a data field (type single). I suspect I could set these parameter values
in the code behind so when the update process is initiated they are set correctly but I'm baffled as to where. I've tried in the RowUpdating procedure, i.e. with lines of code like SqlDataSource1.UpdateParameters("PriceKey") = Cint(e.OldValues.item("PriceKey"))
but that doesn't seem to work.
I have a "fix" in place but it is very inelegant, i.e. the Update statement is a simple select statement for the SqlDataSource and in the RowUpdating procedure I've hardcoded everything to run the Update stored procedure as I can apply the parameters there
easily and work with the output parameter. The simple select statement for the Update command is necessary as if I leave it blank I get an error stating SqlDataSource1 is not updateable.
So could someone please let me know where and how can I set the parameters in the code behind and how I would get the output parameter after the update has ended?
Apr 06, 2016 04:47 AM|Yohann Lu|LINK
From your description, I found some tutorials introduce how to use stored procedure in CRUD operation in gridview. You can refer them and try to make a sample.
1: Insert Edit Update in Gridview Using Single Stored Procedure
2: How to use stored procedure for insert, update and delete using grid view control
3: CRUD operation in Asp.Net GridView Using Single Stored Procedure