none
我用ACCESS写了一个分页的方法总提示SELECT子句有错!! RRS feed

  • 问题

  •  

    我用的是在网上找的:

    select top 15 * from YOURTABLE where ID not in(select top 15 ID from YOURTABLE order by ID desc) order by ID desc

    http://blog.csdn.net/wallimn/archive/2007/09/10/1778703.aspx 我写的方法如下:

     

     

        /// <summary>
        /// 根据查寻条件返回一个DataTable
        /// </summary>
        /// <param name="PageSize">每页显示的条数</param>
        /// <param name="CurrentPage">当前页码</param>
        /// <param name="strWhere">查寻条件</param>
        /// <param name="Record">总记录数</param>
        /// <returns></returns>
        public static DataTable GetAllMusicByStrWhere(int PageSize, int CurrentPage, string strWhere, out int Record)
        {
            string strRecord = "SELECT COUNT(*) FROM mkList";
            Record = Convert.ToInt32(Oledb.getScalar(strRecord));
            DataTable dt;
            string strSql;
            if (strWhere == "")
            {
                strSql = "SELECT TOP 1 * FROM mkList WHERE musicId NOT IN (SELECT TOP 1 musicId FROM mkList ORDER BY musicId DESC)ORDER BY musicId DESC";
                ////strSql = "SELECT TOP @PageSize * FROM mkList WHERE musicId NOT IN (SELECT TOP 1 musicId FROM mkList ORDER BY musicId DESC)ORDER BY musicId DESC";
                OleDbParameter[] prams = { new OleDbParameter("@PageSize",OleDbType.VarChar,50),
                                           new OleDbParameter("@currebtXsize",OleDbType.Integer)};

                prams[0].Value = PageSize.ToString();
                prams[1].Value = PageSize * CurrentPage;
                dt = Oledb.getDataTable(strSql, prams);
            }
            else
            {
                strSql = "SELECT TOP 1 * FROM mkList WHERE musicName = @strWhere AND musicId NOT IN (SELECT TOP 1 musicId FROM mkList ORDER BY musicId DESC)ORDER BY musicId DESC";
                OleDbParameter[] prams = { new OleDbParameter("@strWhere",OleDbType.VarChar,50),
                                       new OleDbParameter("@PageSize",OleDbType.Integer),
                                       new OleDbParameter("@currebtXsize",OleDbType.Integer)};
                prams[0].Value = strWhere;
                prams[1].Value = PageSize;
                prams[2].Value = PageSize * CurrentPage;
                dt = Oledb.getDataTable(strSql, prams);
            }

            //DataTable dt = Oledb.getDataTable(strSql, prams);
            return dt;
        }

    2008年9月30日 20:53

答案

  • 这是一个完整的例子。

    Code Snippet

     

     

     

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;

    public partial class ShowUsers : System.Web.UI.Page
    {

      public OleDbDataReader GetAllMusicByStrWhere(int PageSize, int CurrentPage, string strWhere, out int Record)
      {
        string strRecord = "SELECT COUNT(*) FROM Document";
        cmd.CommandText = strRecord;
        Record = Convert.ToInt32(cmd.ExecuteScalar());
        OleDbDataReader dt;
        string strSql;
        if (CurrentPage == 1)
        {
          if (strWhere == "")
          {
            strSql = "SELECT TOP " + PageSize.ToString() + " * FROM Document ORDER BY id DESC";
          }
          else
          {
            strSql = "SELECT TOP " + PageSize.ToString() + " * FROM Document Where Title=@strWhere ORDER BY id DESC";
             cmd.Parameters.AddWithValue("@strWhere", strWhere);
          }
        }
        else
        {
          if (strWhere == "")
          {
            strSql = "SELECT TOP " + PageSize.ToString() + " * FROM Document WHERE id NOT IN (SELECT TOP " + ((CurrentPage - 1) * PageSize).ToString() + " id FROM Document ORDER BY id DESC ) ORDER BY id DESC";
          }
          else
          {
            strSql = "SELECT TOP " + PageSize.ToString() + " * FROM Document WHERE Title=@strWhere And id NOT IN (SELECT TOP " + ((CurrentPage - 1) * PageSize).ToString() + " id FROM Document ORDER BY id DESC ) ORDER BY id DESC";
            cmd.Parameters.AddWithValue("@strWhere", strWhere);
          }
        }
        cmd.CommandText = strSql;
        return cmd.ExecuteReader();
      }


     private OleDbCommand cmd = null;
     private  OleDbConnection cn = null;
      protected void Page_Load(object sender, EventArgs e)
      {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aspxWeb.mdb;";
        cn = new OleDbConnection(connectionString);
        cn.Open();
        cmd = new OleDbCommand();
        cmd.Connection = cn;
        int total = 0;
        int CurrentPage = Convert.ToInt32(Request.QueryString["page"]);
        OleDbDataReader dr = this.GetAllMusicByStrWhere(10, CurrentPage,"",out total);
        GridView1.DataSource = dr;
        GridView1.DataBind();
        cn.Close();
      }


    }

     

     

     

     

    2008年10月1日 0:12
    版主

全部回复

  • 这是一个完整的例子。

    Code Snippet

     

     

     

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;

    public partial class ShowUsers : System.Web.UI.Page
    {

      public OleDbDataReader GetAllMusicByStrWhere(int PageSize, int CurrentPage, string strWhere, out int Record)
      {
        string strRecord = "SELECT COUNT(*) FROM Document";
        cmd.CommandText = strRecord;
        Record = Convert.ToInt32(cmd.ExecuteScalar());
        OleDbDataReader dt;
        string strSql;
        if (CurrentPage == 1)
        {
          if (strWhere == "")
          {
            strSql = "SELECT TOP " + PageSize.ToString() + " * FROM Document ORDER BY id DESC";
          }
          else
          {
            strSql = "SELECT TOP " + PageSize.ToString() + " * FROM Document Where Title=@strWhere ORDER BY id DESC";
             cmd.Parameters.AddWithValue("@strWhere", strWhere);
          }
        }
        else
        {
          if (strWhere == "")
          {
            strSql = "SELECT TOP " + PageSize.ToString() + " * FROM Document WHERE id NOT IN (SELECT TOP " + ((CurrentPage - 1) * PageSize).ToString() + " id FROM Document ORDER BY id DESC ) ORDER BY id DESC";
          }
          else
          {
            strSql = "SELECT TOP " + PageSize.ToString() + " * FROM Document WHERE Title=@strWhere And id NOT IN (SELECT TOP " + ((CurrentPage - 1) * PageSize).ToString() + " id FROM Document ORDER BY id DESC ) ORDER BY id DESC";
            cmd.Parameters.AddWithValue("@strWhere", strWhere);
          }
        }
        cmd.CommandText = strSql;
        return cmd.ExecuteReader();
      }


     private OleDbCommand cmd = null;
     private  OleDbConnection cn = null;
      protected void Page_Load(object sender, EventArgs e)
      {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aspxWeb.mdb;";
        cn = new OleDbConnection(connectionString);
        cn.Open();
        cmd = new OleDbCommand();
        cmd.Connection = cn;
        int total = 0;
        int CurrentPage = Convert.ToInt32(Request.QueryString["page"]);
        OleDbDataReader dr = this.GetAllMusicByStrWhere(10, CurrentPage,"",out total);
        GridView1.DataSource = dr;
        GridView1.DataBind();
        cn.Close();
      }


    }

     

     

     

     

    2008年10月1日 0:12
    版主
  • 谢谢孟老师

    2008年10月1日 16:43