我有一段代码:
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
System.Data.SqlClient.SqlParameter[] commandParameters = new System.Data.SqlClient.SqlParameter[9];
//int intResult;
commandParameters[0] = SqlHelper.MakeInParam("@tblName", SqlDbType.NVarChar, 500, tblName);
commandParameters[1] = SqlHelper.MakeInParam("@fldName", SqlDbType.NVarChar, 500, fldName);
commandParameters[2] = SqlHelper.MakeInParam("@pageSize", SqlDbType.Int , 4, pageSize);
commandParameters[3] = SqlHelper.MakeInParam("@page", SqlDbType.Int , 4, pageIndex);
commandParameters[4] = SqlHelper.MakeInParam("@fldSort", SqlDbType.NVarChar, 500, fldSort);
commandParameters[5] = SqlHelper.MakeInParam("@Sort", SqlDbType.Bit, 500, Sort);
commandParameters[6] = SqlHelper.MakeInParam("@strCondition", SqlDbType.NVarChar, 500, strCondition);
commandParameters[7] = SqlHelper.MakeInParam("@ID", SqlDbType.NVarChar, 500, ID);
commandParameters[8] = SqlHelper.MakeInParam("@Dist", SqlDbType.Bit , 500, Dist);
DataTable dtb = new DataTable(); PrepareCommand(cmd, conn, null, CommandType.StoredProcedure, "sp_GetDataCanOrder", commandParameters);//该方法稍下看 sp_GetDataCanOrder 是一个存储过程 SqlDataAdapter sqlReader = new SqlDataAdapter(cmd);
sqlReader.Fill(dtb);
conn.Close();
conn.Dispose();
数据在 SqlDataAdapter sqlReader = new SqlDataAdapter(cmd); 从数据库往外取呢,还是在 sqlReader.Fill(dtb);
这个时候从数据库往外取?我的程序在 sqlReader.Fill(dtb); 报错:
第 1 行: 'D' 附近有语法错误。 除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。 在关键字 'and' 附近有语法错误。 在关键字 'and' 附近有语法错误。 除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。如何解决呀?PrepareCommand 方法如下:
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{ if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.CommandText = cmdText; if (trans != null)
cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
if (parm.Value == null) parm.Value = DBNull.Value;
cmd.Parameters.Add(parm);
}
}
}
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
System.Data.SqlClient.SqlParameter[] commandParameters = new System.Data.SqlClient.SqlParameter[9];
//int intResult;
commandParameters[0] = SqlHelper.MakeInParam("@tblName", SqlDbType.NVarChar, 500, tblName);
commandParameters[1] = SqlHelper.MakeInParam("@fldName", SqlDbType.NVarChar, 500, fldName);
commandParameters[2] = SqlHelper.MakeInParam("@pageSize", SqlDbType.Int , 4, pageSize);
commandParameters[3] = SqlHelper.MakeInParam("@page", SqlDbType.Int , 4, pageIndex);
commandParameters[4] = SqlHelper.MakeInParam("@fldSort", SqlDbType.NVarChar, 500, fldSort);
commandParameters[5] = SqlHelper.MakeInParam("@Sort", SqlDbType.Bit, 500, Sort);
commandParameters[6] = SqlHelper.MakeInParam("@strCondition", SqlDbType.NVarChar, 500, strCondition);
commandParameters[7] = SqlHelper.MakeInParam("@ID", SqlDbType.NVarChar, 500, ID);
commandParameters[8] = SqlHelper.MakeInParam("@Dist", SqlDbType.Bit , 500, Dist);
DataTable dtb = new DataTable(); PrepareCommand(cmd, conn, null, CommandType.StoredProcedure, "sp_GetDataCanOrder", commandParameters);//该方法稍下看 sp_GetDataCanOrder 是一个存储过程 SqlDataAdapter sqlReader = new SqlDataAdapter(cmd);
sqlReader.Fill(dtb);
conn.Close();
conn.Dispose();
数据在 SqlDataAdapter sqlReader = new SqlDataAdapter(cmd); 从数据库往外取呢,还是在 sqlReader.Fill(dtb);
这个时候从数据库往外取?我的程序在 sqlReader.Fill(dtb); 报错:
第 1 行: 'D' 附近有语法错误。 除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。 在关键字 'and' 附近有语法错误。 在关键字 'and' 附近有语法错误。 除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。如何解决呀?PrepareCommand 方法如下:
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{ if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.CommandText = cmdText; if (trans != null)
cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
if (parm.Value == null) parm.Value = DBNull.Value;
cmd.Parameters.Add(parm);
}
}
}
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_GetDataCanOrder
(
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fldName nvarchar(500) = '*', ----要显示的字段列表
@pageSize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@fldSort nvarchar(200) = null, ----排序字段列表或条件
@Sort bit = 0, ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ')
@strCondition nvarchar(4000) = null, ----查询条件,不需where
@ID nvarchar(150), ----主表的主键
@Dist bit = 0 , ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
@strSql nvarchar(1000) = '' output,
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output ----查询到的记录数
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(2000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则BDeclare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
if @Dist = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(*)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts --取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize /**//**当前页大于总页数 取最后一页**/
if @page>@pageCount
set @page=@pageCount --/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小 set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize --//***显示分页
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+')'
+' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+')' +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' Where (1>0) ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'
+' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
-----2010年1月5日
--select @SqlSelect,@page,@strSortType
---------
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+')'
+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end------返回查询结果-----
set @strSql = @strTmpexec sp_executesql @strTmp
--print @strTmpGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO我在查询分析器里直接执行存储过程是没有任何错误的:
exec sp_GetDataCanOrder 'BaiDu_TieBaDetails','*','10','1','FaTieTime','1',
' and Not Exists (select DetailsSN from BaiDu_TieBaDetailsInfor where BaiDu_TieBaDetails.SN=BaiDu_TieBaDetailsInfor.DetailsSN and BaiDu_TieBaDetailsInfor.IsCompetitor=1) and Exists (select DetailsSN from BaiDu_TieBaDetailsInfor where BaiDu_TieBaDetails.SN=BaiDu_TieBaDetailsInfor.DetailsSN ) and not Exists( select DetailsSN from BaiDu_TieBaDetailsInfor A where A.DetailsSN=BaiDu_TieBaDetails.SN and TypeNo=5 and Exists (select DetailsSN,Count(1) from BaiDu_TieBaDetailsInfor B where B.DetailsSN=A.DetailsSN group by DetailsSN having Count(1)=1) )',
'SN','0'最后的语句:
select top 10 * from BaiDu_TieBaDetails where SN not in(select top 0 SN from BaiDu_TieBaDetails Where (1>0) and Not Exists (select DetailsSN from BaiDu_TieBaDetailsInfor where BaiDu_TieBaDetails.SN=BaiDu_TieBaDetailsInfor.DetailsSN and BaiDu_TieBaDetailsInfor.IsCompetitor=1) and Exists (select DetailsSN from BaiDu_TieBaDetailsInfor where BaiDu_TieBaDetails.SN=BaiDu_TieBaDetailsInfor.DetailsSN ) and not Exists( select DetailsSN from BaiDu_TieBaDetailsInfor A where A.DetailsSN=BaiDu_TieBaDetails.SN and TypeNo=5 and Exists (select DetailsSN,Count(1) from BaiDu_TieBaDetailsInfor B where B.DetailsSN=A.DetailsSN group by DetailsSN having Count(1)=1) ) order by FaTieTime DESC ) and Not Exists (select DetailsSN from BaiDu_TieBaDetailsInfor where BaiDu_TieBaDetails.SN=BaiDu_TieBaDetailsInfor.DetailsSN and BaiDu_TieBaDetailsInfor.IsCompetitor=1) and Exists (select DetailsSN from BaiDu_TieBaDetailsInfor where BaiDu_TieBaDetails.SN=BaiDu_TieBaDetailsInfor.DetailsSN ) and not Exists( select DetailsSN from BaiDu_TieBaDetailsInfor A where A.DetailsSN=BaiDu_TieBaDetails.SN and TypeNo=5 and Exists (select DetailsSN,Count(1) from BaiDu_TieBaDetailsInfor B where B.DetailsSN=A.DetailsSN group by DetailsSN having Count(1)=1) ) order by FaTieTime DESC
请大家过目。