I am trying to submit a form that contains a few datetimepickers as well as other controls. other controls have no problem but when I take the datetimepicker date value and put it in a parameterised insert statement, this error happens:
Conversion failed when converting date and/or time from character string
I have tried the following methods:
custom datetime format:
dateTimePicker1.Format = DateTimePickerFormat.Custom; dateTimePicker1.CustomFormat = "dd/MM/yyyy";'convert it to string and then into date time using datetime.parseexact, datetime.parse and Convert.ToDateTime.
tried this type of code as well:
DateTime date2 = Convert.ToDateTime(dateTimePicker2.Text);
Full code
con.Open();
string s = "insert into [NutritionClinicDatabase].[dbo].[PatientInformation] values(@NIC, @ClinicNo, @PatientCategory, @Date, @Name, @Gender, @DOB, @DateOfAdmission, @DateOfDischarge, @DurationOfVisit, @Age, @City, @Contact, @Height, @Weight, @UsualWweight, @ReferredUnit, @WC, @FatMass, @LeanBodyMass, @HandGripStrength, @Intake, @Illness, @Illness2, @Illness3, @Illness4, @Specify, @Other)";
cmd = new SqlCommand(s, con);
String strDateFormat = "yyyy-MM-dd";
cmd.Parameters.AddWithValue("@NIC", NIC);
cmd.Parameters.AddWithValue("@ClinicNo", ClinicNo);
cmd.Parameters.AddWithValue("@PatientCategory", PatientCategory);
if (PatientCategory == "Clinic Patient")
{
cmd.Parameters.AddWithValue("@Date", SqlDbType.Date).Value = dateTimePicker1.Value.Date;
}
else if ((PatientCategory == "In-Ward Patient") || (PatientCategory == "ICU Patient"))
{
cmd.Parameters.AddWithValue("@Date", DBNull.Value);
}
cmd.Parameters.AddWithValue("@Name", Name1);
cmd.Parameters.AddWithValue("@Gender", Gender);
//if (dateTimePicker2.CustomFormat == " ")
// cmd.Parameters.AddWithValue("@DOB", DBNull.Value);
//else
cmd.Parameters.AddWithValue("@DOB", SqlDbType.Date).Value = dateTimePicker2.Value.Date;
// cmd.Parameters.AddWithValue("@DOB", SqlDbType.Date).Value = dateTimePicker2.Value.Date;
if ((PatientCategory == "In-Ward Patient") || (PatientCategory == "ICU Patient"))
{
//if (dateTimePicker3.CustomFormat == " ")
// cmd.Parameters.AddWithValue("@DateOfAdmission", DBNull.Value);
//else
cmd.Parameters.AddWithValue("@DateOfAdmission", SqlDbType.Date).Value = dateTimePicker3.Value.Date;
// cmd.Parameters.AddWithValue("@DOB", SqlDbType.Date).Value = dateTimePicker2.Value.Date;
//if (dateTimePicker4.CustomFormat == " ")
// cmd.Parameters.AddWithValue("@DateOfDischarge", DBNull.Value);
//else
cmd.Parameters.AddWithValue("@DateOfDischarge", SqlDbType.Date).Value = dateTimePicker4.Value.Date;
//cmd.Parameters.AddWithValue("@DateOfDischarge", DateTime.Parse(dateTimePicker4.Value.Date.ToString()));
// cmd.Parameters.AddWithValue("@DOB", SqlDbType.Date).Value = dateTimePicker2.Value.Date;
cmd.Parameters.AddWithValue("@DurationOfVisit", Duration);
}
else if (PatientCategory == "Clinic Patient")
{
cmd.Parameters.AddWithValue("@DateOfAdmission", DBNull.Value);
cmd.Parameters.AddWithValue("@DateOfDischarge", DBNull.Value);
cmd.Parameters.AddWithValue("@DurationOfVisit", "Not Specified");
}
cmd.Parameters.AddWithValue("@Age", Age);
cmd.Parameters.AddWithValue("@City", City);
cmd.Parameters.AddWithValue("@Contact", Contact);
cmd.Parameters.AddWithValue("@Height", Height1);
cmd.Parameters.AddWithValue("@Weight", Weight);
cmd.Parameters.AddWithValue("@UsualWweight", UsualWeight);
//cmd.Parameters.AddWithValue("@CurrentWeight", CurrentWeight);
cmd.Parameters.AddWithValue("@ReferredUnit", ReferredUnit);
cmd.Parameters.AddWithValue("@WC", WC);
cmd.Parameters.AddWithValue("@FatMass", FatMass);
cmd.Parameters.AddWithValue("@LeanBodyMass", LeanBodyMass);
cmd.Parameters.AddWithValue("@HandGripStrength", HandGripStrength);
cmd.Parameters.AddWithValue("@Intake", Intake);
cmd.Parameters.AddWithValue("@Illness", Illness);
cmd.Parameters.AddWithValue("@Illness2", Illness2);
cmd.Parameters.AddWithValue("@Illness3", Illness3);
cmd.Parameters.AddWithValue("@Illness4", Illness4);
cmd.Parameters.AddWithValue("@Specify", Specify);
cmd.Parameters.AddWithValue("@Other", Other);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Patient Registered");
