View Full Version : How to set parameters for SqlDataSource UpdateCommand

06-28-2012, 05:18 PM
For a Gridview:

I am trying to use a stored procedure for the first time in a SQLDataSource for the UpdateCommand:

<asp:SqlDataSource ID="TECT_DataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>"
ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>"
SelectCommand="SELECT MPID, User_Id, Last_Name, First_Name
FROM Scripts.vw_Tect_Exam"
UpdateCommand="P_TECT_UPD_EXAM_ID" UpdateCommandType="StoredProcedure">
<asp:Parameter Name="MPID" Type="Int32" />
<asp:Parameter Name="User_Id" Type="String" />

I am wondering how the UpdateParameters get their values set, since I only specify a name?
The procedure P_TECT_UPD_EXAM_ID expects two parameters as input: "in_MPID" and "in_UserId"
I am also wondering how to map those values to the input parameters for the procedure as the names are different?

06-29-2012, 12:56 AM
if you are at this level/in this phase you should be using the code behind- you will want to look at this (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx#Y0) class for more info... but let's say you had a SQL table...

Col 1 -- PK -- INT -- not null
Col 2 -- fName -- varchar(10) -- null
Col 3 -- Age -- INT -- not null

and then these SQL procs (insert and then update)

CREATE PROC usp_ins_Demo @fName varchar(10), @Age int = 18
INSERT INTO tbl_Demo (fName, Age)
VALUES (@fName, @Age)

CREATE PROC usp_updt_Demo @fName varchar(10), @Age int = 18
UPDATE tbl_Demo
SET Age = @Age
WHERE fName = @fName

*note you should not use a first name as an index, you should use a key, but for example purposes...

and then you would want to call code with the following
*let's say for example sake there are two text boxes "txt_fName" and "txt_Age"

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmdA = new SqlCommand("usp_ins_Demo", conn);
cmdA.CommandType = CommandType.StoredProcedure;
cmdA.Parameters.Add(new SqlParameter("@fName", txt_fName.Text));
cmdA.Parameters.Add(new SqlParameter("@Age", txt_Age.Text));

*and similar logic for the update

07-01-2012, 05:21 PM
Please take a look at below mentioned article about how to pass parameters using SQLDatasource