How to insert a c# datetime var into SQL Server

Question

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

the program will run correctly.

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

score 5 · Answer 1 · answered May 23 '11 at 04:59

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:

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

score 2 · Answer 2 · edited May 23 '11 at 04:52

2

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

edited May 23 '11 at 04:52

Nate

30,286
23
113
184

answered May 23 '11 at 04:27

Maxymus

1,460
2
14
22

Yep. Avoid converting the datetime value into a string in the first place, and you avoid all kinds of issues. (And also, yes, use parameters. Unfortunately, I can only +1) – Damien_The_Unbeliever May 23 '11 at 04:31
The `?` parameter placeholder will not work with the standard ADO.NET database provider - you need to use named parameters like `@Date` and `@Event`. – marc_s May 23 '11 at 05:00

Mamta D · Answer 3 · 2011-05-23T05:43:50.367

1

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 *

edited May 23 '11 at 05:43

answered May 23 '11 at 03:35

Mamta D

6,310
3
27
41

2

WARNING: This will open the doors to SQL Injection attacks !! You should **NEVER EVER** just concatenate together your SQL statements - use **parametrized queries** instead - **ALWAYS!** – marc_s May 23 '11 at 04:56

How to insert a c# datetime var into SQL Server

3 Answers3

Linked