菜鸟遇到一个问题~
菜鸟遇到个问题 我写了个存储过程 分页的 目的是 从一个表 根据不同的分类来查询并且分页
前3个是分页的设置 第四个 是分类的 数据 第五个 是分类的子项 每次运行报错 无法将 'y'转换为int
ALTER procedure [dbo].[fyinnovationbyzitem](@pagesize int,@currpage int,@totalrecords int output,@zid int,@zitem char)
as
begin
declare @total int;
set @total = (select COUNT(*) from Innovation where Cast(@zitem as nvarchar(50)) = @zid);
set @totalrecords = @total;
select top(@pagesize) * from Innovation where Id not in (select top((@currpage - 1)*@pagesize) Id from Innovation where Cast(@zitem as nvarchar(50)) = @zid) and Cast(@zitem as nvarchar(50)) = @zid;
end
public IList<Innovation> fyinnovationbyzitem(int pagesize, int currpage, out int totalrecords,int zid,string zitem)
{
IList<Innovation> lt = new List<Innovation>();
string cs = Dbunit.ConnectionString;
SqlConnection conn = new SqlConnection(cs);
SqlParameter[] sp = new SqlParameter[5];
sp[0] = new SqlParameter("@pagesize", pagesize);
sp[1] = new SqlParameter("@currpage", currpage);
sp[2] = new SqlParameter("@totalrecords", SqlDbType.Int, 4);
sp[2].Direction = ParameterDirection.Output;
sp[3] = new SqlParameter("@zid", zid);
sp[4] = new SqlParameter("@zitem", zitem);
using (SqlDataReader sdr = Dbunit.ExecuteReader("fyinnovationbyzitem", CommandType.StoredProcedure, sp))
{
if (sdr != null)
{
Innovation inn = null;
while (sdr.Read())
{
inn = new Innovation();
inn.Id = sdr.GetInt32(0);
inn.Img = sdr.GetString(1);
inn.Contents = sdr.GetString(2);
inn.Peiyang = sdr.GetInt32(3);
inn.Keyan = sdr.GetInt32(4);
inn.Lingyu = sdr.GetInt32(5);
inn.State = sdr.GetInt32(6);
inn.Shengyuan = sdr.GetInt32(7);
inn.Year = sdr.GetString(8);
inn.Click = sdr.GetInt32(9);
inn.Title = sdr.GetString(10);
lt.Add(inn);
}
}
}
totalrecords = Convert.ToInt32(sp[2].Value);
return lt;
}
declare @topsize int=2
declare @columnname varchar(20)='yearid'
select top (@topsize) *
from [dbo].[tb]
where @columnname=2010
