CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 Number of records for a Grid / Paging from a stored procedure

Print topic Send  topic

Author Message
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).
View profile  Send private message
andrewi

Posts: 152
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
View profile  Send private message
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.
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.