Solved

Get count on filtered rows

Posted on 2013-06-06
5
449 Views
Last Modified: 2013-06-10
Hi
I apply several filters to a datatable, then bind a datasource to it. This is a list of Policy renewals.
So my current code looks like
' stFilter contains all the filters
Me.BroomsDataset.Renewals.DefaultView.RowFilter = stFilter
' Set the sorting, then bind
Me.BroomsDataset.Renewals.DefaultView.Sort = "Rdat"
' Filters only apply when datasouce set
Me.RenewalsBindingSource.DataSource = Me.BroomsDataset.Renewals.DefaultView

Open in new window


Now I have to do one additional step. Add an extra column which counts the number of policies for a Client. Which I can do like this
For Each rowRenewal In Me.BroomsDataset.Renewals
     stCalc = "Count(PolicyRef)"
     stPolFilter = "ClientRef = '" & rowRenewal.ClientRef & "'"
     rowRenewal.PolicyCount = Me.BroomsDataset.Renewals.Compute(stCalc,stPolFilter)
Next
Me.BroomsDataset.Renewals.AcceptChanges()

Open in new window


However, this count will include all policies for a Client - I only want to count the filtered policies. For example, Client has 5 policies overall, 2 are in March. The filter is for March policies only, so only those two rows will be in RenewalsBindingSource.DataSource.
I'd like the PolicyCount to be 2 in that case, instead its 5.
What should I change to count only the filtered rows?
Thanks
0
Comment
Question by:jdhackett
[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
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:adriankohws
ID: 39225691
Why not get it from the database what you what before you bind to your dataset, then it's easier. For example:

SELECT Policy_Holder_ID, Policy_Name, Policy_Date,
(COUNT(*) OVER (PARTITION BY (SELECT 1)))AS Found_Policies
FROM YOURTABLE WHERE
Your conidtion here
0
 
LVL 1

Author Comment

by:jdhackett
ID: 39226118
Not all the data is in SQL, so your idea will not work in this case.
What I want is to work with the dataset only, not changing how the underlying data is loaded.
0
 
LVL 10

Expert Comment

by:adriankohws
ID: 39228845
Yes. If you keep manipulating with the binding source and not updating into the database, of course not all data is there. Till today, I am just extremely curious on why lots of people are holding big chunk of binding source data without updating to the database. In event of many concurrent users using the system, might meet data concurrency issues.
0
 
LVL 18

Accepted Solution

by:
vasto earned 500 total points
ID: 39229136
Try this:

Dim dt as System.Data.DataTable = Me.BroomsDataset.Renewals.DefaultView.ToTable()

For Each rowRenewal In dt
     stCalc = "Count(PolicyRef)"
     stPolFilter = "ClientRef = '" & rowRenewal.ClientRef & "'"
     rowRenewal.PolicyCount = dt.Compute(stCalc,stPolFilter)
Next
dt.AcceptChanges()

I cannot check the syntax
0
 
LVL 1

Author Comment

by:jdhackett
ID: 39234215
I also discovered that setting the RowStateFilter will also work. Just run this line before applying the Filter.

Me.ArachasBrooms01DataSet.Renewals.DefaultView.RowStateFilter = DataViewRowState.CurrentRows
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

696 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