我的页面一运行就提示'@pagesize' 附近有语法错误。但是我在数据库里直接查询,把那些参数替换掉就OK,这句SQL我不知道到底错在哪里
string sql = "select top @pagesize from member where userid not in(select top(@pagesize*(@pagecount-1)) userid from member order by userid) order by userid";
-----------------------------------------这里是使用这句SQL的方法--------------------------------/// <summary>
/// 分页获取用户
/// </summary>
/// <param name="pageSize">每页用户数</param>
/// <param name="pageCount">第几页</param>
/// <returns></returns>
public DataTable GetUser(int pageSize, int pageCount)
{
string sql = "select top @pagesize from member where userid not in(select top(@pagesize*(@pagecount-1)) userid from member order by userid) order by userid";
SqlParameter[] parameters = new SqlParameter[2]; parameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
parameters[0].Value = pageSize; parameters[1] = new SqlParameter("@pagecount", SqlDbType.Int);
parameters[1].Value = pageCount;
return new SqlDbHelper(connectionString).ExecuteDataTable(sql, CommandType.Text, parameters);
//todo:必须将上面的代码修改正确! /*
string sql = "select top 20 * from member";
return new SqlDbHelper(connectionString).ExecuteDataTable(sql);
*/
}-------------------------------这里是ExecuteDataTable方法----------------------------------------------
ExecuteDataTable是周金桥老师写的那个SqlDbHelper数据库操作的通用类里的下面是SqlDbHelper中ExecuteDataTable的代码/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
{
DataTable data = new DataTable();//实例化DataTable,用于装载查询结果集
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数,则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
//通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(data);//填充DataTable
}
}
return data;
}
string sql = "select top @pagesize from member where userid not in(select top(@pagesize*(@pagecount-1)) userid from member order by userid) order by userid";
-----------------------------------------这里是使用这句SQL的方法--------------------------------/// <summary>
/// 分页获取用户
/// </summary>
/// <param name="pageSize">每页用户数</param>
/// <param name="pageCount">第几页</param>
/// <returns></returns>
public DataTable GetUser(int pageSize, int pageCount)
{
string sql = "select top @pagesize from member where userid not in(select top(@pagesize*(@pagecount-1)) userid from member order by userid) order by userid";
SqlParameter[] parameters = new SqlParameter[2]; parameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
parameters[0].Value = pageSize; parameters[1] = new SqlParameter("@pagecount", SqlDbType.Int);
parameters[1].Value = pageCount;
return new SqlDbHelper(connectionString).ExecuteDataTable(sql, CommandType.Text, parameters);
//todo:必须将上面的代码修改正确! /*
string sql = "select top 20 * from member";
return new SqlDbHelper(connectionString).ExecuteDataTable(sql);
*/
}-------------------------------这里是ExecuteDataTable方法----------------------------------------------
ExecuteDataTable是周金桥老师写的那个SqlDbHelper数据库操作的通用类里的下面是SqlDbHelper中ExecuteDataTable的代码/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
{
DataTable data = new DataTable();//实例化DataTable,用于装载查询结果集
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数,则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
//通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(data);//填充DataTable
}
}
return data;
}
就是这个原因,(*原来是有的,刚才又重新写那句sql的时候搞丢的)。多谢!
对SQL不熟啊,这个昨天晚上都一直在找原因到现在,也没想到会是这个原因呐
percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' desc) as b order by '+@orderfield+' asc) as a order by '+@orderfield+' asc '';'