?
Solved

GridView aggregate column

Posted on 2013-01-29
14
Medium Priority
?
373 Views
Last Modified: 2013-05-01
How do you fill a GridView column with an aggregate?  i.e. SELECT COUNT(1) FROM AnyOtherTable WHERE PrimaryKeyID = ForeignKeyID_FromThisGridView
0
Comment
Question by:megnin
[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
14 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38833755
Are you filling your gridview from the database? Add a join to that table in your stored procedure or query and do the aggregate on the db side.
0
 
LVL 10

Expert Comment

by:Monica P
ID: 38834002
can u make your question little bit elaborate
0
 
LVL 1

Author Comment

by:megnin
ID: 38834892
Sorry.  I usually don't get any responses at all when I make the question fully explanitory.

Here goes:
I'm trying to display the COUNT from SQL in a TextBox of a GridView based on the Selected Value of a DropDownList on the same row.

This all works fine on the row I'm currently making a selection from the DropDownList.
"ID", "Sites" and "Load" fields all display the correct value and aggregate values.

But, when I go to a different row to make a selection all of the fields become blank except for the row I'm currently selecting a value on.

The database updates are all good.  I just need to display the values on all the rows not just one at a time.  I don't know how to pull aggregate values from the database into the GridView.  Or, values based on a Parameter (the DDL SelectedValue).

.aspx GridView markup (DropDownList and TextBox):
<asp:TemplateField HeaderText="Monitor DropDownList">
    <ItemTemplate>
        <asp:DropDownList ID="ddlMonitors" runat="server" AutoPostBack="True" DataSourceID="SqlDataSourceMonitors" 
            DataTextField="Name" DataValueField="keyMonitorID" OnSelectedIndexChanged="ddlMonitors_SelectedIndexChanged" AppendDataBoundItems="true">
            <asp:ListItem Text="" Value=""></asp:ListItem>
        </asp:DropDownList>
        <asp:SqlDataSource ID="SqlDataSourceMonitors" runat="server" ConnectionString="<%$ ConnectionStrings:SYEP2012ConnectionString %>"
            SelectCommand="SELECT [LastName] + ', ' + [FirstName] + ' - ' + [City] + ' - ' + ISNULL([Zip],'00000') + ' - ' + [HireStatus] AS [Name], [keyMonitorID] FROM [Monitors] ORDER BY [HireStatus], [City], [LastName], [FirstName]">
        </asp:SqlDataSource>
    </ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Sites">
    <ItemTemplate>
        <asp:TextBox ID="txtSites" runat="server" Width="40px" Wrap="False"></asp:TextBox>
    </ItemTemplate>
</asp:TemplateField>

Open in new window

.vb DropDownList_SelectedIndexChanged:
Protected Sub ddlMonitors_SelectedIndexChanged(sender As Object, e As EventArgs)
    Dim ddlMonitors As DropDownList = DirectCast(sender, DropDownList)
    Dim row As GridViewRow = DirectCast(ddlMonitors.NamingContainer, GridViewRow)

    Dim Worksite As String = row.Cells(4).Text
    Dim Monitor As String = ddlMonitors.SelectedValue

    Dim query As String = "UPDATE Worksites SET keyMonitorID = @MonitorID WHERE keyWorksiteID = @WorksiteID; SELECT COUNT(1) FROM Worksites WHERE keyMonitorID = @MonitorID ; "

    Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("SYEP2012ConnectionString").ConnectionString)
    Dim com As New SqlCommand(query, con)

    com.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = Monitor
    com.Parameters.Add("@WorksiteID", SqlDbType.VarChar).Value = Worksite

    Try
        con.Open()
        Dim sites As String = com.ExecuteScalar
        row.Cells(10).Text = sites  'Count of Worksites where Selected Monitor is assigned.


        Dim DBCmd1 As New SqlCommand 'Load
        Dim DBCmd2 As New SqlCommand 'City

        DBCmd1 = New SqlCommand("SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM Worksites WHERE keyMonitorID = @MonitorID)", con)
        DBCmd1.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = Monitor
        Dim Load As Integer = DBCmd1.ExecuteScalar
        row.Cells(11).Text = Load

        DBCmd2 = New SqlCommand("SELECT City FROM Monitors WHERE keyMonitorID = @MonitorID", con)
        DBCmd2.Parameters.Add("@MonitorID", SqlDbType.VarChar).Value = Monitor
        Dim City As String = DBCmd2.ExecuteScalar
        row.Cells(12).Text = City

        con.Close()

    Catch exp As Exception
        Response.Write(exp)
        'Server.Transfer("Error.htm")
    End Try

    row.Cells(9).Text = Monitor

End Sub

Open in new window

0
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 
LVL 1

Author Comment

by:megnin
ID: 38837551
I knew that would happen.  The details take too much time to read when you're tring to rack up the points on the simple questions.

TheLearnedOne, Kaufmed and CodeCruiser used to help me out a lot.  For the past year or so, the point competition has become more important than helping other members so I'm lucky if I get someone asking me if I've tried Googling for the solution.

Thanks anyway.
0
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 1000 total points
ID: 38839496
You need to find the current row index to only change the row that you have selected. Currently you are changing all of the rows because each row has these values.

Dim row As GridViewRow = DirectCast(ddlMonitors.NamingContainer, GridViewRow)
Dim Worksite As String = row.Cells(4).Text

It probably should look something like this:
 Dim ddlMonitors As DropDownList = DirectCast(sender, DropDownList)
 Dim row As GridViewRow = DirectCast(ddlMonitors .Parent.Parent, GridViewRow)
 Dim idx As Integer = row.RowIndex

http://forums.asp.net/t/1426907.aspx/1

FYI, any time that you don't think that you are getting the attention that you need (or deserve), there is a Request Attention button under the original post. A moderator will see to it that other people are contacted and it may speed up the progress. Comments about the lack of progress or participation isn't the best way to get faster help.
0
 
LVL 1

Author Comment

by:megnin
ID: 38839595
I was just having a bad day and venting.  I have used the "Request Attention" button and the moderators are very helpful in bringing the question to the attention of some very helpful Experts.  Up until a year or so ago, I never had to request attention, though.

Anyway...

I don't think I'm changing all of the rows as you suggest.  As I select items from a DropDownList on each row, The DropDownLists selected items are different, depending on what I selected and the calculated totals show correctly ***on that row*** but as soon as I make a selection on a different row, all the calculated total TextBoxes go blank except for the one on the row I'm currently making a selection.
0
 
LVL 1

Author Comment

by:megnin
ID: 38839836
Here is a little screen capture video showing what's happening now.

What I need is to populate all the rows from the database tables on page load and to be updated when a new selection is made from the DDLs.

The problem is the values are aggregates, not coming strait from a table field.

This is what populates the "Load" field in the GridView (seen in code above):
SELECT COUNT(1) FROM [Applicants] WHERE keyWorksiteID IN (SELECT keyWorksiteID FROM Worksites WHERE keyMonitorID = @MonitorID

Open in new window

Sequence-01-2.flv
0
 
LVL 1

Author Comment

by:megnin
ID: 38860506
Thank you!  :)
0
 
LVL 1

Author Comment

by:megnin
ID: 38860704
The one remaining unsolved issue I have with my application is the GridView only displays the values for the row where a selection was just made in the DropDownList.  The rest of the rows are blanked out upon postback.  Please see the screen capture video a couple of comments up.

All the database updates seem to be working fine.  I just need to update/populate the entire GridView when a selection is made.
0
 
LVL 12

Accepted Solution

by:
jagssidurala earned 1000 total points
ID: 38872911
In Row databound event of gridview, you have to retrive all the values and assign it based on ddl selected value so that the state will remains same.

refer below links for rowdatabound event of gridview

http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/c2763efd-b260-421b-bf7b-786893accbcd

http://asp-net-example.blogspot.in/2008/11/aspnet-gridview-rowdatabound-event.html

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowdatabound.aspx
0
 
LVL 1

Author Comment

by:megnin
ID: 38876274
Thanks, jagssidurala.  I'll check those out today.
0
 
LVL 1

Author Comment

by:megnin
ID: 39129501
I've requested that this question be closed as follows:

Accepted answer: 250 points for jagssidurala's comment #a38872911
Assisted answer: 250 points for jmiller1979's comment #a38839496
Assisted answer: 0 points for megnin's comment #a38876274

for the following reason:

Thank you for the help.  I'm sorry I forgot about this question for so long.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
Suggested Courses

764 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