David Megnin
asked on
GridView aggregate column
How do you fill a GridView column with an aggregate? i.e. SELECT COUNT(1) FROM AnyOtherTable WHERE PrimaryKeyID = ForeignKeyID_FromThisGridV iew
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.
can u make your question little bit elaborate
ASKER
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):
Changed:
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>
.vb DropDownList_SelectedIndexProtected 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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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):
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
Sequence-01-2.flv
ASKER
Thank you! :)
ASKER
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.
All the database updates seem to be working fine. I just need to update/populate the entire GridView when a selection is made.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, jagssidurala. I'll check those out today.
ASKER
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.
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.