asp.net stored procedure return value question

Hi guys,

I can't figure out why this is not working? I need to display all the
rows for a given query for a given page index (ex. row 10..20) and the
total number of rows. I got the first part to work. However, when I
added the return parameter value, the first part is empty but I do get
total number of rows.

da.SelectCommand.CommandText = "CommentsByPaging";
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("PageIndex", PageIndex);
da.SelectCommand.Parameters.AddWithValue("PageSize", PageSize);

da.SelectCommand.Parameters.Add("@CommentCount", SqlDbType.Int);
da.SelectCommand.Parameters["@CommentCount"].Direction =
ParameterDirection.Output;

It's suppose to get comments for a given page index and the total
number of comments available. I got the comments for a given page
index to work, however now that I've added 'CommentCount', this total
number of comments works but my dataset.tables.count is empty? Any
idea what I'm doing wrong.

Stored procedure..
ALTER PROCEDURE [dbo].[CommentsByPaging]
@PageIndex INT,
@PageSize INT,
@CommentCount INT OUTPUT

I have select for return rows x..n rows
and another select that
SELECT @CommentCount = (SELECT COUNT(CommentId) FROM Comments) that
returns the total rows

Thank you
Maz.

Re: asp.net stored procedure return value question

mazdotnet wrote:
> Hi guys,
>
> I can't figure out why this is not working? I need to display all the
> rows for a given query for a given page index (ex. row 10..20) and the
> total number of rows. I got the first part to work. However, when I
> added the return parameter value, the first part is empty but I do get
> total number of rows.
>
> da.SelectCommand.CommandText = "CommentsByPaging";
> da.SelectCommand.CommandType = CommandType.StoredProcedure;
> da.SelectCommand.Parameters.AddWithValue("PageIndex", PageIndex);
> da.SelectCommand.Parameters.AddWithValue("PageSize", PageSize);
>
> da.SelectCommand.Parameters.Add("@CommentCount", SqlDbType.Int);
> da.SelectCommand.Parameters["@CommentCount"].Direction =
> ParameterDirection.Output;
>
> It's suppose to get comments for a given page index and the total
> number of comments available. I got the comments for a given page
> index to work, however now that I've added 'CommentCount', this total
> number of comments works but my dataset.tables.count is empty? Any
> idea what I'm doing wrong.
>
> Stored procedure..
> ALTER PROCEDURE [dbo].[CommentsByPaging]
> @PageIndex INT,
> @PageSize INT,
> @CommentCount INT OUTPUT
>
> I have select for return rows x..n rows
> and another select that
> SELECT @CommentCount = (SELECT COUNT(CommentId) FROM Comments) that
> returns the total rows
>
> Thank you
> Maz.
>

You have to read all the rows from the data reader before you can read
the value of the output parameter.

--
Göran Andersson
_____
http://www.guffa.com