Please expert advice: the stored procedure of the paging does not get a return value, it is depressed for two days! ~

wzq0729

Location of stored procedure:
http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.html

My code:
SqlConnection cnn = new SqlConnection (ConfigurationSettings.AppSettings ["ConnectionString"]);
SqlCommand _comm = new SqlCommand ("proc_ListPageInt", cnn);
_comm.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = null;
_comm.Parameters.Add ("@ tblName", SqlDbType.NVarChar, 200);
_comm.Parameters ["@ tblName"]. Value = "info_public";
_comm.Parameters.Add ("@ fldName", SqlDbType.NVarChar, 500);
_comm.Parameters ["@ fldName"]. Value = "*";
_comm.Parameters.Add ("@ pageSize", SqlDbType.Int);
_comm.Parameters ["@ pageSize"]. Value = 5;
_comm.Parameters.Add ("@ page", SqlDbType.Int);
_comm.Parameters ["@ page"]. Value = 1;
_comm.Parameters.Add ("@ fldSort", SqlDbType.NVarChar, 200);
_comm.Parameters ["@ fldSort"]. Value = "infoid";
_comm.Parameters.Add ("@ Sort", SqlDbType.Bit);
_comm.Parameters ["@ Sort"]. Value = true;
_comm.Parameters.Add ("@ strCondition", SqlDbType.NVarChar, 1000);
_comm.Parameters ["@ strCondition"]. Value = "";
_comm.Parameters.Add ("@ ID", SqlDbType.NVarChar, 150);
_comm.Parameters ["@ ID"]. Value = "infoid";
_comm.Parameters.Add ("@ Counts", SqlDbType.Int, 0);
_comm.Parameters ["@ Counts"]. Direction = ParameterDirection.Output;
_comm.Parameters.Add ("@ pageCount", SqlDbType.Int, 0);
_comm.Parameters ["@ pageCount"]. Direction = ParameterDirection.Output;
cnn.Open ();
dr = _comm.ExecuteReader ();
//dr.Read();//There is no value if it is not the same!
string gdds = "ff";
gdds = (string) _comm.Parameters ["@ Counts"]. Value;
Response.Write (gdds); // Nothing is output on the web page!

dr has a value, but out int counts, out int pageCounts cannot take values

It's 80 points, all given! ~

ymooner

Not so serious, go to work tomorrow to debug, there is no environment now.
If there is no problem in the execution of the query analyzer, it must be a problem when it is called.

wzq0729

See in profiler, run this stored procedure and get the result!

Profiler:
declare @ P1 int
set @ P1 = 19
declare @ P2 int
set @ P2 = 4
exec proc_ListPageInt @tblName = N 'info_public', @fldName = N '*', @pageSize = 5, @page = 1, @fldSort = N 'infoid', @Sort = 1, @strCondition = N '', @ID = N 'infoid', @Counts = @ P1 output, @pageCount = @ P2 output
select @ P1, @ P2

------------------------------------------
result:
    Table 1:
ID Title ...
----------------------------------------
1 kkkkkkk ....
..
..

  Table 2:
Unnamed column unnamed column
------------------------------------
  19 4

"Unnamed column" is the value of the output parameter!

huntereijij

Cannot use ExecuteReader

Only use ExecuteNonQuery

But it is troublesome to execute it twice (execute ExecuteNonQuery to return the output parameter value, execute ExecuteReader, and return SqlDataReader)

So instead of output parameters, I get a dataset,
There are two tables in this dataset. One is the parameter value you want to output.
One is the query records, which can be returned as many tables as needed.
For example: the statement in the stored procedure:
select 'aaa'
select 'bbb'
There are two tables in the dataset, which store the records 'aaa' and 'bbb'

wzq0729

ExecuteNonQuery, also used, no results

cpjn9810

ExecuteReader can be used, and the output parameters cannot be returned correctly because the data reader did not close to get the output parameters. It is recommended to output after the DataReader reads until the end of the data is closed.