Solved

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

Posted on 2014-07-29
6
3,558 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
  • 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 33

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 33

Author Closing Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

937 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now