Solved

Get count on filtered rows

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Getting multiple values from a dataset ASP.NET(VB) 3 37
Tool Box 2 34
Not showing page correctly 3 30
VB.Net - Windows UserName index and length error 4 22
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

21 Experts available now in Live!

Get 1:1 Help Now