locked
Execute a stored procedure with parameters from .aspx - no return values RRS feed

  • Question

  • User-1208223211 posted

    I am trying to create a web application that passes values to a sproc which in turn send out an SMS message upon a button click event. The DBA set up the sproc, all I am trying to do is pass the appropriate values to the sproc and execute. Here is my aspx.cs which apparently does nothing:

    protected void Button_Click(object sender, EventArgs e)

    {
    using (SqlConnection conn = new SqlConnection("Data Source=source;Initial Catalog=catalog;Persist Security Info=True;User ID=id;Password=password"))
    using (SqlCommand smsData = new SqlCommand("usp_sendsms", conn))
    {
    conn.Open();
    smsData.CommandType = CommandType.StoredProcedure;
    smsData.Parameters.Add("@ID", SqlDbType.VarChar).Value = DropDownList1.DataValueField;
    smsData.Parameters.Add("@Text", SqlDbType.VarChar).Value = TextBox1.Text;
    smsData.Parameters.Add("@User", SqlDbType.VarChar).Value = "dude";
    smsData.ExecuteNonQuery();
    }

    I have also attempted:

    <asp:SqlDataSource ID="smsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="usp_sendsms" SelectCommandType="StoredProcedure">
    <SelectParameters>
    <asp:ControlParameter ControlID="TextBox1" Name="Message" PropertyName="text" Type="String" />
    <asp:ControlParameter ControlID="DropDownList1" Name="LocationID"
    PropertyName="SelectedValue" Type="String" DefaultValue="00" />
    </SelectParameters>
    </asp:SqlDataSource>

    smsDataSource.ConnectionString = "Data Source=source;Initial Catalog=catalog;Persist Security Info=True;User ID=id;Password=password";
    smsDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
    smsDataSource.SelectCommand = "usp_sendsms";
    smsDataSource.SelectParameters.Add("@ID", DropDownList1.DataValueField);
    smsDataSource.SelectParameters.Add("@Text", TextBox1.Text);
    smsDataSource.SelectParameters.Add("@User","dude");

    Can anyone please show me how to execute a sproc in asp.net/c# using the parameters outlined above? I would also like the user parameter to be the currently logged in user. Thank you.

     

    Thursday, May 23, 2013 6:15 PM

Answers

  • User-1716253493 posted

    Try another way

    <asp:SqlDataSource ID="smsDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    InsertCommand="usp_sendsms" InsertCommandType="StoredProcedure">
    <InsertParameters>
    <asp:ControlParameter ControlID="TextBox1" Name="Message" PropertyName="text" Type="String" />
    <asp:ControlParameter ControlID="DropDownList1" Name="LocationID"
    PropertyName="SelectedValue" Type="String" DefaultValue="00" />
    <asp:Parameter Name="User" Type="String" DefaultValue="dude" />
    </InsertParameters>
    </asp:SqlDataSource>
    Call code behind :smsDataSource.Insert();

    Change Parameter name according your stored proc

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 23, 2013 8:39 PM

All replies

  • User-1716253493 posted
    smsData.Parameters.Add("@ID", SqlDbType.VarChar).Value = DropDownList1.SelectedValue.ToString();
    Thursday, May 23, 2013 6:34 PM
  • User-1208223211 posted

    Ok so that's a good start - I totally missed that, but the sproc is still not firing. Does my approach look correct?

    Thursday, May 23, 2013 6:45 PM
  • User-1716253493 posted

    Try another way

    <asp:SqlDataSource ID="smsDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    InsertCommand="usp_sendsms" InsertCommandType="StoredProcedure">
    <InsertParameters>
    <asp:ControlParameter ControlID="TextBox1" Name="Message" PropertyName="text" Type="String" />
    <asp:ControlParameter ControlID="DropDownList1" Name="LocationID"
    PropertyName="SelectedValue" Type="String" DefaultValue="00" />
    <asp:Parameter Name="User" Type="String" DefaultValue="dude" />
    </InsertParameters>
    </asp:SqlDataSource>
    Call code behind :smsDataSource.Insert();

    Change Parameter name according your stored proc

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 23, 2013 8:39 PM