I am trying to insert values from a datetimepicker into a database, but I get an unhandled exception

Conversion failed when converting date and/or time from character string

This is the code I used:

private void bunifuFlatButton8_Click(object sender, EventArgs e)
{
    if (OrderID.Text == "" || CustomerID.Text == "" || CustName.Text == "" || AmountTB.Text =="")
    {
        MessageBox.Show("Fill the data correctly");
    }
    else
    {
        SqlCommand cmd = new SqlCommand("insert into OrderTB values("   OrderID.Text   ","   CustomerID.Text   ",'"   CustName.Text   "','"   OrderDate.Text   "',"   AmountTB.Text   ")", Con);
        Con.Open();

        cmd.ExecuteNonQuery();

        MessageBox.Show("Order successfully added");

        Con.Close();
        populate();
    } 
}

Any help will be appreciated.

CodePudding user response：

As@Ňɏssa Pøngjǣrdenlarp mentioned in the comment you have to use the Value in the proper format, which should be DateTime.

You have a couple of issues in your code, let me highlight them below.

1. you should use using with the connection to dispose of them.
2. Put your code properly in the try-catch, not like it's mentioned in the code. You should use parameterized queries to avoid SQL injection.
3. Handle the null case as well if the Text is null.
4. you can also utilize the return value of ExecuteNonQuery.

CodePudding user response：

You can use the code below. If you want to use try and catch use it to get the error and Instead of OrderDate.Text use OrderDate.Value to get DateTime value and convert it to string in desired format as below

private void bunifuFlatButton8_Click(object sender, EventArgs e)
{
    if (OrderID.Text == "" || CustomerID.Text == "" || CustName.Text == "" || AmountTB.Text =="")
    {
        MessageBox.Show("Fill The data Correctly");
    }
    else
    {
        try
        {
            //Instead of OrderDate.Text use OrderDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff") then you will have correct format for SQL Insert
            using(SqlCommand cmd = new SqlCommand("insert into OrderTB values("   OrderID.Text   ","   CustomerID.Text   ",'"   CustName.Text   "','"   OrderDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff")   "',"   AmountTB.Text   ")", Con))
            {
                Con.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Order Successfully Added");
                Con.Close();
                populate();
            }
        }
        catch(Exception ex)
        {
            MessageBox.Show("I got an error : "   ex.Message); // This will give you error message instead of stopping your program
        }
    } 
}