Solved

Get count on filtered rows

Posted on 2013-06-06
5
443 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
  • 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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 …
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

815 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

7 Experts available now in Live!

Get 1:1 Help Now