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