Populate a gridview from a stored procedure

Question

User350923160 posted

Hello, 

I have a gridview that I need to fill it with data when a search button is clicked. The stored procedure is created and runs fine when I execute it from SQL server. The stored procedure accepts two parameters (start date, end date) It creates a report for data within the range of start and end dates. I used the following method to populate the grid view but it did not work

 SqlConnection connection = new SqlConnection("connectionstring");
            connection.Open();
            SqlCommand command = new SqlCommand("stored procedure", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            da.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();

For testing purposes, I modified the stored procedure to not accept parameters for right now and hard coded the dates in the stored procedure, that is why you don't see me passing parameters in the above code.

I also tried it without the databind() and nothing happens. The grid view doesn't appear. BTW I didn't create a sqldatasource for the grid view.

Please help. What am I doing wrong?

Answer 1

User-1716253493 posted

you have not set startdate and enddate parameters yet

for example

command.Parameters.AddWithValue("@startdate", DateTime.Now);

Answer 2

User350923160 posted

Hi oned_gk,

for testing purposes, I am not passing parameters and I modified the stored procedure and added start and end dates in the where clause. When I execute the stored procedure from SQL server it doesn't ask me for parameters it just displays the result set, however when I run the code from my application, nothing happens and no error either.

Answer 3

User-1838255255 posted

Hi aazizasp,

According to your code , I test your code ,it run perfect . I make a sample for your reference . I suggest debug your code , and check "da" if have data.

Sample code:

 
 Page:
<asp:GridView ID="GridView1" runat="server"></asp:GridView>

Code Behind:
protected void Page_Load(object sender, EventArgs e)
        {
            String strConnString = ConfigurationManager.ConnectionStrings["MoviesConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            con.Open();
            SqlCommand command = new SqlCommand("test", con);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            da.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }    

stored procedure：
CREATE PROCEDURE test    
AS   
begin
select * from Movies
end

screenshot：

Best Regards ,

Eric Du