如何使用日期变量更新varchar列 [英] How to update a varchar column with date variable

问题描述

在我的下面的代码中，我得到一个错误说明：''字符串或二进制数据将被截断。''

''txfile''表的列名是exp_date varchar（10）。任何人都可以指导我纠正这个错误吗？

In my following code I get an error stating:'' String or binary data would be truncated.''
The ''txfile'' table column name is exp_date varchar(10). Can anyone guide me to rectify this error?

string v_expdt = v_startdt.AddDays(v_adddays).ToString();

string updateSqll = "UPDATE txfile " + "SET publishstatus = ''Y'', amtreceived =''" + v_amtreceived + "'', amtreceived_date =''" + v_amtreceiveddate+ "'', amtreceived_particulars =''" +v_particulars  + "'', amountrealised_date =''" + v_amtrealiseddate + "'' , exp_date =''" + v_expdt + "''" + "WHERE  mem_id = " + v_mem_id1 + "  and reg_id = " + v_regid + " and noofitems= " + v_itemno + " ";

推荐答案

我认为你需要改变：

I think you need to change:

v_startdt.AddDays(v_adddays).ToString();

是

to be

v_startdt.AddDays(v_adddays).Date.ToString(@"dd/MM/yyyy");

另外，我建议查找如何在SQL查询中使用参数以防止SQL注入：



http://www.dotnetperls.com/sqlparameter [ ^ ]

http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/ [ ^ ]

首先，你要让自己接受SQL注入攻击 - 使用参数化查询而不是连接文本 - 你的代码也会更清晰 http： //www.dotnetperls.com/sqlparameter [ ^ ]



下一步 - 你的问题是因为时间可能被添加到字符串 v_expdt 。你需要格式化它 - 看看这段代码输出的差异

Firstly you are leaving yourself open to a SQL Injection attack - use parameterized queries rather than concatenated text - your code will be clearer too http://www.dotnetperls.com/sqlparameter[^]

Next - your problem is because the time is probably being added on to the string v_expdt. You need to format it - See the difference in the output from this code

DateTime d = DateTime.Now;
Debug.Print(d.ToString());
Debug.Print(d.ToString("yyyy-MM-dd"));

给出输出

which gives output of

07/05/2013 17:47:03
2013-05-07

最后 - 如果您想在数据库上存储日期或日期时间，最好使用正确的列类型（取决于您使用的数据库） ）例如 [exp_date] [datetime] NULL 而不是varchar。它避免了这样的问题，并在使用数据时为您提供了更大的灵活性

Finally - if you want to store a date or datetime on a database you are better off using the proper column type (depending on which database you are using) e.g. [exp_date] [datetime] NULL rather than a varchar. It avoids issues like this and gives you greater flexibility when using the data

这篇关于如何使用日期变量更新varchar列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！