DataTimePicker and Checkbox insert to database - Error SQL Syntax

Question

I'm programming in C#.NET and in MySQL. My database is named "projekt1". I've created new form InsertStatus.cs which contains: 3 textboxes, 1 numericUpDown, 2 dataTimePickers and 2 checkboxes.

I have connection with local database. I wrote SQL with INSERT INTO Correctly but it couldn't write that date and time do this database. And shows that is "an error SQL syntax". I've tried with .Value.ToString() and .Value and it didn't work. I would like to change customFormat in dataTimePickers to DD-MM-YYYY HH:MM (I mean in settings on the right site not in code) but i'm not sure if it could work. In below there is this code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace KontrolaBazaDanych

{
    public partial class InsertStatus : Form
    {
        public InsertStatus()
        {
            InitializeComponent();
        }


    private void button2_Click(object sender, EventArgs e)
    {
        this.Close();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=");
        string InsertQuery = "INSERT INTO project1.status_order(ID_ORDER, ID_WORKER, ID_MODULE, AMOUNT_OF_PRODUCTS, DATE_OF_START, DATE_OF_END, ORDER_DONE, ORDER_STARTED) VALUES('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + numericUpDown1.Value + "','" + dateTimePicker1.Value.ToString() + "','" + dateTimePicker2.Value.ToString() + "','" + checkBox1.ToString() + "','" + checkBox2.ToString() + "')'";
        connection.Open();
        MySqlCommand command = new MySqlCommand(InsertQuery, connection);

            if (command.ExecuteNonQuery() == 1)
            {
                MessageBox.Show("DATA IS ADDED.");
            }
            else
            {
                MessageBox.Show("ERROR.");
            }
        connection.Close();
    }

// SQL COLUMNS IN status_order table:

ID_ORDER INT,
ID_WORKER INT,
ID_MODULE INT, 
AMOUNT_OF_PRODUCTS INT, 
DATE_OF_START DATETIME, 
DATE_OF_END DATETIME, 
ORDER_DONE BOOLEAN, 
ORDER_STARTED BOOLEAN;

I have no idea what i should do. Can someone please explain how can i change. Thank you for any help or advice.

related: https://stackoverflow.com/a/29183034/5346438 – Mario Mitterbauer Feb 07 '19 at 16:35 — Mario Mitterbauer, Feb 07 '19 at 16:35
is it not supposed to be datepicker.SelectedDate ? – aspxsushil Feb 07 '19 at 16:35 — aspxsushil, Feb 07 '19 at 16:35

score 0 · Accepted Answer · answered Feb 07 '19 at 16:38

0

You should do this in the Code

dateTimePicker1.Value.ToString("dd-MM-YYYY hh:mm")

answered Feb 07 '19 at 16:38

Soumen Mukherjee

2,953
3
22
34

hmm... what about `checkbox1`? Should be `checkbox1.Value.Tostring()` too? – Prochu1991 Feb 07 '19 at 17:42

score 0 · Answer 2 · answered Feb 07 '19 at 16:57

0

Maybe the reason is:

dateTimePicker1.Value.ToString()

You convert your date to string even though your database has datetime columns

DATE_OF_START DATETIME, 
DATE_OF_END DATETIME,

answered Feb 07 '19 at 16:57

SAR

180
1
9

hmm... what about `checkbox1`? Should be `checkbox1.Value.Tostring()` too? – Prochu1991 Feb 07 '19 at 17:43
I would try to write bool to the data base as first resort and cast it to string later if it's not working. did it fix your problem btw? – SAR Feb 07 '19 at 17:52

DataTimePicker and Checkbox insert to database - Error SQL Syntax

2 Answers2