//下面是我的存储过程,我call过存储过程了,是没有问题的.
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_SearchWordNote`( /* 2007 11 19 查询留言信息 */
in WordID int,
in BegTime varchar(20),
in EndTime varchar(20)
)
Begin
/*declare WordID int;
declare BegTime varchar(20);
declare EndTime varchar(20);*/
declare BegDate datetime;
declare EndDate datetime;
if( BegTime = "0" or BegTime = "") then
set BegDate = DATE_ADD(current_Date(),interval -10 year);
else
set BegDate = CAST( BegTime as datetime );
end if;
if( EndTime = "0" or EndTime = "") then
set EndDate = CURRENT_DATE();
else
set EndDate = cast( EndTime as datetime );
End if;
if( WordID > 0) then
update BaySpeed.KF_WordNote set WN_SeeOk = 1 where WN_ID = WordID;
select * from BaySpeed.KF_WordNote where WN_ID = WordID;
else
select * from BaySpeed.P_WordListView where (WN_AddTime >= BegDate) and ( WN_AddTime <= EndDate );
End if;
END$$//下面是.net中调用的代码
sqlConn.Open();
MySqlCommand myComm = new MySqlCommand("P_SearchWordNote", sqlConn);
myComm.CommandType = CommandType.StoredProcedure;
myComm.Connection = sqlConn;
myComm.CommandText = "P_SearchWordNote";
MySqlParameter P1 = new MySqlParameter("?WordID", MySqlDbType.Int32);
P1.Value = WordID;
P1.Direction = ParameterDirection.Input;
myComm.Parameters.Add(P1);
//myComm.Parameters.Add(new MySqlParameter("WordID", WordID));
//myComm.Parameters.Add(new MySqlParameter("BegTime", BegTime));
//myComm.Parameters.Add(new MySqlParameter("EndTime", EndTime));
MySqlParameter P2 = new MySqlParameter("?BegTime", MySqlDbType.VarChar, 20);
P2.Value = BegTime;
P2.Direction = ParameterDirection.Input;
myComm.Parameters.Add(P2);
MySqlParameter P3 = new MySqlParameter("?EndTime", MySqlDbType.VarChar, 20);
P3.Value = EndTime;
P3.Direction = ParameterDirection.Input;
myComm.Parameters.Add(P3);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = myComm;
adapter.Fill(ds);
sqlConn.Close();
return ds;
//运行后就出现这个错误了(万分感激,如果去掉注释就没有问题了)
Parameter '?/*' not found in the collection
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_SearchWordNote`( /* 2007 11 19 查询留言信息 */
in WordID int,
in BegTime varchar(20),
in EndTime varchar(20)
)
Begin
/*declare WordID int;
declare BegTime varchar(20);
declare EndTime varchar(20);*/
declare BegDate datetime;
declare EndDate datetime;
if( BegTime = "0" or BegTime = "") then
set BegDate = DATE_ADD(current_Date(),interval -10 year);
else
set BegDate = CAST( BegTime as datetime );
end if;
if( EndTime = "0" or EndTime = "") then
set EndDate = CURRENT_DATE();
else
set EndDate = cast( EndTime as datetime );
End if;
if( WordID > 0) then
update BaySpeed.KF_WordNote set WN_SeeOk = 1 where WN_ID = WordID;
select * from BaySpeed.KF_WordNote where WN_ID = WordID;
else
select * from BaySpeed.P_WordListView where (WN_AddTime >= BegDate) and ( WN_AddTime <= EndDate );
End if;
END$$//下面是.net中调用的代码
sqlConn.Open();
MySqlCommand myComm = new MySqlCommand("P_SearchWordNote", sqlConn);
myComm.CommandType = CommandType.StoredProcedure;
myComm.Connection = sqlConn;
myComm.CommandText = "P_SearchWordNote";
MySqlParameter P1 = new MySqlParameter("?WordID", MySqlDbType.Int32);
P1.Value = WordID;
P1.Direction = ParameterDirection.Input;
myComm.Parameters.Add(P1);
//myComm.Parameters.Add(new MySqlParameter("WordID", WordID));
//myComm.Parameters.Add(new MySqlParameter("BegTime", BegTime));
//myComm.Parameters.Add(new MySqlParameter("EndTime", EndTime));
MySqlParameter P2 = new MySqlParameter("?BegTime", MySqlDbType.VarChar, 20);
P2.Value = BegTime;
P2.Direction = ParameterDirection.Input;
myComm.Parameters.Add(P2);
MySqlParameter P3 = new MySqlParameter("?EndTime", MySqlDbType.VarChar, 20);
P3.Value = EndTime;
P3.Direction = ParameterDirection.Input;
myComm.Parameters.Add(P3);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = myComm;
adapter.Fill(ds);
sqlConn.Close();
return ds;
//运行后就出现这个错误了(万分感激,如果去掉注释就没有问题了)
Parameter '?/*' not found in the collection
/* 2007 11 19 查询留言信息 */ 放在CREATE 的前一行试试。
delimiter ||
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_SearchWordNote`( /* 2007 11 19 查询留言信息 */
in WordID int,
in BegTime varchar(20),
in EndTime varchar(20)
)
Begin
/*declare WordID int;
declare BegTime varchar(20);
declare EndTime varchar(20);*/
declare BegDate datetime;
declare EndDate datetime;
if BegTime = "0" or BegTime = "" then
set BegDate = DATE_ADD(current_Date(),interval -10 year);
else
set BegDate = CAST( BegTime as datetime );
end if;
if EndTime = "0" or EndTime = "" then
set EndDate = CURRENT_DATE();
else
set EndDate = cast( EndTime as datetime );
End if;
if WordID > 0 then
update BaySpeed.KF_WordNote set WN_SeeOk = 1 where WN_ID = WordID;
select * from BaySpeed.KF_WordNote where WN_ID = WordID;
else
select * from BaySpeed.P_WordListView where (WN_AddTime >= BegDate) and ( WN_AddTime <= EndDate );
End if;
END||
delimiter ;
(0 ms taken)
我知道原因是出现在注释上,删除就没错了,这样的话其他
注释都要手动改了,真的比较麻烦的.我没有使用过mysql(平常用T-Sql),
mysql居然对注释还有这么严格的规定,郁闷.那我先叫他放在前一行,看看怎么样.
骑着上帝看戏
-------------
请问你的回复是什么意思?我之前说过了,这个存储过程本身是没有问题的,直接call存储过程是可以得到结果的.
只是结合到.net就出现我刚才上面的毛病,我也知道是注释的原因.想请教的是,mysql对注释位置有特别要求?不像我们习惯的c风格那样?
现在只能用折中的办法,把参数的注释都移动
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_SearchWordNote`( /* 2007 11 19 查询留言信息 */
in WordID int,
in BegTime varchar(20),
in EndTime varchar(20)
)
Begin
到这个begin之后了.
这样就是无法实现直接在每个参数后面做注释了.
怀疑是.net和mysql的不大兼容的原因.