Exception:Failed to convert parameter value from a string to a datetime

Question

I tried to insert a date into database using DateTimePickerwith a help of a SQL Server procedure and C#. But when I execute my program showing following exception:

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.

I can't fix this.

My procedure is:

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

Also this is my C# code for inserting to a table using above procedure:

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();

Please help me......

Answer 1

You have given LeaveType as DateTime while it should be varchar.

When you assign the value to this parameter you are assigning a string while you have given its type as DateTime so it raises exception.

Also your SP expects @LeaveTpye as nvarchar so you should change its type to varchar

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

It should be

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