I have been trying to figure out how to add current datetime from c# into mysql column and that attribute/column is also in 'datetime' format. I am writing this code.
if (comboBox1.Text == "Cash On Delivery")
{
MessageBox.Show("Your order has been placed.","",MessageBoxButtons.OK);
string timeString = DateTime.Now.ToString("YYYY-MM-DD HH:MM:SS");
string constring = "datasource=localhost;port=3306;username=root;password=root";
string query = "insert into artgallery.payment_method where payment_method ='" +comboBox1.Text+ "' AND payment_date='" +formatForMySql+ "' AND payment_amount = '" +variables.total_amount+ "' ";
//rest of the code
}
I get this some syntax error related to the timeString Im trying to insert in my 'payment_date' column.
The right answer is to stop building your SQL like that to start with. Use parameterized SQL, then specify the DateTime
value as the parameter value - you don't need a string representation at all in your code.
It's not immediately clear which driver you're using (there are two for MySQL, IIRC) but you should look at the Parameters
property of whichever command type you're using.
It's really important to use parameterized SQL - not just to avoid conversion issues like this, but also to prevent SQL Injection Attacks. It also makes your code considerably simpler to read, in my view.
See more on this question at Stackoverflow