SQL Stored Procedure Output C#

I have a stored procedure that perform a query, and outputs a COUNT of the records.

When I run it in SQL Server Management Console; I get a @totalRows value of 32,545 count.

BUT, when I execute this in Asp.net C#, the value of @totalRows always comes back as 2, not 32,545...

Am I doing something wrong?
USE [database]
GO

DECLARE	@return_value int,
		@totalRows int

EXEC	@return_value = sp_PagingNewPubs
		@startRowIndex = 1,
		@maxRows = 6,
		@totalRows = @totalRows OUTPUT

SELECT	@totalRows as N'@totalRows'

C# Code
======
        SqlConnection sqlConn = new SqlConnection();
        sqlConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString.ToString();

        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.Connection = sqlConn;
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.CommandText = "sp_PagingNewPubs";
        sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int).Value = GridPubs.PageIndex;
        sqlCmd.Parameters.Add("@maxRows", SqlDbType.Int).Value = GridPubs.PageSize;
        sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int).Value = ParameterDirection.Output;

        SqlDataAdapter sqlDad = new SqlDataAdapter();
        sqlDad.SelectCommand = sqlCmd;

        DataTable sqlDt = new DataTable();

        sqlDad.Fill(sqlDt);

        int totalPages = (int)sqlCmd.Parameters["@totalRows"].Value;

Open in new window

adworldmediaCTOAsked:
Who is Participating?
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, try this:
SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString.ToString();

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "sp_PagingNewPubs";
sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int).Value = GridPubs.PageIndex;
sqlCmd.Parameters.Add("@maxRows", SqlDbType.Int).Value = GridPubs.PageSize;
SqlParameter paramTotalRows = sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int);
paramTotalRows.Direction = ParameterDirection.Output; 

SqlDataAdapter sqlDad = new SqlDataAdapter();
sqlDad.SelectCommand = sqlCmd;

DataTable sqlDt = new DataTable();

sqlDad.Fill(sqlDt);

int totalPages = Convert.ToInt32(paramTotalRows.Value);

Open in new window

0
 
Jesus RodriguezIT ManagerCommented:
Check this link http://www.sqlteam.com/article/stored-procedures-returning-data
 on the section for output variables and try to check you code to

EXEC      @return_value = sp_PagingNewPubs
            @startRowIndex = 1,
            @maxRows = 6,
            totalRows = @totalRows OUTPUT

SELECT      totalRows = N'@totalRows'
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Explaining it:

In your code your @totalRows parameter has the value of 2 because it is never filled by your sp (you never specify the direction of your parameter), instead it is filled with 2 when you do this:
sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int).Value = ParameterDirection.Output;

Open in new window

That is because the integer value of ParameterDirection.Output is 2, you need to specify the direcction of your parameter by using the Direction property of your SqlParameter class, as in the next code:
SqlParameter paramTotalRows = sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int);
paramTotalRows.Direction = ParameterDirection.Output;

Open in new window

Then, after execute your query, paramTotalRows.Value will contain the value assigned to @totalRows by your SP.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.