asp.net C# - populate a gridview using an existing stored procedure...

using a StoreProce with three input parameter: spPercentageForm (@Metric_ID INT = NULL,@YYYY NVARCHAR(4) = NULL,@RegionID INT = NULL). How to populate gridview ID="gvLefttForm" ?  

FYI: I have:
SqlDataSource: LeftGV but the parameters are entered as constant Metric_ID = 101, YYYY = "2014", RegionID = 1 during SqlDataSource: LeftGV construction.

From Page_Load event I want to call: PopulateGV(102, "2013", 2, "LeftGV");

Question: How can I modify the sample code below to populate ID="gvLefttForm"?

Thank you.
    static void PopulateGV(int Metric_ID, string yyyy, int RegionID, string gvName)
    {
        using (SqlConnection connection = new SqlConnection("myConnection"))
        {
            // Create the command and set its properties.
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "updateGridview";
            command.CommandType = CommandType.StoredProcedure;

            // Add the input parameter and set its properties.
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@Metric_ID";    // <--------- ????????????????????
            parameter.ParameterName = "@YYYY";         // <--------- ????????????????????
            parameter.ParameterName = "@RegionID";     // <--------- ????????????????????

            parameter.SqlDbType = SqlDbType.NVarChar;
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = Metric_ID;        // <--------- ????????????????????
            command.Parameters.Add(parameter);
            parameter.Value = yyyy;             // <--------- ????????????????????
            command.Parameters.Add(parameter);
            parameter.Value = RegionID;         // <--------- ????????????????????
            command.Parameters.Add(parameter);

            // Open the connection and execute the reader.
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    //How to bind it to gridview called gvName ="gvLefttForm?"
                }
            }
            else
            {
                // igonor this option for now
            }
            reader.Close();
        }
    }

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.

Harish VargheseProject LeaderCommented:
First of all, you are interacting with database in this code and you want to populate web control (gridview) also in the same function, which is not a good design. You should separate your data access layer from web layer. If you want to continue this way, where is this function available? If it is on your aspx.cs file, you can directly access gvLefttForm directly. However, there are few mistakes in the code:
1. Parameter assignments are not correct - you are changing the name of parameter three times before adding it to the sql command.
2. If you use SQLDataReader, you need to use a loop to get the values and assign them to a datatable. Instead you can use a data adapter to fill a dataset directly.
Change the code as below to return a DataTable that can be assigned to the gridview as required:
    static DataTable PopulateGV(int Metric_ID, string yyyy, int RegionID)
    {
        using (SqlConnection connection = new SqlConnection("myConnection"))
        {
            // Create the command and set its properties.
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "updateGridview";
            command.CommandType = CommandType.StoredProcedure;

            // Add the input parameter and set its properties.
            command.Parameters.AddWithValue("@Metric_ID", Metric_ID); 
            command.Parameters.AddWithValue("@YYYY", yyyy); 
            command.Parameters.AddWithValue("@RegionID", RegionID); 

            // Open the connection and execute the reader.
            connection.Open();
            SqlDataAdapter sda = new SqlDataAdapter(command);

            DataSet ds = new DataSet();
            sda.Fill (ds);

            /***** NOW POPULATING GRIDVIEW
            IT IS A BETTER IDEA TO RETURN A DATATABLE FROM HERE
            THAT CAN BE ASSIGNED AS THE DATASOURCE TO YOUR GRIDVIEW
            FROM THE CALLING FUNCTION
            *******/
            return ds.Tables[0]
        }
    }

Open in new window

And, on the form from where you are calling this function, you can assign this as the datasource of gvLefttForm as below:
gvLefttForm.DataSource = PopulateGV(102, "2013", 2);
gvLefttForm.DataBind();
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:
Hi Harish,

Thank you very much for the solution and good suggestions.

re:> You should separate your data access layer from web layer
I am sort of beginner, I definately will take your advise on this and learn more about this. If you can think of good link on this subject please share it with me.


re:> you can directly access gvLefttForm directly.
Yes, this gridview is on the same page where I am putting this code. FYI, in the aspx page I have:
<asp:GridView ID="gWLeftForm" runat="server" AutoGenerateColumns="False" BackColor="White"

As you can see, I actualy have gWLeftForm not gvLefttForm. I wanted to improve the syntax before I post the question. The reason for mentioning this may be related to my followup question below.

I am getting an error on the second line at DataBind().

gWLeftForm.DataSource = PopulateGV(102, "2013", 2);
gWLeftForm.DataBind();

Question: How can I correct this.

Error details:
System.InvalidOperationException was unhandled by user code
  HResult=-2146233079
  Message=Both DataSource and DataSourceID are defined on 'gwLeftForm'.  Remove one definition.
  Source=System.Web
  StackTrace:
       at System.Web.UI.WebControls.DataBoundControl.EnsureSingleDataSource()
       at System.Web.UI.WebControls.DataBoundControl.ConnectToDataSourceView()
       at System.Web.UI.WebControls.DataBoundControl.GetData()
       at System.Web.UI.WebControls.DataBoundControl.PerformSelect()
       at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
       at System.Web.UI.WebControls.GridView.DataBind()
       at Default2.Page_Load(Object sender, EventArgs e) in c:\Users\Mike\Documents\_smc\__CS37\MasterPageTestJul25\Default2.aspx.cs:line 41
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException: 

Open in new window

In an attempt to correct this error, I changed ID="gWLeftForm" to ID="gWLeftFormID" to avoid naming conflict. But the error didn't go away.

Here is my latest aspx code for the gridview:
(FYI, it is possible that I may have used this name to create a data source not in use now but its trace is somewhere in the application)
               <asp:GridView ID="gwLeftFormID" runat="server" AutoGenerateColumns="False" BackColor="White"
                    BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="MedCtrID"
                    DataSourceID="LeftGW" Font-Names="Arial" Font-Size="Small" ForeColor="Black"
                    GridLines="Vertical" Width="412px">
                    <AlternatingRowStyle BackColor="White" />
                    <Columns>
                        <asp:BoundField DataField="Med_Center" HeaderText="Med Ctr" ReadOnly="True" SortExpression="Med_Center">
                            <ItemStyle HorizontalAlign="Left" Width="72px" Wrap="False" />
                        </asp:BoundField>
                        <asp:BoundField DataField="LastYearCount" DataFormatString="{0:F0}" HeaderText="LY"
                            SortExpression="LastYearCount">
                            <ItemStyle Width="72px" Wrap="False" HorizontalAlign="Center" />
                        </asp:BoundField>
                        <asp:BoundField DataField="01" DataFormatString="{0:F0}" HeaderText="01" SortExpression="01">
                            <ItemStyle Width="25px" Wrap="False" />
                        </asp:BoundField>
                        <asp:BoundField DataField="02" DataFormatString="{0:F0}" HeaderText="02" SortExpression="02" />
                        <asp:BoundField DataField="03" DataFormatString="{0:F0}" HeaderText="03" SortExpression="03" />
                        <asp:BoundField DataField="04" DataFormatString="{0:F0}" HeaderText="04" SortExpression="04" />
                        <asp:BoundField DataField="05" DataFormatString="{0:F0}" HeaderText="05" SortExpression="05" />
                        <asp:BoundField DataField="06" DataFormatString="{0:F0}" HeaderText="06" SortExpression="06" />
                        <asp:BoundField DataField="07" DataFormatString="{0:F0}" HeaderText="07" SortExpression="07" />
                        <asp:BoundField DataField="08" DataFormatString="{0:F0}" HeaderText="08" SortExpression="08" />
                        <asp:BoundField DataField="09" DataFormatString="{0:F0}" HeaderText="09" SortExpression="09" />
                        <asp:BoundField DataField="10" DataFormatString="{0:F0}" HeaderText="10" SortExpression="10" />
                        <asp:BoundField DataField="11" DataFormatString="{0:F0}" HeaderText="11" SortExpression="11" />
                        <asp:BoundField DataField="12" DataFormatString="{0:F0}" HeaderText="12" SortExpression="12" />
                    </Columns>
                    <FooterStyle BackColor="#CCCC99" />
                    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                    <RowStyle BackColor="#F7F7DE" Width="25px" Wrap="False" />
                    <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#FBFBF2" />
                    <SortedAscendingHeaderStyle BackColor="#848384" />
                    <SortedDescendingCellStyle BackColor="#EAEAD3" />
                    <SortedDescendingHeaderStyle BackColor="#575357" />
                </asp:GridView>
                <asp:SqlDataSource ID="LeftGW" runat="server" ConnectionString="<%$ ConnectionStrings:ROD_July18ConnectionString %>"
                    SelectCommand="spPercentageForm" SelectCommandType="StoredProcedure">
                    <SelectParameters>
                        <asp:Parameter DefaultValue="101" Name="Metric_ID" Type="Int32" />
                        <asp:Parameter DefaultValue="2013" Name="YYYY" Type="String" />
                        <asp:Parameter DefaultValue="1" Name="RegionID" Type="Int32" />
                    </SelectParameters>
                </asp:SqlDataSource>

Open in new window

0
Harish VargheseProject LeaderCommented:
Okay, the message is clear:
"Both DataSource and DataSourceID are defined on 'gwLeftForm'.  Remove one definition."
Since we are programmatically assigning a data source to the gridview, we should not provide DataSourceId in the design. From below code, remove DataSourceID="LeftGW":
<asp:GridView ID="gwLeftFormID" runat="server" AutoGenerateColumns="False" BackColor="White"
                    BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="MedCtrID"
                    DataSourceID="LeftGW" Font-Names="Arial" Font-Size="Small" ForeColor="Black"
                    GridLines="Vertical" Width="412px">

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Harish VargheseProject LeaderCommented:
Also note that you are using AutoGenerateColumns="False" for the gridview and using BoundColumns with names 01, 02, 03, etc. The column names you return in your stored proc should be same as these.
0
Harish VargheseProject LeaderCommented:
Here is a good article with source code that demonstrates N-Tier architecture:
http://www.codeproject.com/Articles/439688/Creating-ASP-NET-application-with-n-tier-architect 

There are many more to know about separating out the layers, but this is a good start.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you very much.
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.