DateTime.Now in smalldatetime?

Question

I am trying to get the date and time using C # and then insert it into the smalldatetime data smalldatetime in SQL SERVER.

Here is how I am trying to do this:

 DateTime date = DateTime.Now; sql = "INSERT INTO YTOODLE_LINKS (YTOODLE_LINKS.TASK_ID,YTOODLE_LINKS.LINK_TITLE,YTOODLE_LINKS.LINK_DESC,YTOODLE_LINKS.LINK_PATH,YTOODLE_LINKS.USER_ID,YTOODLE_LINKS.LAST_USER_EDIT)VALUES (1,'','','',2,'1',"+ date +")"; dataObj = new DataObj(); dataObj.InsertCommand(sql); connection = new SqlConnection(conn); connection.Open(); cmd = new SqlCommand(sql, connection); cmd.ExecuteNonQuery(); connection.Close(); 

and then he gives me: “The wrong syntax is around“ 16. ”I think this refers to my current time, which is now 16:15.

Answer 1

I would suggest using options. cmd.Parameters.AddWithValue("@date", date.toString); AddWithField will take care of the correct conversion.

Your InsertSQL status will look like this:

 sql = "INSERT INTO YTOODLE_LINKS (YTOODLE_LINKS.TASK_ID,YTOODLE_LINKS.LINK_TITLE,YTOODLE_LINKS.LINK_DESC,YTOODLE_LINKS.LINK_PATH,YTOODLE_LINKS.USER_ID,YTOODLE_LINKS.LAST_USER_EDIT)VALUES (1,'','','',2,'1',@date)"; 

Answer 2

This does not work for two reasons:

• Your date parameter should call date.ToString ()

• You must add single quotes before and after the date string is inserted into your inline query like this:

   sql = "INSERT INTO YTOODLE_LINKS (YTOODLE_LINKS.TASK_ID,YTOODLE_LINKS.LINK_TITLE,YTOODLE_LINKS.LINK_DESC, YTOODLE_LINKS.LINK_PATH,YTOODLE_LINKS.USER_ID,YTOODLE_LINKS.LAST_USER_EDIT) VALUES (1,'','','',2,'1','"+ date +"')"; 

But the above strategy is not good because it exposes you to SQL Injection attacks by concatenating strings the way you do it, and also because you need to worry about adding single quotes, etc. etc.

It is better to use the following parameters:

 sql = "INSERT INTO YTOODLE_LINKS (YTOODLE_LINKS.TASK_ID,YTOODLE_LINKS.LINK_TITLE,YTOODLE_LINKS.LINK_DESC, YTOODLE_LINKS.LINK_PATH,YTOODLE_LINKS.USER_ID,YTOODLE_LINKS.LAST_USER_EDIT) VALUES (@First,@Second,@Third,@Fourth,@Fifth,@Sixth,@YourDate)"; cmd.Parameters.AddWithValue("@First", 1); // ... and so on cmd.Parameters.AddWithValue("@YourDate", date); 

Now you don’t have to worry about SQL injections or adding single quotes to some parameters depending on the data type, etc. All this is transparent to you, you are more secure, and the database engine will be able to optimize the execution. Plan your request.