swati agrawal

swati agrawal

  • NA
  • 20
  • 98k

problem in datalist custom paging

Mar 8 2011 12:59 AM

I have a datalist in which i have to do paging.for  bind that datalist m calling a store procedure dat is given below:

USE [tklight]
GO
/****** Object:  StoredProcedure [dbo].[spx_Pager2]    Script Date: 03/08/2011 10:10:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spx_Pager2]
    @PageNo int ,
    @ItemsPerPage int ,
    @TotalRows int out,
    @Country varchar(50),
    @Property int,
    @state varchar(50),
    @city varchar(50),
    @Area varchar(50)
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @StartIdx int
  DECLARE @SQL nvarchar(max)
  DECLARE @SQL_Conditions nvarchar(max)
  DECLARE @EndIdx int

    IF @PageNo < 1 SET @PageNo = 1
    IF @ItemsPerPage < 1 SET @ItemsPerPage = 10

    SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
    SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
   
    if(@Country Is Not Null)
    SET @SQL = 'SELECT RegId,Name, (Address  + City)as Address, Zip, Contact, Email, Password, PropertyTypeId, StatRating, Image
    FROM (SELECT  ROW_NUMBER() OVER(ORDER BY RegId) AS Row, *  FROM  tbl_hotels ) AS tbl
    WHERE  Row >= @StartIdx AND Row <=  @EndIdx And Country =@Country'
   
    if(@Country Is Not Null)And (@Property !='0')
    SET @SQL = 'SELECT RegId,Name, (Address  + City)as Address, Zip, Contact, Email, Password, PropertyTypeId, StatRating, Image
    FROM (SELECT  ROW_NUMBER() OVER(ORDER BY RegId) AS Row, *  FROM  tbl_hotels ) AS tbl
    WHERE  Row >= @StartIdx AND Row <=  @EndIdx And Country =@Country And PropertyTypeId=@Property'
   
    if( @state Is Not Null)And (@Country Is Not Null)
     SET @SQL = 'SELECT RegId,Name, (Address  + City)as Address, Zip, Contact, Email, Password, PropertyTypeId, StatRating, Image
    FROM (SELECT  ROW_NUMBER() OVER(ORDER BY RegId) AS Row, *  FROM  tbl_hotels ) AS tbl
    WHERE  Row >= @StartIdx AND Row <=  @EndIdx And Country =@Country And State=@state'
  
    if( @state Is Not Null)And (@Country Is Not Null) And(@Property !='0')
    SET @SQL = 'SELECT RegId,Name, (Address  + City)as Address, Zip, Contact, Email, Password, PropertyTypeId, StatRating, Image
    FROM (SELECT  ROW_NUMBER() OVER(ORDER BY RegId) AS Row, *  FROM  tbl_hotels ) AS tbl
    WHERE  Row >= @StartIdx AND Row <=  @EndIdx And Country =@Country And State=@state And PropertyTypeId=@Property'
   
    if( @city Is Not Null)And( @state Is Not Null)And (@Country Is Not Null)
    SET @SQL ='SELECT RegId,Name, (Address  + City)as Address, Zip, Contact, Email, Password, PropertyTypeId, StatRating, Image
    FROM (SELECT  ROW_NUMBER() OVER(ORDER BY RegId) AS Row, *  FROM  tbl_hotels ) AS tbl
    WHERE  Row >= @StartIdx AND Row <=  @EndIdx And Country =@Country And State=@state And City=@city'
   
    if( @city Is Not Null)And( @state Is Not Null)And (@Country Is Not Null) And(@Property !='0')
   
    SET @SQL = 'SELECT RegId,Name, (Address  + City)as Address, Zip, Contact, Email, Password, PropertyTypeId, StatRating, Image
    FROM (SELECT  ROW_NUMBER() OVER(ORDER BY RegId) AS Row, *  FROM  tbl_hotels ) AS tbl
    WHERE  Row >= @StartIdx AND Row <=  @EndIdx And Country =@Country And State=@state And City=@city And PropertyTypeId=@Property'
   
    if( @city Is Not Null)And( @state Is Not Null)And (@Country Is Not Null)And(@Area Is Not Null)
       SET @SQL = 'SELECT RegId,Name, (Address  + City)as Address, Zip, Contact, Email, Password, PropertyTypeId, StatRating, Image
    FROM (SELECT  ROW_NUMBER() OVER(ORDER BY RegId) AS Row, *  FROM  tbl_hotels ) AS tbl
    WHERE  Row >= @StartIdx AND Row <=  @EndIdx And Country =@Country And State=@state And City=@city And Area=@Area'
     
    if( @city Is Not Null)And( @state Is Not Null)And (@Country Is Not Null)And(@Area Is Not Null)And (@Property !='0')   
   
     SET @SQL = 'SELECT RegId,Name, (Address  + City)as Address, Zip, Contact, Email, Password, PropertyTypeId, StatRating, Image
    FROM (SELECT  ROW_NUMBER() OVER(ORDER BY RegId) AS Row, *  FROM  tbl_hotels ) AS tbl
    WHERE  Row >= @StartIdx AND Row <=  @EndIdx And Country =@Country And State=@state And City=@city And Area=@Area And PropertyTypeId=@Property'
     
   
    EXEC sp_executesql @SQL
    SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tbl_hotels'
    EXEC sp_executesql
        @query = @SQL,
         @params = N'@TotalRows INT OUTPUT',
        @TotalRows = @TotalRows OUTPUT
END

Problem is:I have already declare @StartIdx , @EndIdx  dese vaeiable in sp bt whem m runing the page its give me an execption dats is:
Must declare variable @StartIdx .and Procedure or function 'spx_Pager2' expects parameter '@city', which was not supplied.

my code for bind datalist is:
 private int BindList(int PageNo)
        {
            DataTable dataTable = this.GetDataTable();
            _PageDataSource.DataSource = dataTable.DefaultView;
            _PageDataSource.AllowPaging = true;
            _PageDataSource.PageSize = 1;
            _PageDataSource.CurrentPageIndex = CurrentPage;
            //_PageDataSource.PageSize = 10;
            ViewState["TotalPages"] = _PageDataSource.PageSize;
           // ViewState["TotalPages"] = _PageDataSource.PageCount;
            this.lblPageInfo.Text = "Page " + (CurrentPage) + " of " + _PageDataSource.PageCount;
            this.lbtnPrevious.Enabled = !_PageDataSource.IsFirstPage;
            this.lbtnNext.Enabled = !_PageDataSource.IsLastPage;
            this.lbtnFirst.Enabled = !_PageDataSource.IsFirstPage;
            this.lbtnLast.Enabled = !_PageDataSource.IsLastPage;
            //this.dListItems.DataSource = _PageDataSource;
            //this.dListItems.DataBind();
            int TotalRows = 0;
            if (Session["country"] != null)
            {
                country = Session["country"].ToString();
            }
            if (Session["Property"] != null)
            {
                Property =Convert.ToInt32(Session["Property"].ToString());
              
            }
            if (Session["state"] != null)
            {
                State = Session["state"].ToString();
            }
         
            if (Session["city"] != null )
            {
                City = Session["city"].ToString();
            }
           
            if ( Session["Area"] != null)
            {
                Area = Session["Area"].ToString();
            }
            DataTable dt = new DataTable();
            SqlConnection con = new SqlConnection(strConnString);
            SqlDataAdapter sda = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand("spx_Pager2");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@PageNo", SqlDbType.Int).Value = PageNo;
            cmd.Parameters.Add("@ItemsPerPage", SqlDbType.Int).Value = ItemsPerPage;
            cmd.Parameters.Add("@TotalRows", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = country;
            cmd.Parameters.Add("@Property", SqlDbType.Int).Value = Property;
            cmd.Parameters.Add("@state", SqlDbType.VarChar).Value = State;
            cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = City;
            cmd.Parameters.Add("@Area", SqlDbType.VarChar).Value = Area;
            cmd.Connection = con;
            try
            {
                con.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                //DataList1.DataBind
                DataList1.DataSource = dt;
                DataList1.DataBind();
                TotalRows = Convert.ToInt32(cmd.Parameters["@TotalRows"].Value);
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                con.Close();
                sda.Dispose();
                con.Dispose();
            }
            this.doPaging();
            return TotalRows;

        }

plz help me where m doing wrong

Answers (1)