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

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.

 

Answer 1

User-1716253493 posted
smsData.Parameters.Add("@ID", SqlDbType.VarChar).Value = DropDownList1.SelectedValue.ToString();

Answer 2

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?

Answer 3

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