TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
1
)
Regex pattern UNMATCH in VB.NET or C#
History Name