如何插入一个C＃日期时间VAR到SQL Server [英] How to insert a c# datetime var into SQL Server

问题描述

下面是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();

时间为 SMALLDATETIME SQL Server 2005中

the time is smalldatetime in SQL Server 2005

当我运行这个程序，错误occurrs是这样的：

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

strDate是不是在允许的名称
  这方面。有效的前pressions是
  常量，不断前pressions和
  （在某些情况下）的变量。柱
  名称是不允许的。

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.

但如果我替换 strDate 与 2010/05/22 是这样的：

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.

推荐答案

您应该使用参数化查询将数据插入到SQL Server，你应该把你的的SqlConnection 和的SqlCommand 成块的使用 - 尝试是这样的：

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

此外，这与你的UI code（检索从文本框和日历控件数据）将你的数据库访问code - 这是一个不好的做法 - 你应该分开这两个步骤：

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. 抢在$ C $从UI的当前值C-隐藏文件


2. 将它们传递到其处理数据访问而没有直接接触任何UI控件数据层

这篇关于如何插入一个C＃日期时间VAR到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！