调用存储过程输出参数的问题
最近在学分页存储过程,在调用的时候出现这样的问题:未将对象引用设置到对象的实例。搞了很久都没搞清楚,所以请大家帮帮忙
下面是代码:
protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("MaxTest", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PageNum", SqlDbType.Int);
cmd.Parameters.Add("@PageSize", SqlDbType.Int);
cmd.Parameters.Add("@countPage", SqlDbType.Int);
cmd.Parameters["@PageSize"].Value = txtPageSize.Text;
cmd.Parameters["@PageNum"].Value = txtPageNum.Text;
cmd.Parameters["@countPage"].Direction = ParameterDirection.Output;
con.Open();
GridView1.DataSource = cmd.ExecuteReader();
lbltotal.Text=cmd.Parameters["@countPage"].Value.ToString();
GridView1.DataBind();
con.Close();
}
如果注释这句lbltotal.Text=cmd.Parameters["@countPage"].Value.ToString();
就没问题
这里存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[MaxTest]
@PageNum int=10,
@PageSize int=0,
@countPage int output
as
declare @strSql nvarchar(1000)
declare @strto nvarchar(200)
set @strto='select @countPage=count(*) from testTable'
exec sp_executesql @strto,N'@countPage int out',@countPage out
set @strSql='select top '+cast(@PageNum as varchar(20))+ ' * from testTable where
(id>(select MAx(id) from (select top '+cast(@PageNum*@PageSize as varchar(20))+' id from TestTable order by
id) as t))'
exec sp_executesql @strSql