hi,
the code below works fine when I use asp.net with sql server
private void GetProductsPageWise(int pageIndex)
{
string constring = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("declare @RecordCount int declare @PageSize int=4 declare @PageIndex int=3 Select @RecordCount= Count(*) From tbl_product SELECT * FROM (SELECT Top (@PageSize) sub.* FROM (SELECT TOP ((@RecordCount) - (((@PageIndex) - 1) * (@PageSize))) tbl_product.* FROM tbl_product ORDER BY tbl_product.pd_id Desc) sub ORDER BY sub.pd_id Asc) subOrdered ORDER BY subOrdered.pd_id", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", 5);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@cat_id", Request.QueryString["pcId"]);
con.Open();
IDataReader idr = cmd.ExecuteReader();
lst.DataSource = idr;
lst.DataBind();
idr.Close();
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
}
but I'd like do it with access database instead of sql server
so I chenged above code to like below:
private void GetProductsPageWise(int pageIndex)
{
string constring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\NextYear\Desktop\paginnationtest\test.mdb";
using (OleDbConnection con = new OleDbConnection(constring))
{
using (OleDbCommand cmd = new OleDbCommand("declare @RecordCount int declare @PageSize int=4 declare @PageIndex int=3 Select @RecordCount= Count(*) From tbl_product SELECT * FROM (SELECT Top (@PageSize) sub.* FROM (SELECT TOP ((@RecordCount) - (((@PageIndex) - 1) * (@PageSize))) tbl_product.* FROM tbl_product ORDER BY tbl_product.pd_id Desc) sub ORDER BY sub.pd_id Asc) subOrdered ORDER BY subOrdered.pd_id", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", 5);
cmd.Parameters.Add("@RecordCount", OleDbType.Integer, 4);
//cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@cat_id", Request.QueryString["pcId"]);
con.Open();
IDataReader idr = cmd.ExecuteReader();
lst.DataSource = idr;
lst.DataBind();
idr.Close();
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
but it throws this exception:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
I know working with parameters is different when we use access, please let me know what is wrong in above code?
thanks in advance