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.

marc_s · Accepted Answer · 2011-05-23 04:59:31Z

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

Nate · Accepted Answer · 2011-05-23 04:52:02Z

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, 2011 at 4:52

Nate

30.7k24 gold badges121 silver badges187 bronze badges

answered May 23, 2011 at 4:27

Maxymus

1,4902 gold badges14 silver badges22 bronze badges

2 Comments

Damien_The_Unbeliever Over a year ago

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)

marc_s Over a year ago

The ? parameter placeholder will not work with the standard ADO.NET database provider - you need to use named parameters like @Date and @Event.

Mamta D · Accepted Answer · 2011-05-23 05:43:50Z

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, 2011 at 5:43

answered May 23, 2011 at 3:35

Mamta D

6,4803 gold badges30 silver badges43 bronze badges

1 Comment

marc_s Over a year ago

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!

Collectives™ on Stack Overflow

How to insert a c# datetime var into SQL Server

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related