How to convert C# DateTime to MySQL timestamp table column

Question

I'm trying to update a table element of type timestamp called dtprint with the current time (the original value is NULL). The code that I am using is as follows:

MySqlConnection con = new MySqlConnection("Connection_String");
con.Open();
MySqlCommand _cmd = con.CreateCommand();

string dt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

_cmd.CommandText = "UPDATE requests SET dtprint = " + dt + " WHERE idPerson = " + _personID[index];

_cmd.ExecuteNonQuery();

con.Close();

The exception I keep getting is: Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '14:03:23 WHERE idPerson = 45' at line 1.

The only thing I can think of is that the Database isn't recognizing the time as a timestamp, any help is greatly appreciated.

What are the types of dtprint and idPerson columns? And you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. If your dtprint is a timestamp, you should add it a DateTime, not string. — Soner Gönül
– Soner Gönül, Commented Oct 29, 2014 at 18:08
do you have access to convert the UPDATE command to a stored procedure..? if so do that..also I would recommend converting the UPDATE statement to utilize Parameterized query and wrap the _cmd.ExecuteNonQuery(); around a try{}catch(SqlException sqlEx{show the sqlEx.Message here} so that you can capture the SQLException — MethodMan
– MethodMan, Commented Oct 29, 2014 at 18:15

Soner Gönül · Accepted Answer · 2014-10-30 11:59:11Z

2

Since dt is a string and your dtprint is timestamp, you need to use single quotes when you try to insert it. Like;

"UPDATE requests SET dtprint = '" + dt + "' WHERE

But don't use this way.

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Also use using statement to dispose your database connections and objects.

using(MySqlConnection con = new MySqlConnection(ConnectionString))
using(MySqlCommand _cmd = con.CreateCommand())
{
    string dt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
    _cmd.CommandText = @"UPDATE requests SET dtprint = @dtprint
                         WHERE idPerson = @id";
    _cmd.Parameters.Add("@dtprint", MySqlType.TimeStamp).Value = dt; 
    _cmd.Parameters.Add("@id", MySqlType.Int).Value = _personID[index];
     con.Open();
    _cmd.ExecuteNonQuery();      
}

edited Oct 30, 2014 at 11:59

answered Oct 30, 2014 at 7:43

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert C# DateTime to MySQL timestamp table column

1 Answer 1

But don't use this way.

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

But don't use this way.

Comments

Your Answer

Sign up or log in

Post as a guest

Related