CODEWITHSUNDEEP
sqlasp.netsqldatasource
user3674481
user3674481

Reputation: 47

Button click to fire stored procedure from SqlDataSource

I have an sql stored procedure which accepts 3 parameters and acts as a search facility which all works fine as its supposed to. I also have an asp page with 3 corresponding text boxes, a grid view, an sql source which points to the stored proc and a button but I'm stuck as to what code to put behind my button to get the grid view display the search results.

I tried sqlDataSource1.Select(DataSourceSelectArguments.Empty) as I believed the select arguments were being handled by the sql data source on the page but it's not pulling anything back.

Upvotes: 1

Views: 548

Answers (2)

Tim
Tim

Reputation: 4099

Set up a SqlCommand, and set its CommandType to StoredProcedure. Use your DB connection when you create the command object.

Add your three parameters to the command, set their values, then execute it. Make sure you do this in a Try/Catch block and close the connection in the Finally block or you could wind up with connections that don't close. (This has bitten several teams I've been on in the past)

You'll need to choose the proper method when you execute based on what you want to do. (ExecuteNonQuery, ExecuteScalar, or ExecuteReader) Look at the docs and figure out what's best for this feature.

I personally don't like SqlDataSources dragged and dropped on the page. I prefer to work in the codebehind because I feel it's easier to debug and error handle.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand%28v=vs.110%29.aspx

Sample:

SqlCommand command = new SqlCommand(
        "myProcedure", connection);
command.commandType=CommandType.StoredProcedure;
command.Parameters.Add(new SqlParamater("name",datatype,length));
cmd.Parameters["name"].Value = "value";
...
try
{
   connection.Open();
   cmd.ExecuteScalar(); //or whatever method you need
}
catch(exception xx)
{
   //do your error handling here
}
finally
{
  connection.Close(); //Remember to do this here!
}

Upvotes: 0

Bill Kindig
Bill Kindig

Reputation: 3652

For the on click of the button:

GridView1.DataSourceID = "sqlDataSource1"
GridView1.DataBind();

Your button click will cause a postback and your grid will be updated with your results.

Upvotes: 2

Related Questions