c# - 在集合中找不到参数 'daterange'

Question

我在调用数据库中的存储过程时遇到问题

这是我数据库中的存储过程

DELIMITER $$

USE `sample`$$

DROP PROCEDURE IF EXISTS `sp_ReturnAttendanceInfo`$$

CREATE DEFINER=`root`@`192.168.%` PROCEDURE `sp_ReturnAttendanceInfo`(IN uname INT(4), IN daterange DATETIME)
BEGIN
        SELECT shift_time.in, shift_time.out, MIN(perf_prog.start_time) AS start_time, MAX(perf_prog.end_time) AS end_time, perf_prog.date FROM perf_prog INNER JOIN Shifts ON perf_prog.emp_id = Shifts.emp_id 
INNER JOIN shift_time ON Shifts.id = shift_time.id

WHERE perf_prog.emp_id = uname  AND  DATE(`date`) >= daterange

GROUP BY  `date` ORDER BY `date` ;

END$$

DELIMITER ;

当我在数据库查询中调用这个存储过程时像这样

CALL sp_ReturnAttendanceInfo(0921, '2017-04-02')

它返回一个输出

但是当我在我的 C# 中调用它时

using (MySqlConnection connection = new MySqlConnection(mysqlConnection))
{
    connection.Open();
    if (connection.State == System.Data.ConnectionState.Open)
    {
        using (MySqlCommand cmd = new MySqlCommand("sp_ReturnAttendanceInfo", connection))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@uname", username);
            cmd.Parameters.AddWithValue("@date", date.ToString("yyyy-MM-dd"));
            using (MySqlDataReader dataReader = cmd.ExecuteReader())
            {
                while (dataReader.Read())
                {

                }
            }
        }
    }
}

我什至检查了我传递的参数:

921
2017-04-04

但是我还是没能解决这个问题。我什至在创建问题之前搜索问题，但我无法解决它。错误信息还是一样

这是错误:

Parameter 'daterange' not found in the collection.
   at MySql.Data.MySqlClient.MySqlParameterCollection.GetParameterFlexible(String parameterName, Boolean throwOnNotFound)
   at MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, MySqlSchemaRow param, Boolean realAsFloat, MySqlParameter returnParameter)
   at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
   at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()

Answer 1

传递到存储过程的参数和接受这些值的参数必须相同，因此需要更改:

cmd.Parameters.AddWithValue("@date", date.ToString("yyyy-MM-dd"));

到

cmd.Parameters.AddWithValue("@daterange", date.ToString("yyyy-MM-dd"));