Exception Unhandled - MySql.Data.MySqlClient.MySqlException

Question

I was gonna save my date and time record in my database when an unhandled exception always thrown at this code: int value = cmd.ExecuteNonQuery(); when I'm clicking the 'Time In' button.

it says that MySql.Data.MySqlClient.MySqlException 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Mar 2022 3:53:00 AM,System.Windows.Forms.DateTimePicker, Value

fig.1 Exception Unhandled every time

When I check my codes, there's no errors. I debugged it but no errors appeared.

Here's my codes:

private void btnTimeIn_Click(object sender, EventArgs e)
        {
            string connectstring = "datasource=localhost;port=3306;username=root;password=;database=employeedb;SslMode=none";
            MySqlConnection conn = new MySqlConnection(connectstring);
            conn.Open();
            string iQuery = "INSERT INTO `attendancerecord`(`EmployeeID`, `EmplLastName`, `EmplFirstName`, `RecordDate`, `TimeIn`, `TimeOut`) VALUES (" + txtEmployeeID.Text + "," + txtLstName.Text + "," + txtFirstName.Text + "," + dateTimePicker1.Value + "," + dateTimePicker2 + "," + dateTimePicker3 + ")";
            MySqlCommand cmd = new MySqlCommand(iQuery, conn);
            int value = cmd.ExecuteNonQuery();
            MessageBox.Show(value.ToString());
            conn.Close();

        }

Your immediate help, tips and advice are highly appreciated

I was gonna expect that I'm gonna save records in my database by timing in... but I can't even find what could be wrong because i just did all ways of inserting data into table in database. Still, I didn't also work, and the exception still throwing every time at the 'cmd.ExecuteNonQuery' line.

you need single quotes around al values, but better is to switch to preprared statements https://stackoverflow.com/questions/11070434/using-prepared-statement-in-c-sharp-with-mysql — nbk, Mar 23 '22 at 21:56
Yes sir. I tried copying the value assigned to iQuery and run it onto the databaes directly. and it worked. — IMCP30, Mar 23 '22 at 22:03
"When I check my codes, there's no errors." If your program isn't working properly, I promise you there are errors in the code. — Bradley Grainger, Mar 24 '22 at 05:51

score 0 · Answer 1 · answered Mar 24 '22 at 05:50

This line of code is causing the bug, and can also lead to SQL injection:

string iQuery = "INSERT INTO `attendancerecord`(`EmployeeID`, `EmplLastName`, `EmplFirstName`, `RecordDate`, `TimeIn`, `TimeOut`) VALUES (" + txtEmployeeID.Text + "," + txtLstName.Text + "," + txtFirstName.Text + "," + dateTimePicker1.Value + "," + dateTimePicker2 + "," + dateTimePicker3 + ")";

You should always use command parameters, not string concatenation:

using (MySqlConnection conn = new MySqlConnection(connectstring))
{
    conn.Open();
    string iQuery = @"
INSERT INTO `attendancerecord`(`EmployeeID`, `EmplLastName`, `EmplFirstName`,
  `RecordDate`, `TimeIn`, `TimeOut`)
VALUES (@id, @last, @first, @date, @in, @out);";
    using MySqlCommand cmd = new MySqlCommand(iQuery, conn))
    {
        cmd.Parameters.AddWithValue("@id", txtEmployeeID.Text);
        cmd.Parameters.AddWithValue("@first", txtLstName.Text);
        cmd.Parameters.AddWithValue("@last", txtFirstName.Text);
        cmd.Parameters.AddWithValue("@date", dateTimePicker1.Value);
        cmd.Parameters.AddWithValue("@in", dateTimePicker2.Value);
        cmd.Parameters.AddWithValue("@out", dateTimePicker3.Value);

        int value = cmd.ExecuteNonQuery();
    }
}

Additionally, use using statements to automatically close and clean up database resources.

Exception Unhandled - MySql.Data.MySqlClient.MySqlException

1 Answers1