من این کار رو کردم اما این ارور رو می ده :
Must declare the scalar variable "@title"
ممنون می شم اگه کلا کدهام رو یک نگاه بندازی :
sp :
USE [DIGlibrary]
GO
/****** Object: StoredProcedure [dbo].[BookCastumPaging] Script Date: 07/14/2012 18:34:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BookCastumPaging]
(
@SqlTableName nvarchar(100),
@PageIndex int,
@title nvarchar(200),
@PageSize int
)
AS
BEGIN
declare @SQLText nvarchar(500)
--set@SqlTableName = 'book'
--set@PageIndex = 0
--set@PageSize = 10
set @SQLText = 'SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY bookid) as RowNum FROM '+@SqlTableName+' WHERE title = @title ) as DerivedTableName WHERE RowNum BETWEEN '+ str(@PageIndex)+' AND'+ str((@PageIndex+ @PageSize) - 1)
exec(@SQLText)
END
paging.cs :
public class paging
{
public paging()
{
}
public string ConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings["constr"].ToString();
}
}
public DataSet ExecuteDataset(string title,string SQLTableName, int PageIndex, int PageSize)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[BookCastumPaging]";
cmd.Parameters.AddWithValue("@SqlTableName", SQLTableName);
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@PageIndex", PageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
con.Open();
da.Fill(ds);
con.Close();
return ds;
}
}
public int countall(string SQLTableName, string title)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
con.Open();
string commandText55 = "select count(bookid)as mm from " + SQLTableName + " where title = @title ";
SqlCommand cmdObj55 = new SqlCommand(commandText55, con);
SqlDataReader ddr = cmdObj55.ExecuteReader();
ddr.Read();
int code22;
if (ddr.HasRows == true)
{
code22 = int.Parse(ddr["mm"].ToString());
con.Close();
return code22;
}
else
con.Close();
return 0;
}
}
}
تو صفحه book.aspx جایی که گریدویو قراره بیاد :
#region Property
protected int PageIndex
{
get
{
object value = this.ViewState["PageIndex"];
if (value != null)
return System.Convert.ToInt32(value);
else
return 1;
}
set
{
this.ViewState["PageIndex"] = value;
}
}
protected int PageCount
{
get
{
object value = this.ViewState["PageCount"];
if (value != null)
return System.Convert.ToInt32(value);
else
return 1;
}
set
{
this.ViewState["PageCount"] = value;
}
}
protected int PageSize
{
get
{
object value = this.ViewState["PageSize"];
if (value != null)
return System.Convert.ToInt32(value);
else
return 1;
}
set
{
this.ViewState["PageSize"] = value;
}
}
public string SQLTableName
{
get
{
object value = this.ViewState["SQLTableName"];
if (value != null)
return value.ToString();
else
return null;
}
set
{
this.ViewState["SQLTableName"] = value;
}
}
public string title
{
get
{
object value = this.ViewState["title"];
if (value != null)
return value.ToString();
else
return null;
}
set
{
this.ViewState["title"] = value;
}
}
#endregion
public void DataBindGrid()
{
GridView1.DataSource = ADO.ExecuteDataset(title,SQLTableName, PageIndex, PageSize);
GridView1.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["title"] != null)
{
if (!IsPostBack)
{
SQLTableName = "[book]";
title = Request.QueryString["title"];
PageSize = 10;
PageIndex = 1;
PageCount = Convert.ToInt32(Math.Ceiling((double)ADO.countall( SQLTableName, title) / (double)PageSize));
DataBindGrid();
}
}
}