try:CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT,
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUTAS
SELECT @RecordCount = COUNT(*) FROM MoneyRecord WHERE UserName=@strUserName
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR VARCHAR(500)
DECLARE @strWhere VARCHAR(500)
DECLARE @MinID INT,@sql nvarchar(1000)set @sql=N'SELECT @MinID=MIN(RecordID) '+
N' FROM (SELECT TOP '+convert(varchar,@PageIndex*@PageSize)+
N' RecordID from [MoneyRecord] where UserName=@strUserName)t'
exec sp_executesql @sql,N'@MinID int out,@strUserName VARCHAR(30)',@MinID out,@strUserNameSET @strWhere =' RecordID<'+convert(varchar,@MinID)+' and (UserName = '''+str(@strUserName)+''')' IF @PageIndex = 0
begin
SET @SQLSTR =' SELECT TOP '+STR( @PageSize )+' myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM [MoneyRecord] where '+str(@strWhere)+' Order by RecordID desc'
endELSE
begin
SET @SQLSTR =' SELECT TOP '+STR( @PageSize )+' RecordID, myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM [MoneyRecord] where '+'( RecordID< (select min(RecordID) from (SELECT TOP '+convert(varchar,@PageIndex * @PageSize) +' RecordID from [MoneyRecord] where UserName='''+str(@strUserName)+''')t)) and (UserName = '''+str(@strUserName)+''')'+' Order by RecordID desc'
endEXEC (@SQLSTR)
GO
@PageIndex INT,
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUTAS
SELECT @RecordCount = COUNT(*) FROM MoneyRecord WHERE UserName=@strUserName
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR VARCHAR(500)
DECLARE @strWhere VARCHAR(500)
DECLARE @MinID INT,@sql nvarchar(1000)set @sql=N'SELECT @MinID=MIN(RecordID) '+
N' FROM (SELECT TOP '+convert(varchar,@PageIndex*@PageSize)+
N' RecordID from [MoneyRecord] where UserName=@strUserName)t'
exec sp_executesql @sql,N'@MinID int out,@strUserName VARCHAR(30)',@MinID out,@strUserNameSET @strWhere =' RecordID<'+convert(varchar,@MinID)+' and (UserName = '''+str(@strUserName)+''')' IF @PageIndex = 0
begin
SET @SQLSTR =' SELECT TOP '+STR( @PageSize )+' myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM [MoneyRecord] where '+str(@strWhere)+' Order by RecordID desc'
endELSE
begin
SET @SQLSTR =' SELECT TOP '+STR( @PageSize )+' RecordID, myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM [MoneyRecord] where '+'( RecordID< (select min(RecordID) from (SELECT TOP '+convert(varchar,@PageIndex * @PageSize) +' RecordID from [MoneyRecord] where UserName='''+str(@strUserName)+''')t)) and (UserName = '''+str(@strUserName)+''')'+' Order by RecordID desc'
endEXEC (@SQLSTR)
GO
我的这个存储过程是用来给DATAGIRD分页用的
运行时报错
将数据类型 varchar 转换为 float 时出错。 1 RecordID bigint 8 0数据库结构为:
0 UserName varchar 30 1
0 myDateTime datetime 8 1
0 MoneyType bit 1 1
0 MoneyNum decimal 9 1
0 myKeyWords varchar 50 1
0 Others varchar 200 1
{
string strUserName = Session["UserName"].ToString();
PageSize = this.DGMoneyList.PageSize;;
PageIndex = this.DGMoneyList.CurrentPageIndex;
DataSet ds = GetCustomersData(PageIndex,PageSize,strUserName,ref recordCount,ref pageCount);
this.DGMoneyList.VirtualItemCount = recordCount;
this.DGMoneyList.DataSource = ds;
this.DGMoneyList.DataBind();
ShowStatus();
}
private static DataSet GetCustomersData(int pageIndex,int pageSize,string strUserName,ref int recordCount,ref int pageCount)
{
string connString = ConfigurationSettings.AppSettings["strConnection"];
SqlConnection conn = new SqlConnection(connString); SqlCommand comm = new SqlCommand("GetCustomersDataPage",conn);
comm.Parameters.Add(new SqlParameter("@PageIndex",SqlDbType.Int));
comm.Parameters[0].Value = pageIndex;
comm.Parameters.Add(new SqlParameter("@PageSize",SqlDbType.Int));
comm.Parameters[1].Value = pageSize;
comm.Parameters.Add(new SqlParameter("@RecordCount",SqlDbType.Int));
comm.Parameters[2].Direction = ParameterDirection.Output;
comm.Parameters.Add(new SqlParameter("@PageCount",SqlDbType.Int));
comm.Parameters[3].Direction = ParameterDirection.Output;
comm.Parameters.Add(new SqlParameter("@strUserName",SqlDbType.VarChar));
comm.Parameters[4].Value = strUserName; comm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
recordCount = (int)comm.Parameters[2].Value;
pageCount = (int)comm.Parameters[3].Value; return ds;
}