Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post How to set parameters for SqlDataSource UpdateCommand

    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
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,711
    Thanks
    41
    Thanked 191 Times in 190 Posts
    if you are at this level/in this phase you should be using the code behind- you will want to look at this class for more info... but let's say you had a SQL table...
    Code:
    tbl_Demo
    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)

    Code:
    CREATE PROC usp_ins_Demo @fName varchar(10), @Age int = 18
    AS
    INSERT INTO tbl_Demo (fName, Age)
    VALUES (@fName, @Age)
    
    CREATE PROC usp_updt_Demo @fName varchar(10), @Age int = 18
    AS
    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"
    Code:
    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

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #3
    New Coder
    Join Date
    Jul 2012
    Location
    Bug Tracking, Isssue Tracking
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please take a look at below mentioned article about how to pass parameters using SQLDatasource
    http://forums.asp.net/t/1115044.aspx/1


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •