CODEWITHSUNDEEP
c#mysqlvisual-studio
DearS
DearS

Reputation: 192

MySQL C# Insert query Server timestamp

I have a C# program and I want to run a MySQL query that insert a record. In this record I have a timestamp field that MUST BE the server timestamp, not the client timestamp. So, I write this:

start_session = new MySqlDataAdapter("INSERT INTO CUBE_WORKTIME(ID_WORKTIME,
                                      ID_RISORSA_FK,DATA,ORA_INIZIO_EVENTO, ORA_FINE_EVENTO,
                                      ID_CDC_FK, CAUSALE, LAST_EVENT)
                             VALUES ('', '"+ idrisorsa_global + "', DATE(NOW()),NOW(),
                                     NULL, '"+ IDCDC +"', 'Login', 'Y')", connection);

DataTable start_session_dataset = new DataTable();
start_session.Fill(start_session_dataset);

This query works well, the ID_RISORSA_FK and IDCDC fields are correct. But the date and the datetime are 0000-00-00 and 0000-00-00 00:00:00. I also tried adding the quotes, but no effects.

Any ideas?

Upvotes: 0

Views: 771

Answers (1)

Steve
Steve

Reputation: 216303

The first thing to change is the use of an MySqlDataAdapter to just insert a record. While this could work it is not the correct class to use for this work. A simple MySqlCommand is the correct object to use and with a lot less of infrastructure required

The second thing to change is the way in which you build your sql query. Do not concatenate together strings to form an sql command but use Parameters. This avoid Sql Injection and parsing problems.

So your code could be rewritten as 

string cmdText = @"INSERT INTO CUBE_WORKTIME 
         (ID_RISORSA_FK,DATA,ORA_INIZIO_EVENTO, ORA_FINE_EVENTO,ID_CDC_FK, 
          CAUSALE, LAST_EVENT) VALUES (@risorsaID, CURDATE(), CURTIME(),
          NULL, @cdcID, 'Login', 'Y')";

MySqlCommand cmd = new MySqlCommand(cmdText, connection);
cmd.Parameters.Add("@risorsaID", MySqlDbType.Int32).Value = idrisorsa_global;
cmd.Parameters.Add("@cdcID", MySqlDbType.Int32).Value = IDCDC;
int rowsInserted = cmd.ExecuteNonQuery();

Upvotes: 1

Related Questions