Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 27th, 2005, 04:09 AM
Registered User
Join Date: Aug 2005
Location: Hull, , United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mcloum
Default Filter Help

Hi all,

First time posting here. Been using ASP for about a year but never really got to far into coding. I only learnt enough to get my site up and running as i was on a tight deadline. Im now upgrading the site and need a little help with the "Filte" properties of a RecordSet.

Im trying to group together similar records. Its basically a discography of albums that a band has released. I have been reading the code on chapter 13 page 549 which i could adapt to do what i want it to do but im getting stuck.

Here is the code i have.


<link rel="stylesheet" href="includes/style.css" type="text/css">

<body bgcolor="#333333">

    Dim strSQL, ID
    strSQL = "SELECT Albums.AlbumsID AS Albums_AlbumsID, Albums.Name AS Albums_Name, Tracks.TracksID, Tracks.AlbumsID AS Tracks_AlbumsID, Tracks.LyricsID, Tracks.TabID, Tracks.Name AS Tracks_Name, Tracks.TrackNumber FROM Albums INNER JOIN Tracks ON Albums.AlbumsID = Tracks.AlbumsID;"

            'Debug lines

            'Open DB connection
             objRS.Open strSQL, objConn, adOpenStatic, AdLockReadOnly, adCmdTable
            objRS.Filter = objRS("Albums_Name")

            Dim Count 'We'll use this to limit the number of records displayed on a page
            Count = 1

                        Response.Write "<table border=1 cellpadding=0>"
                        Do While Not objRS.EOF
                                With Response
                                    .Write "<tr class=""body"">"
                                    .Write "<td>"
                                    .Write objRS("Albums_Name")
                                    .Write "</td><td>"
                                    .Write objRS("Tracks_Name")
                                    .Write "</td><td>"
                                    .Write "</td></tr>"
                            End With
                            count = count + 1
                    Response.Write "<tr><td align=center>"
                    Response.Write "</td></tr></table>"
                    Response.Write "<center>"

            Response.Write "</center>"
Set objRS = Nothing
When i run the page a i get the following error message.
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/beta/lyrics_iframe.asp, line 22
Can someone explain where im going wrong? i've been at this for a few hours and i think i need a fresh pair of eyes.

Many thanks


  #2 (permalink)  
Old November 7th, 2005, 02:09 AM
Authorized User
Join Date: Sep 2003
Location: Melbourne, Victoria, Australia.
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts

Not sure of your problem, but I would slim the code down to its basic for just listing one record and do away with the locks.
Then clear out all the stuff after " Dim strSQL, ID
    strSQL = "SELECT Albums.AlbumsID AS Albums_AlbumsID, Albums.Name AS Albums_Name;"
Then when you find something that works just add bits back untill it fails

Its what I do on occasions

  #3 (permalink)  
Old November 10th, 2005, 05:40 PM
Friend of Wrox
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts

OK - here's the problem, your bit of code on line 22:
objRS.Filter = objRS("Albums_Name")
is invalid because the .Filter method requires an actual filter, such as
objRs.Filter = "Albums_Name='SomeName'"
And now, a word on this from Microsoft themselves:
quote:Settings and Return Values

Sets or returns a Variant value, which can contain one of the following:

Criteria string — a string made up of one or more individual clauses concatenated with AND or OR operators.

Array of bookmarks — an array of unique bookmark values that point to records in the Recordset object.

A FilterGroupEnum value.

Use the Filter property to selectively screen out records in a Recordset object. The filtered Recordset becomes the current cursor. Other properties that return values based on the current cursor are affected, such as AbsolutePosition, AbsolutePage, RecordCount, and PageCount. This is because setting the Filter property to a specific value will move the current record to the first record that satisfies the new value.

The criteria string is made up of clauses in the form FieldName-Operator-Value (for example, "LastName = 'Smith'"). You can create compound clauses by concatenating individual clauses with AND (for example, "LastName = 'Smith' AND FirstName = 'John'") or OR (for example, "LastName = 'Smith' OR LastName = 'Jones'"). Use the following guidelines for criteria strings:

FieldName must be a valid field name from the Recordset. If the field name contains spaces, you must enclose the name in square brackets.

Operator must be one of the following: <, >, <=, >=, <>, =, or LIKE.

Value is the value with which you will compare the field values (for example, 'Smith', #8/24/95#, 12.345, or $50.00). Use single quotes with strings and pound signs (#) with dates. For numbers, you can use decimal points, dollar signs, and scientific notation. If Operator is LIKE, Value can use wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string. Value cannot be null.
Note To include single quotation marks (') in the filter Value, use two single quotation marks to represent one. For example, to filter on O'Malley, the criteria string should be "col1 = 'O''Malley'". To include single quotation marks at both the beginning and the end of the filter value, enclose the string with pound signs (#). For example, to filter on '1', the criteria string should be "col1 = #'1'#".

There is no precedence between AND and OR. Clauses can be grouped within parentheses. However, you cannot group clauses joined by an OR and then join the group to another clause with an AND, like this:
(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

Instead, you would construct this filter as
(LastName = 'Smith' AND FirstName = 'John') OR (LastName = 'Jones' AND FirstName = 'John')

In a LIKE clause, you can use a wildcard at the beginning and end of the pattern (for example, LastName Like '*mit*'), or only at the end of the pattern (for example, LastName Like 'Smit*').
The filter constants make it easier to resolve individual record conflicts during batch update mode by allowing you to view, for example, only those records that were affected during the last UpdateBatch method call.

Setting the Filter property itself may fail because of a conflict with the underlying data (for example, a record has already been deleted by another user). In such a case, the provider returns warnings to the Errors collection but does not halt program execution. A run-time error occurs only if there are conflicts on all the requested records. Use the Status property to locate records with conflicts.

Setting the Filter property to a zero-length string ("") has the same effect as using the adFilterNone constant.

Whenever the Filter property is set, the current record position moves to the first record in the filtered subset of records in the Recordset. Similarly, when the Filter property is cleared, the current record position moves to the first record in the Recordset.

See the Bookmark property for an explanation of bookmark values from which you can build an array to use with the Filter property.
I am a loud man with a very large hat. This means I am in charge

Similar Threads
Thread Thread Starter Forum Replies Last Post
Matrix filter jpenn Reporting Services 0 August 10th, 2006 03:46 PM
Filter Help mcloum BOOK: Beginning ASP 3.0 1 August 26th, 2005 03:10 PM
ListBox Filter dgold Access 3 March 30th, 2005 06:00 PM
How to Filter Cr 9.0 abdusalam Crystal Reports 0 June 25th, 2004 02:04 AM
How to filter data? coolest_pie SQL Server 2000 7 May 7th, 2004 07:00 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.