来自c＃project的mysql数据库中update datetime列的问题 [英] problem in update datetime column in mysql database from c# project

问题描述

大家好，

我有c＃项目和数据库来存储信息。

问题是：

Hello all,
I have c# project and and database to store information in it.
the problem is :

string query = "UPDATE `accounting_security` SET `Email`='" + email + "', `UniqueID`='" + unID + "', `SubmitDate`='" + DateTime.Now + "', `Checked`='1' WHERE `Key`='" + key + "'";

//create mysql command
MySqlCommand cmd = new MySqlCommand();
//Assign the query using CommandText
cmd.CommandText = query;
//Assign the connection using Connection
cmd.Connection = connection;

//Execute query
cmd.ExecuteNonQuery();

查询执行成功但是datetime列没有正确保存DateTime.Now

它保存为这个：0000-00-00 00：00：00

有什么问题，怎么解决呢？

the query executes successfully but datetime column doesn't save the DateTime.Now correctly
it save like this : 0000-00-00 00:00:00
what is the problem and how can I solve it?

推荐答案

作为理查德说，在任何时候都使用参数化查询 - 这甚至很有可能同时解决您的问题。



如果没有，那么看看密切关注您的表定义：验证您尝试更改的列确实称为SubmitDate，并且存在具有相应键值的行。如果它不完全匹配，则不会更新任何行，这可能会造成该值被零覆盖的印象。

如果一切看起来都不错，那么手动将SubmitDate值更改为明年的某个时间并再次运行您的代码。数据库中的值是否更改为零？ （不要通过你的代码查看，使用外部工具）如果没有，那么更新就不会发生。

这也值得一看你用来检测它的代码是什么零 - 可能是你读错了而不是写错了。



目前，我们不能说做这个，它会解决它 - 你需要首先收集一些关于问题的信息。

但是首先要改变你的所有代码以使用参数化查询 - 这是一个无法忽视的风险。

As Richard says, use Parameterized queries at all times - there is even a good chance that that will cure your problem at the same time.

If it doesn't, then look closely at your table definition: verify that the column you are trying to change is indeed called SubmitDate, and that a row with the appropriate key value exists. If it doesn't exactly match, then no rows will be updated, and that may create the impression that the value is being overwritten with zeros.
If it all looks good, then manually change the SubmitDate value to sometime next year and run your code again. Is the value changed to zeros in the DB? (Don't look via your code, use an external tool) If not, then the update isn't happening.
It's also worth a look at the code that you are using to detect that it's a zero - it may be that you are reading it wrong instead of writing it badly.

At the moment, we can't say "do this and it'll fix it" - you need to gather some information around the problem first.
But change all your code to use parameterized queries first - that's just too much of a risk to ignore.

这篇关于来自c＃project的mysql数据库中update datetime列的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！