marcwolf
Posts: 361
|
| Posted: 05/04/2005, 7:25 PM |
|
Hi All..
I had a problem that I solved and I think others have the same issue.
Often when you have a very complex SQL statement with a lot of joins that is displayed on a page with a Navigator control - you will find that there is a difference between the number of items AND what CCS thinks is the number of records.
I.e. - my SQL when run in a SQL editor brings back 14 records, BUT CCS insists that there are 16 records. And thats a problem when the number of items on a page is set at 15. One page too many on the Navigator.
Now - one can modify the CountSQL property in the BeforeBuildSelect area
Dim SQL
sSQL = Replace product.datasource.Countsql, "SELECT", "SELECT DISTINCT")
product.datasource.Countsql = sSQL
to have a distinct inserted into it but often with these queries a mere Count(*) will NOT work!!!.
After much pulling of hair and digging through the CCS code I found the solution.
We know that there are 14 records returned. We can run the SQL in an independant (SqlYog for MySQL) app to see this.
So how do we get CCS to use the correct number.
The trick is in the AfterExecuteSelect. Here is my snippet and I wil explain
Dim iloop
iLoop=0
product.datasource.recordset.recordset.movefirst
do while not product.datasource.recordset.eof()
iloop = iloop + 1
product.datasource.recordset.recordset.movenext
loop
product.datasource.recordset.recordset.movefirst
product.datasource.recordset.recordcount = iloop
What am I doing.
Firstly I am creating an empty loop variable, then moving the TRUE recordset (Not the CCS Datasource class one) to the start.
Next I loop throught doing a count until I hit the end.
Move it back to the beginning again so that CCS can work with it later
and then....
Setting the CCS DataSource class's recordcount to the correct value.
From here the Navigator will work correctly as well as any other reference to the number of records.
It is often a lot of fun digging throught all of the object references to get to the real one but it is worth it in the end.
Have fun and hope this helps
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
