存储过程
ELIMITER $$DROP PROCEDURE IF EXISTS `sitenews`.`p_pageList` $$
CREATE PROCEDURE `sitenews`.`p_pageList` (
m_pageNo int ,
m_perPageCnt int ,
m_column varchar(1000) ,
m_table varchar(1000) ,
m_condition varchar(1000),
m_orderBy varchar(200) ,
out m_totalPageCnt int)
BEGIN
SET @pageCnt = 1;
SET @limitStart = (m_pageNo - 1)*m_perPageCnt;
SET @limitEnd = m_perPageCnt;
SET @sqlCnt = CONCAT('select count(1) into @pageCnt from ',m_table);
SET @sql = CONCAT('select ',m_column,' from ',m_table);
IF m_condition IS NOT NULL AND m_condition <> '' THEN
SET @sql = CONCAT(@sql,' where ',m_condition);
SET @sqlCnt = CONCAT(@sqlCnt,' where ',m_condition);
END IF;
IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN
SET @sql = CONCAT(@sql,' order by ',m_orderBy);
END IF;
SET @sql = CONCAT(@sql, ' limit ', @limitStart, ',', @limitEnd);
PREPARE s_cnt from @sqlCnt;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
SET m_totalPageCnt = @pageCnt;
PREPARE record from @sql;
EXECUTE record;
DEALLOCATE PREPARE record;
END $$DELIMITER ;
后台: MySqlConnection mysqlconn = new MySqlConnection(conn);
mysqlconn.Open();
MySqlCommand comm = new MySqlCommand();
comm.Connection = mysqlconn;
comm.CommandText = "p_pageList";
comm.CommandType = CommandType.StoredProcedure;
MySqlParameter[] parameters ={
new MySqlParameter("?m_pageNo", MySqlDbType.Int32,4),
new MySqlParameter("?m_perPageCnt", MySqlDbType.Int32,4),
new MySqlParameter("?m_column", MySqlDbType.VarChar,1000),
new MySqlParameter("?m_table", MySqlDbType.VarChar,1000),
new MySqlParameter("?m_condition", MySqlDbType.VarChar,1000),
new MySqlParameter("?m_orderBy", MySqlDbType.VarChar,200),
new MySqlParameter("?m_totalPageCnt", MySqlDbType.Int32,4)
};
parameters[0].Value = pageIndex;
parameters[1].Value = pageSize;
parameters[2].Value = " * ";
parameters[3].Value = "news";
parameters[4].Value = "";
parameters[5].Value = "";
parameters[6].Direction = ParameterDirection.Output; for (int i = 1; i <= parameters.Length - 1; i++)
{
comm.Parameters.Add(parameters[i]);
}
MySqlDataAdapter Adapter = new MySqlDataAdapter(comm);
DataSet ds = new DataSet();
Adapter.Fill(ds);---此处提示Parameter '?m_pageNo' not found in the collection. mysqlconn.Close();
mysqlconn.Dispose();
recordCount = Convert.ToInt32(parameters[8].Value);//
return ds;
}
调用:
string conn = System.Configuration.ConfigurationSettings.AppSettings["connstr"];
int recordCount = 0;
int pageIndex = AspNetPager1.CurrentPageIndex;
int pageSize = AspNetPager1.PageSize;
DataSet ds = MYSQL_DAL.getdata(conn, pageIndex, pageSize, out recordCount);
this.listrepeater1.dt = ds.Tables[0];
this.AspNetPager1.RecordCount = recordCount;
请问是那个地方出错了
ELIMITER $$DROP PROCEDURE IF EXISTS `sitenews`.`p_pageList` $$
CREATE PROCEDURE `sitenews`.`p_pageList` (
m_pageNo int ,
m_perPageCnt int ,
m_column varchar(1000) ,
m_table varchar(1000) ,
m_condition varchar(1000),
m_orderBy varchar(200) ,
out m_totalPageCnt int)
BEGIN
SET @pageCnt = 1;
SET @limitStart = (m_pageNo - 1)*m_perPageCnt;
SET @limitEnd = m_perPageCnt;
SET @sqlCnt = CONCAT('select count(1) into @pageCnt from ',m_table);
SET @sql = CONCAT('select ',m_column,' from ',m_table);
IF m_condition IS NOT NULL AND m_condition <> '' THEN
SET @sql = CONCAT(@sql,' where ',m_condition);
SET @sqlCnt = CONCAT(@sqlCnt,' where ',m_condition);
END IF;
IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN
SET @sql = CONCAT(@sql,' order by ',m_orderBy);
END IF;
SET @sql = CONCAT(@sql, ' limit ', @limitStart, ',', @limitEnd);
PREPARE s_cnt from @sqlCnt;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
SET m_totalPageCnt = @pageCnt;
PREPARE record from @sql;
EXECUTE record;
DEALLOCATE PREPARE record;
END $$DELIMITER ;
后台: MySqlConnection mysqlconn = new MySqlConnection(conn);
mysqlconn.Open();
MySqlCommand comm = new MySqlCommand();
comm.Connection = mysqlconn;
comm.CommandText = "p_pageList";
comm.CommandType = CommandType.StoredProcedure;
MySqlParameter[] parameters ={
new MySqlParameter("?m_pageNo", MySqlDbType.Int32,4),
new MySqlParameter("?m_perPageCnt", MySqlDbType.Int32,4),
new MySqlParameter("?m_column", MySqlDbType.VarChar,1000),
new MySqlParameter("?m_table", MySqlDbType.VarChar,1000),
new MySqlParameter("?m_condition", MySqlDbType.VarChar,1000),
new MySqlParameter("?m_orderBy", MySqlDbType.VarChar,200),
new MySqlParameter("?m_totalPageCnt", MySqlDbType.Int32,4)
};
parameters[0].Value = pageIndex;
parameters[1].Value = pageSize;
parameters[2].Value = " * ";
parameters[3].Value = "news";
parameters[4].Value = "";
parameters[5].Value = "";
parameters[6].Direction = ParameterDirection.Output; for (int i = 1; i <= parameters.Length - 1; i++)
{
comm.Parameters.Add(parameters[i]);
}
MySqlDataAdapter Adapter = new MySqlDataAdapter(comm);
DataSet ds = new DataSet();
Adapter.Fill(ds);---此处提示Parameter '?m_pageNo' not found in the collection. mysqlconn.Close();
mysqlconn.Dispose();
recordCount = Convert.ToInt32(parameters[8].Value);//
return ds;
}
调用:
string conn = System.Configuration.ConfigurationSettings.AppSettings["connstr"];
int recordCount = 0;
int pageIndex = AspNetPager1.CurrentPageIndex;
int pageSize = AspNetPager1.PageSize;
DataSet ds = MYSQL_DAL.getdata(conn, pageIndex, pageSize, out recordCount);
this.listrepeater1.dt = ds.Tables[0];
this.AspNetPager1.RecordCount = recordCount;
请问是那个地方出错了
这里的错误,
你仔细想想