sSQL="SELECT " _
& "Job.JobID, Job.ProjectID , " _
& "Project.PProject AS Project, " _
& "Job.JTitle AS JobTitle, " _
& "COUNT(Bid.JobID) AS NumBids, " _
& "Job.JobStatus AS JobStatus, " _
& "Job.JPostDate AS PostDate, " _
& "Job.JDueDate AS DueDate, " _
& "Job.JModifyDate AS LastModified, " _
& "Project.UserID " _
& "FROM ((Project RIGHT OUTER JOIN Job ON Project.ProjectID = Job.ProjectID) " _
& "LEFT OUTER JOIN Bid ON Job.JobID = Bid.JobID) " _
& "WHERE (Job.UserID = 12345) " _
& "GROUP BY Job.JobID, Job.ProjectID, Job.JTitle, " _
& "Job.JobStatus, Job.JPostDate, Job.JDueDate, " _
& "Job.JModifyDate, Job.ProjectID, Project.PProject, Project.UserID"
This example will properly display the records requested, but when you choose
the Filter function, it will fail if you try and filter on the “COUNT(Bid.JobID)”
field. This is because ASPdb adds the criteria you specify to the end of the
existing SQL statement as a “WHERE” condition. This creates an aggregate
function COUNT(Bid.JobID) in the WHERE criteria which is illegal in both ASP and
in an MS Access query. It will also fail in a pure asp file because the source
string exceeds 255 characters. The solution is two part:
First, assign an alias to all the fields with a formula or function (such as this COUNT function) and set the 4th parameter of the dbNameMap property in order for the filter to work correctly. The corresponding dbNameMap property for the above source string is as follow:
X.dbNameMap="" _
& "Project,Project,,Project.PProject; " _
& "JobTitle,Job Title,,Job.JTitle; " _
& "NumBids,Num Bids,,COUNT(Bid.JobID); " _
& "JobStatus,Job Status,,Job.JobStatus; " _
& "PostDate,Post Date,,Job.JPostDate; " _
& "DueDate,Due Date,,Job.JDueDate; " _
&"LastModified,Last Modified,,Job.JModifyDate; "
Second, in order to make the filter work for NumBids=xxx, set the UseRSFilter to true in the dbOptions property:
X.dbFilterParams="UseRSFilter=true”
When this property is set, ASPdb uses the Recordset’s filter instead of using the WHERE criteria of the SQL statement. This avoids the aggregate function condition and solves the problem. Note: Only use this technique (UseRSFilter=true) when you’ll have an aggregate function in one of your fields. Otherwise, leave the default (false) value for higher performance and enhanced capability. For example, with UseRSFilter=true, you can’t search for a text field that contains an apostrophe (‘) such as LastName=”O’Connor”. This type of search works fine with UseRSFilter=false since ASPdb uses the more powerful “WHERE” condition to meet the criteria.
WARNING – STANDARD ASP’s RECORDSET FILTER DOES NOT WORK WITH INDEX.