asp.net stored procedure return value question

  • Thread starter Thread starter mazdotnet
  • Start date Start date
M

mazdotnet

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.
 
mazdotnet said:
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.
Click to expand...

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