PagedLocationList SP

Topics: Developer Discussion
Jul 13, 2007 at 6:03 PM
Edited Jul 13, 2007 at 6:25 PM
The current sp sorts locations by ID on the Locations List page. I modified the SP to sort by Title (Alphabetically). Makes them easier to find. I'd like to have the locations list sort alphabetically on the Event Edit Page as well, but can not figure out how to set the order by for the dropdown list.

Any ideas?

The SP for the first portion is below.

ALTER PROCEDURE PagedLocationsList
(
@pageNum INT = 1,
@pageSize INT = 10
)

AS
DECLARE @rows INT
DECLARE @keyid INT
DECLARE @rowCount FLOAT /* yes we need a float for the math */

IF @pageNum = 1
BEGIN
SET @keyid=0
END
ELSE
BEGIN
/* get the values for the date and row */
SELECT @rows = (@pageNum-1) * @pageSize
SET ROWCOUNT @rows
SELECT @keyid=id FROM locations ORDER BY id ASC
END

SELECT @rowCount=COUNT(*) FROM locations

SET ROWCOUNT @pageSize
SELECT id, title, description, linkURL, address, photo, directions
FROM Locations
WHERE id > @keyid
ORDER BY title ASC
RETURN CEILING(@rowCount/@pageSize)
Jul 13, 2007 at 6:30 PM
Found it. Locations_picker.ascx. Change the Data Source Connection String to be:

<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" ID="LocationList"
runat="server" SelectCommand="SELECT title, id, description, linkURL, directions, address, photo FROM Locations ORDER BY title">
</asp:SqlDataSource>