sql 分页查询
- SQL code
USE [guestbook]GO/****** Object: StoredProcedure [dbo].[selectmessage] Script Date: 08/27/2012 21:52:45 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[selectmessage] @pageSize int, @pageIndex intasdeclare @sql nvarchar(200)if(@pageIndex<2)beginset @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook'endelsebeginset @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook whereid>(select max(id) from ( select top '+convert(varchar(10),@pageSize*(@pageIndex-1))+' id from gbook order by id) a)order by id'endexec sp_executesql @sql
- C# code
//进行数据绑定的方法 public void Bind(int x,int y) { //objPds.DataSource = this.SqlDataSource1.Select(new DataSourceSelectArguments()); int CurrentPage = Convert.ToInt32(labNowPage.Text); PagedDataSource ps = new PagedDataSource();//生成PagedDataSource的实例 //实例化SqlConnection对象 SqlConnection sqlCon = new SqlConnection(); sqlCon.ConnectionString = "server=.;uid=sa;pwd=sa;database=guestbook"; //定义SQL语句 //string SqlStr = "select * from gbook order by id desc"; string SqlStr = "exec selectmessage @pageSize="+x+",@pageIndex="+y+""; //实例化SqlDataAdapter对象 SqlDataAdapter da = new SqlDataAdapter(SqlStr, sqlCon); //实例化数据集DataSet DataSet ds = new DataSet(); da.Fill(ds, "gbook"); ps.DataSource = ds.Tables["gbook"].DefaultView; //ps.AllowPaging = true; //是否可以分页 //ps.PageSize = 2; //显示的数量 ps.CurrentPageIndex = CurrentPage - 1; //取得当前页的页码 lnkbtnFront.Enabled = true; lnkbtnFirst.Enabled = true; lnkbtnNext.Enabled = true; lnkbtnLast.Enabled = true; if (CurrentPage == 1) { lnkbtnFirst.Enabled = false;//不显示第一页按钮 lnkbtnFront.Enabled = false;//不显示上一页按钮 } if (CurrentPage == ps.PageCount) { lnkbtnNext.Enabled = false;//不显示下一页 lnkbtnLast.Enabled = false;//不显示最后一页 } this.labCount.Text = Convert.ToString(ps.PageCount); this.DataList1.DataSource = ps; this.DataList1.DataKeyField = "id"; this.DataList1.DataBind(); if (Session["username"] == null) { //Panel1.Visible = false; } else { Label1.Visible = true; Label1.Text = "欢迎您！" + Session["username"].ToString(); Button1.Visible = true; Label6.Visible = true; Label7.Visible = true; TextBox2.Visible = true; //TextBox3.Visible = true; FCKeditor1.Visible = true; Button6.Visible = true; // Button9.Visible = true; Button4.Visible = true; } if (Session["admin"] == null) { // DataList1.FindControl("Button2").Visible = false; // DataList1.FindControl("Button3").Visible = false; // DataList1.FindControl("TextBox1").Visible = false; } else { Label1.Visible = true; Label1.Text = "欢迎您！" + Session["admin"].ToString(); Button1.Visible = true; //txt.Attributes.Add["href"] = "recall.aspx"; // Button button1 = DataList1.Items[0].FindControl("button1") as Button; // ((Button)DataList1.FindControl("Button2")).Attributes.Add("Visible")= "true"; // ((Button)DataList1.FindControl("Button3")).Attributes["Visible"] = "true"; //(()DataList1.FindControl("TextBox1")).Attributes["Visible"] = "true"; foreach (DataListItem item in DataList1.Items) { Button btn = (Button)item.FindControl("Button7"); btn.Visible = true; } foreach (DataListItem item in DataList1.Items) { Button btn = (Button)item.FindControl("Button8"); btn.Visible = true; } foreach (DataListItem item in DataList1.Items) { FredCK.FCKeditorV2.FCKeditor f2 = (FredCK.FCKeditorV2.FCKeditor)item.FindControl("FCKeditor2"); //TextBox tb = (TextBox)item.FindControl("TextBox1"); f2.Visible = true; } //((Button)DataList1.FindControl("Button2")).Visible = true; //((TextBox)this.DataList1.FindControl("Button2")).Visible = true; //DataList1.FindControl("Button3").Visible = true; // DataList1.FindControl("TextBox1").Visible = true; // Datalist1.Item.FindControl("TextBox1"); // Button b2=(Button)this.FindControl("Button2"); // b2.Visible = true; // Button2.visible='<%#this.Session["UserName"].ToString()=="管理员"%>' } // DataSet ds = new DataSet(); // ds.ReadXml (s) if(DataList1.FindControl(ID = "Label6")!=null) { DataList1.FindControl(ID = "Label5").Visible = true; DataList1.FindControl(ID = "Label6").Visible = true; // DataList1.FindControl(ID="Label6").Text="Eval("repcontent")"; } if (Session["username"] != null) { LinkButton3.Enabled = false; } if (Session["admin"] != null) { LinkButton1.Enabled = false; LinkButton2.Enabled = false; } } //首页 protected void lnkbtnFirst_Click(object sender, EventArgs e) { this.labNowPage.Text = "1"; this.Bind(2, Convert.ToInt32(labNowPage.Text)); } //上一页 protected void lnkbtnFront_Click(object sender, EventArgs e) { this.labNowPage.Text = Convert.ToString(Convert.ToInt32(this.labNowPage.Text) - 1); this.Bind(2, Convert.ToInt32(labNowPage.Text)); } //下一页 protected void lnkbtnNext_Click(object sender, EventArgs e) { this.labNowPage.Text = Convert.ToString(Convert.ToInt32(this.labNowPage.Text) + 1); this.Bind(2, Convert.ToInt32(labNowPage.Text)); } //尾页 protected void lnkbtnLast_Click(object sender, EventArgs e) { this.labNowPage.Text = this.labCount.Text; this.Bind(2, Convert.ToInt32(labNowPage.Text)); } //public PagedDataSource objPds = new PagedDataSource(); //使用分页类 protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { this.Bind(2,Convert.ToInt32(labNowPage.Text)); }
[解决办法]
string SqlStr = "exec selectmessage @pageSize="+x+",@pageIndex="+y+"";//没这样试过，这样也可以！
//实例化SqlDataAdapter对象
SqlDataAdapter da = new SqlDataAdapter(SqlStr, sqlCon);
//实例化数据集DataSet
DataSet ds = new DataSet();
da.Fill(ds, "gbook");
ps.DataSource = ds.Tables["gbook"].DefaultView;
//ps.AllowPaging = true; //是否可以分页
//ps.PageSize = 2; //显示的数量
ps.CurrentPageIndex = CurrentPage - 1; //取得当前页的页码
lnkbtnFront.Enabled = true;
lnkbtnFirst.Enabled = true;
lnkbtnNext.Enabled = true;
lnkbtnLast.Enabled = true;
[解决办法]
ps.DataSource = ds.Tables["gbook"].DefaultView; //这里又怎么使用数据源分页
[解决办法]
楼主想问datalist如何分页吗？我给你个市里你看看：地址http://blog.csdn.net/cnceohjm/article/details/7762079
[解决办法]
存储过程分页 自己百度
[解决办法]
存储过程分页要有两个反回值
第一 你的分页数据 比如 一页二十条你将反回20条数据
第二 你的数据总数
根据楼主的代码我们可以去掉 ps所有代码
this.DataList1.DataSource=ds.Tables["gbook"];
this.DataList1.DataBind();
下面有详细代码写的挺不错的
http://www.cnblogs.com/bihailantian/archive/2010/09/28/1837347.html
[解决办法]
楼主为什么非要走错误的路呢。这样你用存储过程没有意义
[解决办法]