How to insert a c# datetime var into SQL Server

Here is the code:

string ConnectionString= @"Data Source=localhost\SQLEXPRESS; 
Initial Catalog=notepad; Integrated Security=SSPI ";
SqlConnection con = new SqlConnection(ConnectionString); 
con.Open();
string strEvent = TextBoxEvent.Text;
string strDate = Calendar1.TodaysDate.ToShortDateString();
string strInsert = "insert into notepad (time, event) values (strDate, strEvent )";
SqlCommand cmd=new SqlCommand(strInsert, con);
cmd.ExecuteNonQuery();

the time is smalldatetime in SQL Server 2005

When I run this program, an error occurrs like this:

The name "strDate" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

but if I replace the strDate with 2010/05/22 like this:

string strInsert = "insert into notepad (time, event) values ("2010/05/22", strEvent )";

t开发者_高级运维he program will run correctly.

I am puzzled with this problem and turn for help to you.

You should use parametrized queries for inserting data into SQL Server, and you should put your SqlConnection and SqlCommand into using blocks - try something like this:

string ConnectionString= @"Data Source=localhost\SQLEXPRESS; 
Initial Catalog=notepad; Integrated Security=SSPI ";

string sqlStatement = "INSERT INTO dbo.Notepad(time, event) VALUES (@Date, @Event)";

using(SqlConnection con = new SqlConnection(ConnectionString))
using(SqlCommand cmd = new SqlCommand(sqlStatement, con))
{
   cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = Calendar1.TodaysDate;
   cmd.Parameters.Add("@Event", SqlDbType.VarChar, 100).Value = TextBoxEvent.Text.Trim();

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

Also, this is mixing your database access code with your UI code (retrieving data from textbox and calendar control) - this is a bad practice - you should separate this into two steps:

1. grab the current values from the UI in your code-behind file
2. pass them to a data layer which handles data access without ever directly touching any UI controls

All the methods here said are good but I prefer the following one because its has an edge over the other ones

DataAccess DAB = new DataAccess();
ArrayList arrList = new ArrayList();
string SQL = " insert into notepad (time, event) values (?,?) ";
arrList.Add(new DataAccessBlock.DataAccess.Parameter("@time",  DbType.DateTime, 30, ParameterDirection.Input, "", strDate ));    
arrList.Add(new DataAccessBlock.DataAccess.Parameter("@event", DbType.String, 50, ParameterDirection.Input, "", strEvent ));

DAB.ExecuteScalar(SQL, CommandType.Text, arrList);

This statement below is wrong because you are actually including strDate instead of substituting its value.

string strInsert = "insert into notepad (time, event) values (strDate, strEvent )";

What you need to do is write as follows:

string strInsert = "insert into notepad (time, event) values ("+strDate+"," +strEvent+)";

This will substitute strDate and strEvent with the actual values at runtime.

*This approach however is NOT recommended because it is prone to SQL injection attacks *