Why date cannot be inserted in database

Question

I have program that inserts data into SQL-server database

var format = "dd/MM/yyyy HH:mm:ss.mmm";   
date = DateTime.Now.ToString(format);

I have tried this one too : "yyyy-MM-dd HH:mm:ss.fff";

updateEvent_list = string.Format(@"INSERT INTO Events_List 
            (date, Object,Event,IOA,ASDU) 
            VALUES({0},{1},{2},{3},{4})", 
            date, event_object, "bla bla", ioa, ASDU);

The date column type in the database is datetime2(7)

(0x80131904): Incorrect syntax near '10'

The date that was inserted : 12/07/2016 10:43:22.43 I have changed the date and it's always showing error near hours .

Execution code :

if (state == ConnectionState.Closed)   
   connection.Open();
try
{
    ExecuteQueryWithoutResult(connection, updateEvent_list);
    SetText2("update_Even_list query executed");
}
catch(Exception ex) { SetText2(ex.ToString()); }


static void ExecuteQueryWithoutResult(SqlConnection connection, string query)
{
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.ExecuteNonQuery();
    }
}

NOTE : i tried to insert directly from sql query and it works fine :

INSERT INTO Events_List (date, Object,Event,IOA,ASDU)

VALUES ('2016-07-12 10:26:03.523', 'B','bla bla' ,10,10)

Stop here and discard this code. Then search how to build parameterized queries. You will never get out of this kind of problems if you continue to concatenate strings. And the syntax errors are the least of your problems — Steve, Jul 12 '16 at 09:50
And now hope that none of your inputs contains a single quote. [Or worse...](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — Steve, Jul 12 '16 at 09:52
And some people wonder why sql-injection is still a problem today... this is why. Please use parameters https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx — Esko, Jul 12 '16 at 09:53

score 3 · Accepted Answer · answered Jul 12 '16 at 10:13

You should consider the internationalization, globalization and localization in your programs.

I suggest you try the following

        DateTime date=DateTime.UtcNow; 
        string updateEvent_list = "INSERT INTO Events_List (date, Object,Event,IOA,
    ASDU) VALUES(@date, @Object,@Event,@IOA,@ASDU)";

        SqlCommand command = new SqlCommand(query, db.Connection);
        command.Parameters.AddWithValue("@date", date);
        //...

With this, your program won't care if the sql server platform dateformat is the same as the C# running machine. In addition, you avoid sql injection.

score -1 · Answer 2 · answered Jul 12 '16 at 09:51

-1

you forget to add '' into your query in the VALUES part. it should be like bellow

string.Format("INSERT INTO Events_List (date, Object,Event,IOA,ASDU)  VALUES('{0}','{1}','{2}',{3},{4})", date, event_object, "bla bla", ioa, ASDU);

answered Jul 12 '16 at 09:51

Mark

2,041
2
18
35

1

Formating Date via string is dangerous, for example will give error in the future in different national settings. Only SQL parameters are correct solution – Jacek Cz Jul 12 '16 at 09:52
@JacekCz why downvote? i was just answering his problem. – Mark Jul 12 '16 at 09:59
Down isnt from me ( but to some level I agree) – Jacek Cz Jul 12 '16 at 10:01
2

@Mark your solution is just a gate straight to hell. You never concatenate strings to build sql commands. What if a single quote is present in the inputs?. And have you ever heard of Sql Injection?. Just providing a quick fix and not writing at least something about the bigger problems is not a good answer. – Steve Jul 12 '16 at 10:02
@Steve look at how the date declared. its declared in the Back End. How if OP just want to update without a single input from Front end that wont let any people use the sql injection. I believe it depends on how OP would create his web whether he lets people input something and prevent SQL injection, or just use a simple update in the server side without any input from Front side – Mark Jul 12 '16 at 10:07

Why date cannot be inserted in database

2 Answers2