Solved

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

Posted on 2014-07-29
6
3,878 Views
Last Modified: 2014-07-30
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

0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 40228555
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40228694
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
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228713
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228715
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
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228739
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
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40229528
Thank you very much.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question