MySQL C# Insert query Server timestamp

Question

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?

What are the datypes of the columns DATA and ORA_INIZIO_EVENTO? Is the field ID_WORKTIME an AUTONUMBER ? — Steve
– Steve, Commented Jun 28, 2016 at 14:48

Steve · Accepted Answer · 2016-06-28 14:57:01Z

1

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();

answered Jun 28, 2016 at 14:57

Steve

217k22 gold badges242 silver badges296 bronze badges

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

6 Comments

Rahul Over a year ago

When did you learned MySQL? I don't see it in your profile :)

DearS Over a year ago

I follow your code but on DB the fields are again 0000-00-00 etc... Here is my table imgur.com/ndX0JHa.

Steve Over a year ago

A couple of observations. I have not added the column and value for ID_Worktime because I thought that this is an autoincrement field. If it is not then you need to provide a numeric value for it not an empty string and rely on any kind of conversion that the database will do for you (if any). Then the column ending with FK let me think that these are foreign keys to other tables. So when you add a record here then the two values should be present in the other tables where the FK points to.

Steve Over a year ago

Finally do you have any kind of try/catch around this code? If yes I recommend to show the error message in the catch block kept by the Exception.Message property. Said that, manually inserting a record works here.

Steve Over a year ago

Tried passing an empty string for ID_WORKTIME and, as expected, the code throws an exception.... "Incorrect integer value: '' for column 'ID_WorkTime' at row 1" So the problem now is: Why you don't see this exception?

|

Collectives™ on Stack Overflow

MySQL C# Insert query Server timestamp

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related