c# - 异常 :Failed to convert parameter value from a string to a datetime

Question

我尝试在 SQL Server 过程和 C# 的帮助下使用 DateTimePicker 将日期插入数据库。但是当我执行我的程序时显示以下异常:

System.FormatException: Failed to convert parameter value from a String to a DateTime. ---> System.FormatException: The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.

我无法解决这个问题。

我的程序是:

create procedure [dbo].[LeaveProcedure]
    (@EmployeeID nvarchar(20),
     @EmployeeName nvarchar(50),
     @LeaveType nvarchar(20),
     @leaveCode nvarchar(20),
     @Leavedate date,
     @TotalLeave int,
     @LeaveTaken int,
     @Balance int,
     @Reason varchar(500)
)
AS
    DECLARE @Max INT, @id varchar(10)

    IF NOT EXISTS(SELECT id FROM LeaveMaster)
    BEGIN
        SET @id = 'EL001'                     

        INSERT INTO dbo.EmployeeLeave(LeaveID, EmployeeID, EmployeeName, LeaveType, leaveCode, Leavedate, TotalLeave, LeaveTaken, Balance, Reason)
        VALUES(@id, @EmployeeID, @EmployeeName, @LeaveType, @leaveCode, @Leavedate, @TotalLeave, @LeaveTaken, @Balance, @Reason)         
    END
    ELSE
    BEGIN
        SELECT @Max = CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(10),id), 3, 10)) 
        FROM LeaveMaster

        SET @id = 'EL' + RIGHT('00' + CONVERT(NVARCHAR(10), @Max + 1), 5)

        INSERT INTO   dbo.EmployeeLeave(LeaveID, EmployeeID, EmployeeName, LeaveType, leaveCode, Leavedate, TotalLeave, LeaveTaken, Balance, Reason)
        VALUES(@id, @EmployeeID, @EmployeeName, @LeaveType, @leaveCode, @Leavedate, @TotalLeave, @LeaveTaken, @Balance, @Reason)         
    END

这也是我使用上述过程插入到表中的 C# 代码:

SqlCommand Leave = new SqlCommand();

SqlParameter EmployeeID = new SqlParameter("@EmployeeID", SqlDbType.VarChar);
SqlParameter EmployeeName = new SqlParameter("@EmployeeName", SqlDbType.VarChar);
SqlParameter LeaveType = new SqlParameter("@LeaveType", SqlDbType.DateTime);
SqlParameter leaveCode = new SqlParameter("@leaveCode", SqlDbType.VarChar);
SqlParameter Leavedate = new SqlParameter("@Leavedate", SqlDbType.Date);
SqlParameter TotalLeave = new SqlParameter("@TotalLeave", SqlDbType.Int);
SqlParameter LeaveTaken = new SqlParameter("@LeaveTaken", SqlDbType.Int);
SqlParameter Balance = new SqlParameter("@Balance", SqlDbType.Int);
SqlParameter Reason= new SqlParameter("@Reason", SqlDbType.VarChar);
SqlParameter LeaveID = new SqlParameter("@LeaveID", SqlDbType.VarChar);

Leave.Parameters.Add(EmployeeID);
Leave.Parameters.Add(EmployeeName);
Leave.Parameters.Add(LeaveType);
Leave.Parameters.Add(leaveCode);
Leave.Parameters.Add(Leavedate);
Leave.Parameters.Add(TotalLeave);
Leave.Parameters.Add(Balance);
Leave.Parameters.Add(Reason);

EmployeeID.Value = label_leave_emp_id.Text;
EmployeeName.Value = label_leave_emp_name.Text;
LeaveType.Value = comboBox_leavetype.Text;
leaveCode.Value = s2.ToString();
 SqlParameter Leavedate = new SqlParameter("@Leavedate", SqlDbType.Date);
                SqlParameter TotalLeave = new SqlParameter("@TotalLeave", SqlDbType.Int);
                SqlParameter LeaveTaken = new SqlParameter("@LeaveTaken", SqlDbType.Int);
                SqlParameter Balance = new SqlParameter("@Balance", SqlDbType.Int);
                SqlParameter Reason= new SqlParameter("@Reason", SqlDbType.VarChar);
                SqlParameter LeaveID = new SqlParameter("@LeaveID", SqlDbType.VarChar);

                Leave.Parameters.Add(LeaveID);
                Leave.Parameters.Add(EmployeeID);
                Leave.Parameters.Add(EmployeeName);
                Leave.Parameters.Add(LeaveType);
                Leave.Parameters.Add(leaveCode);
                Leave.Parameters.Add(Leavedate);
                Leave.Parameters.Add(LeaveTaken);
                Leave.Parameters.Add(TotalLeave);
                Leave.Parameters.Add(Balance);
                Leave.Parameters.Add(Reason);

                EmployeeID.Value = label_leave_emp_id.Text;
                EmployeeName.Value =label_leave_emp_name.Text;
                LeaveType.Value = comboBox_leavetype.Text;
                leaveCode.Value = s2.ToString();

                Leavedate.Value = dateTimePicker_leave_date.Value;
                TotalLeave.Value = Convert.ToInt32(totalleave.ToString());
                LeaveTaken.Value = leavetaken;
                Balance.Value = totalleave - leavetaken;

                SqlCommand ccc = new SqlCommand("select LeaveID from EmployeeLeave", con);

                SqlDataReader r1 = ccc.ExecuteReader();
                while (r1.Read())
                {
                    s2 = r1["LeaveID"].ToString();
                }
                if (s2 == null)
                    LeaveID.Value = " ";
                else if (s2 != null)
                    LeaveID.Value = s2.ToString();
                r1.Close();
                Reason.Value = textBox_leave_reason.Text;
                Leave.Connection = con;

                Leave.CommandText = "LeaveProcdure @EmployeeID, @EmployeeName, @LeaveType, @leaveCode, @Leavedate, @TotalLeave, @LeaveTaken, @Balance, @Reason";

      Leave.ExecuteNonQuery();

请帮帮我......

Answer 1

您已将 LeaveType 指定为 DateTime，而它应该是 varchar。

当您将值分配给此参数时，您正在分配一个 string，同时您将其类型指定为 DateTime，因此它会引发 exception。

此外，您的 SP 期望 @LeaveTpye 为 nvarchar，因此您应该将其类型更改为 varchar

SqlParameter LeaveType = new SqlParameter("@LeaveType", SqlDbType.DateTime);

应该是

SqlParameter LeaveType = new SqlParameter("@LeaveType", SqlDbType.VarChar);