populate gridview, stored proc, codebehind, asp.net c#

The good solution from Fernando at: http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_28505181.html

Works when I am populating a gridview using a table. But here with stored procedure I am getting an error at line 11.

Question: How can I correct this?

Thank you.
       SqlCommand cmd = new SqlCommand();
        cmd.Parameters.Clear();
        SqlDataAdapter da = new SqlDataAdapter("spAuunuaCountMedCtr", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@RegionID", Session["RegionID"]);
        cmd.Parameters.AddWithValue("@YYYY", Session["YYYY"]);
        cmd.Parameters.AddWithValue("@StatType_ID", Session["StatType_ID"]);
        var ds = new DataSet();
        da.Fill(ds);
        // Assign the Table to the grids datasource before data binding it.
       // grdHighLight.DataSource = ds.Tables[0];
        grdHighLight.DataBind();

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry MillerCommented:
What error are you getting? If you uncomment line 11, it looks like it should work at first glance. Even using a stored procedure, you still have to assign the gridview a datasource before you try to populate it.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
The error is: Cannot find table 0.
0
Jerry MillerCommented:
It doesn't look like you're executing the stored procedure.  Add a cmd.execute before your fill command.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
There is new error at cmd.execute;:

var ds = new DataSet();
cmd.execute;
da.Fill(ds);

(local variable)sqlCommand cmd

Error:
Only assignment, all, increment, and new object expressions can be used as a statement.
0
Jerry MillerCommented:
Sorry, ExecuteReader is for SQLDataReader, not adapters.

Try this:

SqlCommand cmd = new SqlCommand();
        cmd.Parameters.Clear();
        SqlDataAdapter da = new SqlDataAdapter("spAuunuaCountMedCtr", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@RegionID", Session["RegionID"]);
        cmd.Parameters.AddWithValue("@YYYY", Session["YYYY"]);
        cmd.Parameters.AddWithValue("@StatType_ID", Session["StatType_ID"]);
        var ds = new DataSet();
        da.Fill(ds);
        // Assign the Table to the grids datasource before data binding it.
        if (ds.Tables[0].Rows.Count > 0)
          {
        grdHighLight.DataSource = ds;
        grdHighLight.DataBind();
          }
          else
        {
        grdHighLight.DataSource = null;
        grdHighLight.DataBind();
          }
0
Jerry MillerCommented:
Also you should generally wrap datasource access in a try...catch statement like on this page:

http://www.webcodeexpert.com/2013/02/how-to-bind-gridview-using_9775.html#.U_zoz8LD_Gg

That way you are always closing out your datasources when you are finished with them.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.