Solved

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

Posted on 2014-07-29
6
3,453 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

757 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

24 Experts available now in Live!

Get 1:1 Help Now