使用日期格式更新数据库 [英] Update database with date format

问题描述

我在论坛中找到了一些与此问题相关的主题，但它们没有帮助我.我只想用日期值更新我的数据库.这些来自文本文件(例如，在那里写成 2014-10-02).现在我尝试了这个(在其他线程中提到过):

I found some threads here in the forum related to this problem but they didn't help me. I just want to update my database with a date value. These come from a Textfile (written there as 2014-10-02 for example). Now I tried this (which was mentioned in the other threads):

 String connectionQuery = form1.conString.Text;
                SqlConnection connection = new SqlConnection(connectionQuery);
                SqlCommand sqlComInsert = new SqlCommand(@"INSERT INTO [" + form1.tableName.Text + "] ([" + form1.CusId.Text + "],["+ form1.date.Text +"],[" + form1.cusName.Text + "]) VALUES('" + cusId[i] + "',convert(date,'" + date[i] + "',104),'" + "','" + cusName[i] + "')", connection);

  sqlComInsert.Connection.Open();
                sqlComInsert.ExecuteNonQuery();
                sqlComInsert.Connection.Close();

现在，当我将 "'" 去掉 ("',convert/"',104)) 时，他告诉我语法在 2013 年(我约会的开始)附近不正确.当我像上面那样写时，我得到:字符串或二进制数据将被截断.

Now when I leave the "'" out ("',convert / "',104)) he tells me that the syntax is incorrect near 2013 (the beginning of my date). When I write it like above then I get: String or binary data would be truncated.

这是什么?我还尝试将日期转换为:

What is this? I tried also to convert the date with:

  for (int i = 0; i < typeDelDate.Count; i++)
            {
                unFormatedDate = date[i];
                formatedDate = unFormatedDate.ToString("dd/MM/yyyy");
                dateFormat.Add(formatedDate);
            }

但我仍然遇到相同的错误.如何更新我的值?列类型为日期".

but I get still the same errors. How can I update my values? the column type is "date".

推荐答案

使用参数化查询而不是将字符串拼接在一起:

Use parametrized queries instead of slapping strings together:

var commandText = "insert (column) values (@dt);";
var cmd = new SqlCommand(commandText, connection);

cmd.Parameters.AddWithValue("dt", DateTime.ParseExact(dateString, "yyyy-MM-dd"));

cmd.ExecuteNonQuery();

不要通过添加字符串将值传递给查询 - 如果可能，您应该始终使用参数.它可以为您省去很多转换为正确值(因不同地区而异等)的麻烦，更安全，并且有助于提高性能.

Do not pass values into queries by adding strings - if possible, you should always use parameters. It saves you a lot of trouble converting to proper values (different for different locales etc.), it's more secure, and it helps performance.

这篇关于使用日期格式更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！