date time now format

Hi I try to insert into DB date time and the Column is date type what I need to do? this is the code

  string query = "INSERT INTO Feedback (user_Name, date_of_, Praise)  VALUES     ('"+TextBox1.Text+"',"+DateTime.Now+",'"+TextBox2.Text+"')";
    SqlCommand cmd = new SqlCommand(query, con);
    con.Open();
    cmd.ExecuteNonQuery();
    con.开发者_开发知识库Close();

I would advise against using time from the application server to insert values into the database. The most basic example how that can go wrong is that you could have two servers set to different time zones, that use the same database. What server's time is the right time?

Other thing is the neccessary transformation of a datetime to string when you are using inline SQL statements. If the application server and the database server are set to different cultures, you need to be extremely careful not to insert May 2nd (02.05), when you want to insert Feb 5th (02.05).

Sure, all these issues are avoidable, but why bother with them at all, when the RDBMS can do all that for us?

BTW, even if you don't want to use stored procedures, use parameters.
This code should be reformated like:

string query = "INSERT INTO Feedback (user_Name, date_of_, Praise)  VALUES     (@username, getdate(), @praise)"; 

SqlCommand cmd = new SqlCommand(query, con);
SqlParameter param = new SqlParameter("@username", SqlDbType.Text);
param.Value = text1;
cmd.Parameters.Add(param);

param = new SqlParameter("@praise", SqlDbType.Text);
param.Value = text2;
cmd.Parameters.Add(param);

con.Open(); 
cmd.ExecuteNonQuery(); 
con.Close(); 

Don't include the value directly in your SQL.

Use a parameterized query instead. There's no point in messing around with string formatting when the database is quite capable of accepting a prepared statement with a DateTime parameter.

You should get in the habit of using query parameters for all values which can't be simply hard-coded into the SQL to start with. For example, your query is currently just blithely taking the contents of TextBox1.Text and inserting that into the SQL. That's a recipe for a SQL injection attack.

You should separate the code (the SQL) from the data (the values). Parameterized queries are the way to do that.

EDIT: Using a built-in function in the SQL is fine, of course, if you're happy to use the database's idea of "now" instead of your client's idea of "now". Work out which is more appropriate for your situation.

Why don't you use a TIMESTAMP column in your database ? Seems like overhead by inserting it through your code.

http://msdn.microsoft.com/en-us/library/aa260631(SQL.80).aspx

edit: Set the default value of your database column as CURRENT_TIMESTAMP (Transact-SQL), and leave the column name out of your insert statement. The current date and time will be inserted by your database automatically. No problem with conversions anymore!

Replace DateTime.Now with DateTime.Now.ToString("yyyy-MM-dd");

Also, you should really parameterize your insert statement so that you cannot fall victim of a SQL injection attack.

There is a NOW() function in most SQL implementations.

You have to convert your DateTime to an Sql DateTime literal. The easiest way to do it is this:

DateTime.Now.ToString(System.Globalisation.CultureInfo.InvariantCulture)

Yet, especially for DateTime.Now, you may use some Sql function, such as GetDate() but that often depends on your database server.

You can use the Date property:

DataTime.Now.Date