Running a stored procedure in a SqlDataSource on button click

Question

I am building a C# ASP.NET page where I want to call a stored procedure in a database.

I have setup a SqlDataSource which points to the stored procedure. Parameters are obtained from the Web's control directly (TextBox)

I have a button, when the user clicks it it should run the stored procedure, so I was going to add code like this :

mySqlDataSource.run()

or

mySqlDataSource.exec()

or

mySqlDataSource.storedProcedure()

but of course none of these methods exist.

How do I initial the stored procedure? and how do I get the value returned by the stored procedure please?

Thank you!

What database product do you have? You should have to use Provider API instead of rough SqlDataSource control. — KV Prajapati
– KV Prajapati, Commented Oct 19, 2012 at 3:09
You mean you are not binding it to any databound control like GridView etc? — codingbiz
– codingbiz, Commented Oct 19, 2012 at 3:24
@AVD I am connecting to MSSQL DB, I can't seem to find a "Provider" component in Visual Studio tho. — Gapton
– Gapton, Commented Oct 19, 2012 at 3:28

nunespascal · Accepted Answer · 2012-10-19 04:02:40Z

11

I think the method you are looking for is DataBind. Call it using mySqlDataSource.DataBind()

<asp:SqlDataSource 
  ID="sds2" 
  runat="server" 
  ConnectionString="..."
  SelectCommand="spTest"      
  SelectCommandType="StoredProcedure"
  >
  <SelectParameters>
    <asp:ControlParameter ControlID="TextBox1" PropertyName="Text"
                              Name="ParamName" Type="Int32" DefaultValue="0" />
  </SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="gv" runat="server" DataSourceID="sds2"></asp:GridView>

The stored procedure is executed when you call DataBind. The DataBind method is called automatically if the DataSourceID property of the GridView control refers to a valid data source control.

answered Oct 19, 2012 at 4:02

nunespascal

17.7k2 gold badges45 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nunespascal Over a year ago

spTest is the name of the stored procedure to execute.

Fandango68 Over a year ago

But what if you want to pass a parameter from code behind to the sqldatasource without re-defining the SQL command, connection, etc in C#? I was hoping for an answer like mySqlDataSource.Select(wsMyParmsHere);

Gary Stacey Over a year ago

In the Selecting event for your SQL Datasource, you could have something like e.Command.Parameters["@LocationID"].Value = localvar;

mortdale · Accepted Answer · 2012-10-19 05:02:31Z

2

To init a SP from sqldatasource is easy:

mySqlDataSource.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
mySqlDataSource.UpdateCommand = "sp_name";
mySqlDataSource.Update();

To get a return value from your SP, I would suggest to use SqlCommand:

http://forums.asp.net/t/1177022.aspx

make sure you have a parameter created with ParameterDirection set to ReturnValue or Output

answered Oct 19, 2012 at 5:02

mortdale

3924 silver badges16 bronze badges

Comments

ARUNRAJ · Accepted Answer · 2012-10-19 04:12:15Z

1

//HTML:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
        onselecting="SqlDataSource1_Selecting" SelectCommand="testProc_sp" 
        SelectCommandType="StoredProcedure"></asp:SqlDataSource>
    <br />
    <asp:Button ID="Button3" runat="server" onclick="Button3_Click" Text="Button" />



//c#:

 protected void Button3_Click(object sender, EventArgs e)
    {
        SqlDataSource1.DataBind();
    }

answered Oct 19, 2012 at 4:12

ARUNRAJ

4896 silver badges16 bronze badges

Collectives™ on Stack Overflow

Running a stored procedure in a SqlDataSource on button click

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related