Pass parameters from Asp.net to stored procedure in SQL Server and get data back from SQL Server with the parameters' input and view it in a gridView

Question

I have four DropDownLists in ASP.NET. I want to send values selected from the DropDownLists as four parameters to my stored procedure in SQL Server and when I hit the submit button, I want to get the data back from SQL Server to ASP.NET and display it in a GridView. I have tried some code. Since I am fairly new to ASP.NET, I am not sure what is wrong with my code:

protected void Button1_Click1(object sender, EventArgs e)
{
    string s = "prIMDBStatistics_Submit";

    SqlCommand cmd = new SqlCommand(s, con);

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@IPAddress", SqlDbType.VarChar).Value = DropDownList5.SelectedItem.Value;
    cmd.Parameters.Add("@PortNumber", SqlDbType.VarChar).Value = DropDownList2.SelectedItem.Value;
    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = DropDownList3.SelectedItem.Value;
    cmd.Parameters.Add("@CacheType", SqlDbType.VarChar).Value = DropDownList4.SelectedItem.Value;

    DataSet ds = new DataSet();

    con.Open();
    cmd.ExecuteNonQuery();

    GridView1.DataSource = ds;
    GridView1.DataBind();

    con.Close();
}

Answer 1

SqlCommand.ExecuteNonQuery() will not return data. If you want data back you'll need to use a different method on the SqlCommand object and assign the results to the dataset before you bind it to the grid.

The easiest way is to use a DataAdapter. It has a Fill() method that populates a dataset all at once. Then you can bind the data set.

Direct method from SQL command text to DataSet

Answer 2

You are declaring data set

 DataSet ds = new DataSet();

and then binding it to grid

GridView1.DataSource = ds;

From where are you thinking data will be coming?

You have to re-query data and then bind to grid. Following is an example. I am typing directly here, so please ignore syntax errors.

SqlCommand cmd = new Sqlcommand();
cmd.connection = yourConnection;
cmd.CommandText = "select * from table";
SqlDataReader dr = cmd.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();