我写的一条分页 语句在MS SQL 里正常 但是放到ASP.net 里不行了 麻烦各位高手给看看 代码如下using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace jnsapo
{
/// <summary>
/// News_List 的摘要说明。
/// </summary>
public class News_List : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
string type;
protected OleDbConnection conn;
protected OleDbCommand selcmd;
protected OleDbDataAdapter da;
protected DataSet ds;
private void Page_Load(object sender, System.EventArgs e)
{
type=Page.Request.QueryString["type"].ToString();
Page.Response.Write(type); String strconn = "Provider=Microsoft.Jet.OleDb.4.0;Mode=ReadWrite;Jet OLEDB:Database Password=***;Data Source=";
strconn+=Server.MapPath("mdb/jnsapo.mdb");
try
{
conn = new OleDbConnection(strconn);
conn.Open();
}
catch(Exception ee)
{
Page.Response.Write("出错了");
}
selcmd = new OleDbCommand("Select TOP 5 title from News where type='"+ type +"' and 'id NOT IN(SELECT TOP 10 id FROM News )' ORDER BY id desc",conn);
da = new OleDbDataAdapter(selcmd);
ds = new DataSet();
da.Fill(ds,"News");
DataGrid1.DataSource=ds.Tables["News"];
DataGrid1.DataBind();
for(int i=0;i<3;i++)
Response.Write("<a href=index.aspx>"+i+"</a>");
} #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.DataGrid1.SelectedIndexChanged += new System.EventHandler(this.DataGrid1_SelectedIndexChanged);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion
private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e)
{
}
}
}--------------------------
selcmd = new OleDbCommand("Select TOP 5 title from News where type='"+ type +"' and 'id NOT IN(SELECT TOP 10 id FROM News )' ORDER BY id desc",conn);
就是这句话 本来这个应该返回 第11—15条数据 但是 现在返回的还是地1~5条 还有不知道为什么在ASP.net 里 'id NOT IN(SELECT TOP 10 id FROM News )' 这里要加''单引号 不然就报错 麻烦高手给解释一下
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace jnsapo
{
/// <summary>
/// News_List 的摘要说明。
/// </summary>
public class News_List : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
string type;
protected OleDbConnection conn;
protected OleDbCommand selcmd;
protected OleDbDataAdapter da;
protected DataSet ds;
private void Page_Load(object sender, System.EventArgs e)
{
type=Page.Request.QueryString["type"].ToString();
Page.Response.Write(type); String strconn = "Provider=Microsoft.Jet.OleDb.4.0;Mode=ReadWrite;Jet OLEDB:Database Password=***;Data Source=";
strconn+=Server.MapPath("mdb/jnsapo.mdb");
try
{
conn = new OleDbConnection(strconn);
conn.Open();
}
catch(Exception ee)
{
Page.Response.Write("出错了");
}
selcmd = new OleDbCommand("Select TOP 5 title from News where type='"+ type +"' and 'id NOT IN(SELECT TOP 10 id FROM News )' ORDER BY id desc",conn);
da = new OleDbDataAdapter(selcmd);
ds = new DataSet();
da.Fill(ds,"News");
DataGrid1.DataSource=ds.Tables["News"];
DataGrid1.DataBind();
for(int i=0;i<3;i++)
Response.Write("<a href=index.aspx>"+i+"</a>");
} #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.DataGrid1.SelectedIndexChanged += new System.EventHandler(this.DataGrid1_SelectedIndexChanged);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion
private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e)
{
}
}
}--------------------------
selcmd = new OleDbCommand("Select TOP 5 title from News where type='"+ type +"' and 'id NOT IN(SELECT TOP 10 id FROM News )' ORDER BY id desc",conn);
就是这句话 本来这个应该返回 第11—15条数据 但是 现在返回的还是地1~5条 还有不知道为什么在ASP.net 里 'id NOT IN(SELECT TOP 10 id FROM News )' 这里要加''单引号 不然就报错 麻烦高手给解释一下
FROM KnowHome
WHERE [KnowHomeID] not in(
Select top 1 [KnowHomeID] From [KnowHome] Order By [KnowHomeID] Desc)
ORDER BY [KnowHomeID] DESC;
SELECT TOP 10 *
FROM KnowHome
ORDER BY [KnowHomeID] DESC;
FROM KnowHome
WHERE [KnowHomeID] not in(
Select top 1 [KnowHomeID] From [KnowHome] Order By [KnowHomeID] Desc)
ORDER BY [KnowHomeID] DESC;
这和我写的一样啊
selcmd = new OleDbCommand("Select TOP 5 title from News where type='"+ type +"' and 'id NOT IN(SELECT TOP 10 id FROM News ORDER BY id desc )' ORDER BY id desc",conn); 但问题是 他不管用阿 在MS sql里面可以 放到程序里不行了
原因:
Select TOP 5 title from #table where id NOT IN(SELECT TOP 10 id FROM #table)order by id desc"where id NOT IN(SELECT TOP 10 id FROM #table)"就这里了,这里的“select top 10 id from #table “是正序排列的,也是就说语句中不包函前面10条,面你后面又用倒序排列,
order by desc 当然会取出前面5条数据了
where id NOT IN(SELECT TOP 10 id FROM #table)—>
where id not in (select top 10 id from #table order by id desc)
and 'id NOT IN(SELECT TOP 10 id FROM News )'
SELECT TOP 10 [ID],Title
FROM
(
SELECT TOP 20 [ID],Title FROM News WHERE Type='" + type +"' ORDER BY [ID] DESC
) A
ORDER BY [ID] DESC第21-30条:
SELECT TOP 10 [ID],Title
FROM
(
SELECT TOP 30 [ID],Title FROM News WHERE Type='" + type +"' ORDER BY [ID] DESC
) A
ORDER BY [ID] DESC