NickL
Posts: 2
|
| Posted: 12/17/2012, 6:31 AM |
|
ASP.NET VB / MS SQL
When using a stored procedure on a grid, you normally get -1 as the number of records returned and also the page navigation goes crazy as it does not know the number of records to work out the number of pages.
I have worked out a way round this and thought it might help someone.
I set the RecordCount and PagesCount to the correct number in the code after the stored procedure has been called, which is then used to correctly display the number of records and helps the page numbering be correct.
To Start with, in your MS SQL stored procedure, at the bottom include a RETURN @@RowCount
Now, by default, when using a stored procedure as the data source, normally code charge adds @RETURN_VALUE as a URL value at the top of the procedure.
Leave this in to pickup the number of records returned value from the stored procedure.
Go to the grid, on the Property / Events, add an "After Execute Select" event of "Add Code", OR my preferable way to keep track of small amounts of code, 3 * "Add Action" of "Retrieve Value For Variable" events
For the "Add Code" event, ecopy and pase in the following code.
m_recordCount = (UrlRETURN_VALUE.value)
if m_recordCount > 0 then _PagesCount = (m_recordCount \ RecordsPerPage)
if m_recordCount > 0 and m_recordCount Mod RecordsPerPage > 0 then _PagesCount = (_PagesCount + 1)
But my preferred way is to add 3 "Retrieve Value For Variable" events , this keeps it a lot more visible for me.
In the first "Retrieve Value For Variable" event
Variable Name >> m_recordCount
Source Type >> Expression
Source Name >> UrlRETURN_VALUE.value
In the Second "Retrieve Value For Variable" event
Variable Name >> if m_recordCount > 0 then _PagesCount
Source Type >> Expression
Source Name >> m_recordCount \ RecordsPerPage
In the Third "Retrieve Value For Variable" event
Variable Name >> if m_recordCount > 0 and m_recordCount Mod RecordsPerPage > 0 then _PagesCount
Source Type >> Expression
Source Name >>_PagesCount + 1
It creates exactly the same code as the Add Code above but keeps it more visible.
So, now the number of records has been correctly set, then number of pages based on the current page size and number of records has also been set.
All will look and work a lot better than before.
I hope this helps you (plus my trick of using the events might be of interest).
|
 |
 |
andrewi
Posts: 162
|
| Posted: 12/17/2012, 1:51 PM |
|
That's excellent. I'd become resigned to disabling paging and sorting when using stored procedures. I'll try this next time.
Does the paging work with just the code shown above, or do you pass the RecordsPerPage and a current-page values as parameters for the SP?
Andrew
|
|
|
|
NickL
Posts: 2
|
| Posted: 12/17/2012, 4:57 PM |
|
The paging and record count are set within the object before it is used, so as far as I can see, its thinks it has worked as normal.
The sorting is still a pain/problem, but was considering passing it into the proc at some point.
|
|
|
|
|
