尝试更新数据库中的值时出错 [英] Error when trying to update values in database

问题描述

我有以下c＃/查询：

TrackDuration =TimeSpan.Parse( Request.Form["TrackDuration"].ToString());
string InsertQuery = string.Format("UPDATE tblTracks SET  TrackLength={0}, TrackDuration='{1}', TrackName='{2}',TrackDescription='{3}',TrackMap='{4}',DifficultLevel={5},OverallHeight={6},IsCircular='{7}', ForBeginners='{8}',StartPoint='{9}',ParkingPlace='{10}',SeasonOfYear={11},TrackLocation={12}, Images='{13}' WHERE UserID={14}",
                                                                      TrackLength, TrackDuration, TrackName, TrackDescription, TrackMap, DifficultID, OverallHeight, IsCircular, ForBeginners, StartPoint, ParkingPlace, SeasonID, AreaID, ImageList, UserID);

但我收到此错误讯息：

UPDATE语句中的语法错误

Syntax error in UPDATE statement

查询表达式中的语法错误（缺少运算符）

Syntax error (missing operator) in query expression

$

我如何解决这个问题？

更新：

这是查询的值：

UPDATE tblTracks SET  TrackLength=35, TrackDuration='02:30:00', TrackName='45',TrackDescription='<p>sometext.</p>
',TrackMap='f',DifficultLevel=3,OverallHeight=450,IsCircular='true', ForBeginners='false',StartPoint='<p>קיבוץיסעור </p>
',ParkingPlace='<p>כניסה לקיבוץ יסעור</p>
',SeasonOfYear=1,TrackLocation=3, Images='' WHERE UserID=1

sql值类型是：

TrackLength = number ; TrackDuration = date/time ; TrackName= string ;TrackDescription= string; TrackMap = string; DifficultLevel=number;OverallHeight=number;IsCircular=true/false;ForBeginners=true/false;
StartPoint=string; ParkingPlace=string; SeasonOfYear=number; TrackLocation=number;Images=string

推荐答案

'02：30 ：00'不是 datetime 数据库字段AFAIK的正确值。默认格式由日期格式设置控制。

'02:30:00' is not a correct value for datetime DB field, AFAIK. The default format is controlled by date format setting.

此外，20130412应该在任何情况下工作，但对于datetime字段。您需要正确格式化 TrackDuration 或使用 CAST / CONVERT 。由于 TimeSpan 不包含日期部分（它表示持续时间，而不是时间点），您只能将其添加（例如，添加20100101），但这是一个可怕的黑客。

Additionally, '20130412' should work in any case, but for datetime field. You need to format the TrackDuration correctly or use CAST/CONVERT. As TimeSpan doesn't contain date part (it represents a duration and not a point in time), you can only make it up (e.g. prepend "20100101") but that is an awful hack.

正确的解决方案是使用正确的数据库字段类型。

The proper solution is to use the correct DB field type.

'02：30 ：00如果字段为 time 类型，则可能有效。请详细了解 SQL Server中的时间类型。

'02:30:00' might work if the field was of time type. Please read some more about time types in SQL Server.

更好的是，为什么不使用纯整数的持续时间以秒为单位？

Even better, why don't you use plain integer for the duration in seconds? The duration is not a date anyway.

更大的问题是，您要连接字符串来设置命令文本，这将打开SQL注入攻击。如果我命名赛道 a'; DROP TABLE tblTracks; - 您的数据库是toast：

The much bigger issue is that you are concatenating strings to set the command text, which opens you for SQL injection attack. If I name the racing track a';DROP TABLE tblTracks;-- your database is toast:

UPDATE tblTracks SET TrackLength=35, 
                     TrackDuration='02:30:00', 
                     TrackName='a';DROP TABLE tblTracks;-- ...

这篇关于尝试更新数据库中的值时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！