0

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">  
    <UpdateParameters>                    
        <asp:Parameter Name="MPID" Type="Int32"  />  
        <asp:Parameter Name="User_Id" Type="String" />   
    </UpdateParameters> 
</asp:SqlDataSource>

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?

2
Contributors
1
Reply
3
Views
5 Years
Discussion Span
Last Post by |-|x
0

The name of the parameter declared in your datasource needs to be the same as the parameter defined in the query/stored procedure. Otherwise they won't map, afik.

<asp:Parameter Name="in_MPID" Type="Int32"  />

You can set the parameter value in code prior to databinding/updating. (Look at OnDataBinding & OnUpdating events)

TECT_DataSource.UpdateParameters["in_MPID"].DefaultValue = x;

Although, I find this logically to be a bit of a hack, as setting the Default Value is not the same as assigning a value - but it has the same outcome.

Alternately, simply create the parameters dynamically at runtime in code.

TECT_DataSource.UpdateParameters.Add("in_MPID",value);
//you can also specify type and other properties in the method overloads

You might want to look into the other types of parameters as well. Of course it depends on your usage/requirements but I like the ControlParameters as they hook up to a page control such as a dropdown and automatically grab its value - great for user input based queries. SessionParameters (hook up to a session variable) are also great for passing things like sessionid, user login, and other persistent data to the query.

<asp:ControlParameter Name="in_MPID" ControlID="DropDownList1" />
//example.

Edited by |-|x: more info

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.