Clarification on DataReader

I want to create a DataList that shows products, which will be onmultiple pages. I have my stored proc to show paged results, whichcontains a return value for more records.
I have found examples of coding the DataReader, defining all theparameters etc, but what about the drag and drop SqlDataSource?? Youcan select the DataSource Mode to be "DataReader". I can put selectparameters in, with input and my return value. I don't know how to thenaccess the return value, or output value if needed, from this? MyDataList references the SqlDataSource, but I don't know how to get thereturn/output value out? This is very frustrating, cause I can't findany info about it anywhere. Always input parameters, but no output.
This is my current SqlDataSource...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"DataSourceMode="DataReader" ConnectionString="<%$ConnectionStrings:Personal %>" SelectCommand="sp_PagedItems"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="Page" QueryStringField="page" />
<asp:Parameter Name="RecsPerPage" DefaultValue="10" />
<asp:QueryStringParameter Name="CategoryID" QueryStringField="cat"/>
<asp:Parameter Name="RETURN_VALUE" Direction="ReturnValue" Size="1"/>
</SelectParameters>
</asp:SqlDataSource
If I take out the RETURN_VALUE Parameter, my results display in my datalist, but that's useless if I can't access the return value todetermine the remaining number of pages etc. Is my RETURN_VALUEparameter wrong? How do I access that? My stored proc is shown below...

CREATE PROCEDURE sp_PagedItems
(
@.Page int,
@.RecsPerPage int,
@.CategoryID int
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
No varchar(100),
Name varchar(100),
SDescription varchar(500),
Size varchar(10),
ImageURL varchar(100)
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (No, Name, SDescription, Size, ImageURL)
SELECT No, Name, SDescription, Size, ImageURL FROM Products WHERE CategoryID=@.CategoryID

-- Find out the first and last record we want
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @.LastRec
)
FROM #TempItems
WHERE ID > @.FirstRec AND ID < @.LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

Hi Micky,

I have a sample that you might want to check out on my blog here:http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

It shows how to build a paged datalist control that provides a product category listing in a very efficient way.

Hope this helps,

Scott

|||Scott,
Thanks for the reply, and I did find your blog, and it works perfect, however...
I don't have SQL 2005, i have SQL Server 2000. That's why I'm trying tofind a solution, or a blog or tutorial that would deal with that.
If i have to code the parameters in code-behind, then that's what I'lldo, but I'm trying to figure out why the SqlDataSource has the featureof DataReader, and putting in parameters, if I don't know how to usethem. No literature so far has shown me how it works. Everyone showsexamples of hand coding the parameters, but like I said, why would thedatasource have then available?|||

Hi Micky,

You can use the Selecting and Selected events on the SQlDataSource to write code that fires before and immediately after the Select method is called on the SqlDataSource. You can then use the eventArgs parameter to these events to either put parameters into the SqlDataSource (before the database is called), or to retrieve the output parameters (after it is called).

Hope this helps,

Scott