Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get count on filtered rows

Posted on 2013-06-06
5
Medium Priority
?
455 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 2000 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

Implementing Azure Infrastructure Exam 70-533

This course is designed to familiarize and instruct students in the content that is covered by Microsoft Exam 70-533, Implementing Microsoft Azure Solutions. It focuses on all the November 2016 objective domain topics.

Question has a verified solution.

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

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 article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

721 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