We help IT Professionals succeed at work.

ADO Filtered Recordset to Crystal Reports Viewer

JayCrowe
JayCrowe asked
on
Medium Priority
919 Views
Last Modified: 2013-12-25
I am trying to filter a recordset and pass it to Crystal Reports.  However, Crystal still gets the full recordset...

Say rsCopy contains 100 records, then I apply a filter which takes rsCopy back to 25 records.  If I pass the recordset to Crystal, it has all 100 records.  How frustrating!  This seems like it should be so easy.

rsCopy.Filter = "sdtext LIKE '*" & strFilter & "*'"
CrxReport.Database.SetDataSource rsCopy

I have even tried making a new recordset based on the returned filter, such as:

Dim rsTemp as adodb.Recordset
rsCopy.Filter = "sdtext LIKE '*" & strFilter & "*'"
Set rsTemp = rsCopy
CrxReport.Database.SetDataSource rsTemp

And it still returns the unfiltered recordset...  UGH!

What am I doing wrong?

VB6 SP5, CR10, ADO 2.5

Thanks.
Comment
Watch Question

TimCotteeHead of Software Services

Commented:
Hi JayCrowe,

Nothing, however the .filter doesn't get passed to CR in any fashion. You either have to apply the filter as part of your where clause on generating the recordset or specify a filter within crystal seperately.

Tim Cottee
Brainbench MVP for Visual Basic
http://www.brainbench.com

Author

Commented:
Maybe I could create a new recordset in VB memory and only ADD the records from the filtered recordset.  Tedious, but it may work.  I'll give that a try.

I am aware I can enter the criteria up-front during my SQL SELECT, but it's a little more complicated than that... Long story, but my select statement is longer than my arm already, plus the fact what I am really trying to do is run a LIKE string search against an Oracle 8i LONG field, which is not supported directly.  It is a long-description field (like a memo field) for our Maximo CMMS system.

Not pretty no matter how I look at it.

I could pass the criteria field to Crystal, but I would like to have the filtered recordset in VB memory so I could export, print, etc, outside of Crystal.  I guess I could still run the filter in VB, as well as inside the CR, I'm just trying to find the simplest answer.

Thanks for the suggestions.

Author

Commented:
Here is a solution...   This allows me to make a clone of the filtered recordset which contains OLNY the filtered recordset items:

Public Function FilterField(rsTemp As ADODB.Recordset, strFilter As String) As Boolean

If strFilter <> "" Then
    rsTemp.Filter = "sdtext LIKE '*" & strFilter & "*' OR ldtext LIKE '*" & strFilter & "*'"
    If Not rsTemp.EOF Then
        Dim oStream As ADODB.Stream
        Dim rsClone As ADODB.Recordset
        Set oStream = New ADODB.Stream
        rsTemp.Save oStream
        Set rsClone = New ADODB.Recordset
        rsClone.Open oStream, , , adLockUnspecified
        Set rsCopy = rsClone
        Set oStream = Nothing
        Set rsClone = Nothing
        FilterField = True
    End If
Else
    FilterField = True
End If

End Function

I return True/False so I can decide if any records are returned.

Here is how I call it:

If FilterField(rsCopy, txtContains.Text) = False Then GoTo NoResults

CrxReport.Database.SetDataSource rsCopy



How do I accept my own answer as the solution?

:o)

Jay
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.