ekendricks
Posts: 34
|
| Posted: 07/25/2005, 8:24 PM |
|
Using MS Access and CCS
I'm trying to limit the number of records returned to a grid when page loads. Currently ALL records are are returned, and I need to limit it to 100. The code below returns no records and does not give an error.
SELECT Top 100 PatientID, LName, FName, MName, Address, City, Zip, PhysicianID, LastUpdated, StateAbbr
FROM Patient LEFT JOIN States ON
Patient.State = States.StateID
WHERE PatientID = {s_PatientID}
AND LName LIKE '%{s_LName}%'
AND FName LIKE '%{s_FName}%'
Thanks in advance
Ernest
|
 |
 |
peterr
Posts: 5971
|
| Posted: 07/25/2005, 8:29 PM |
|
Have you tried specifying this in grid's properties? There are "Record Per Page" and "Page Size Limit" properties that should take care of this.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Ernest
|
| Posted: 07/25/2005, 8:51 PM |
|
I have the Record Per Page set for 10 and Page Size Limit set for 10.
Currently I have 1689 records in the table, and all 1689 are returned. The page count show Page 1 or 167.
thanks
|
|
|
|
peterr
Posts: 5971
|
| Posted: 07/25/2005, 9:26 PM |
|
Since several things don't work, try rebuilding the page. Or delete the PageName.asp and CCS will regenerate it cleanly.
Also do not use "LIMIT" within the SQL because CCS automatically adds the LIMIT clause where needed. Finally, you may not need to use SQL at all, just the visual query builder and grid properties should work.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
donb
Posts: 52
|
| Posted: 07/26/2005, 5:36 AM |
|
Quote ekendricks:
Using MS Access and CCS
I'm trying to limit the number of records returned to a grid when page loads. Currently ALL records are are returned, and I need to limit it to 100. The code below returns no records and does not give an error.
SELECT Top 100 PatientID, LName, FName, MName, Address, City, Zip, PhysicianID, LastUpdated, StateAbbr
FROM Patient LEFT JOIN States ON
Patient.State = States.StateID
WHERE PatientID = {s_PatientID}
AND LName LIKE '%{s_LName}%'
AND FName LIKE '%{s_FName}%'
Thanks in advance
Ernest
Some ODBC drivers do not support 'TOP n'. This might be the problem.
_________________
http://www.gotodon.com/ccbth |
|
|
|
Ernest
|
| Posted: 07/28/2005, 7:34 AM |
|
I have re-generated the page using the visual query builder, set the record per page and page size limits, and still doesn't work. Using SQL returns no records. I have narrowed the problem down to the query parameter when using SQL. My first parameter is PatientID which is an integer. The builder requires a default value be entered. Set to "0" returns no records. Set to some value returns that record. If I delete the PatientID parameter, I get the desired top 100 records. My delima now is, how do I get around putting a required default value in the query parameter, or somehow set it to "Null".
Thanks
Ernest
|
|
|
|
peterr
Posts: 5971
|
| Posted: 07/28/2005, 11:55 AM |
|
You initially wrote that ALL records were returned but now no records are returned, therefore I have to assume that the problem is different now and not related to LIMIT.
The default value must be required. Otherwise your SQL statement wouldn't work and the program would error out. For example if you use "WHERE PatientID = {s_PatientID}" then a null parameter would execute such SQL: "WHERE PatientID = " , which of course cannot work. In your case you just need to create your SQL statement in such way that it doesn't use null/blank values.
For examples see:
http://forums.codecharge.com/posts.php?post_id=50005
http://forums.codecharge.com/posts.php?post_id=27344
http://forums.codecharge.com/posts.php?post_id=48740
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
