MSSQL server 2005-数据类型（smalldatetime）的问题，而它有5个smalldatetime字段 [英] MSSQL server 2005- problem in datatype(smalldatetime) while its have 5 smalldatetime field

问题描述

亲爱的，

在名为tbltest的表中，还有5列有smalldatetime数据类型和其他列。当我插入 DateTime 时，我的问题非常有趣。

列字段的名称是 Dated，StartTime，EndTime，OnAirStartTime，OnAirEndTime。

我的插入操作代码没有任何问题：

Dear all,
There are 5 columns with smalldatetime datatype and others columns too in my Table named tbltest. And I got the problem very interesting when I am inserting DateTime.
The name of my column fields are Dated, StartTime, EndTime, OnAirStartTime, OnAirEndTime.
And there is not any problem in my insert operation code:

db.Adapter("INSERT INTO tbltest (Dated,StartTime,EndTime,OnAirStartTime,OnAirEndTime,DurationStart,DurationOnAir,DeviceType,UnitName,VendorName,ActiveNetwork,SIM,ProfileUsed,Bitrate,ServerPort,CameraType,EventName,Division,Location,Stutus,Remarks,ReporterName,CameramanName,TechnicianName) VALUES ('" + Convert.ToDateTime(this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["Dated"].ToString()).ToString() + "',"
                        + "'" + Convert.ToDateTime(this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["StartTime"].ToString()).ToString() + "',"
                        + "'" + Convert.ToDateTime(this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["EndTime"].ToString()).ToString() + "',"
                        + "'" + Convert.ToDateTime(this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["OnAirStartTime"].ToString()).ToString() + "',"
                        + "'" + Convert.ToDateTime(this.mysql.dataSet.Tables["liveoperation7"].Rows[i][5].ToString()).ToString() + "',"
                        + "'" + Convert.ToInt32(this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["DurationStart"].ToString()) + "',"
                        //+ "'" + Convert.ToDateTime(this.mysql.dataSet.Tables["liveoperation7"].Rows[i][5].ToString()).ToString() + "',"
                        + "'" + Convert.ToInt32(this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["DurationOnAir"].ToString()) + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["DeviceType"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["UnitName"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["VendorName"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["ActiveNetwork"].ToString() + "',"
                        + "'" + Convert.ToInt32(this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["SIM"].ToString()) + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["ProfileUsed"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["Bitrate"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["ServerPort"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["CameraType"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["EventName"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["Division"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["Location"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["Stutus"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["Remarks"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["ReporterName"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["CameramanName"].ToString() + "',"
                        + "'" + this.mysql.dataSet.Tables["liveoperation7"].Rows[i]["TechnicianName"].ToString() + "')");

Pa 5个日期时间值为：



日期= 2016-10-05 00:00:00

StartTime = 2016- 10-05 13:53:00

EndTime = 2016-10-05 13:55:00

OnAirStartTime = 2016-10-05 13:53:00

OnAirEndTime = 10/5/2016 13:54:30



>但是，在OnAirEndTime字段中插入了EndTime字段的值。

匹配字段名称有问题吗？

或者MSSQL 2005中是否有任何限制，不应超过4个字段smalldatetime 数据类型？



我尝试过：



我刚刚将OnAirEndTime数据类型更改为varchar而不是插入精确值。但是，我为什么要更改数据类型？



请帮我继续smalldatatime作为数据类型在表格中需要多少。



谢谢。

Passing 5 datetime values are:

Dated = 2016-10-05 00:00:00
StartTime = 2016-10-05 13:53:00
EndTime = 2016-10-05 13:55:00
OnAirStartTime = 2016-10-05 13:53:00
OnAirEndTime = 10/5/2016 13:54:30

>But, there is inserting in OnAirEndTime filed, the value of EndTime field.
Is there any problem the matching fields name ?
Or is there any limitation in MSSQL 2005 that there should not be more than 4 fields with smalldatetime datatypes ?

What I have tried:

I have just change a OnAirEndTime datatype as varchar than its inserting exact value. But, why should I change datatype ?

Please help me to continuing smalldatatime as a datatype how much I need in a table.

Thank you.

推荐答案

永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。使用参数化查询代替。

在这个特定的代码中它并不危险 - 但是有代表性的是你的代码的其余部分是：所以在一些用户通过输入为你做之前去检查并更改所有代码在一个文本框中...

然后将DateTime值直接传递给SQL作为参数，你的问题几乎肯定会消失 - 因为目前发送给SQL的值是在语言环境中格式化的运行该代码的机器，不太可能是ISO格式yyyy-MM-dd，更可能是MM-dd-yyyy或dd-MM-yyyy，这导致了它自己的一系列问题。

这也是一个更容易阅读的该死的视线......

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
In this specific code it's not dangerous - but it's symptomatic that the rest of your code is: so go and check and change it all, before some user does it for you by typing in a text box...
And then pass the DateTime values directly to SQL as parameters and your problem will almost certainly disappear - because at the moment the value you send to SQL are formatted in the locale for the machine running that code, which is unlikely to be ISO format yyyy-MM-dd, and more likely to be MM-dd-yyyy or dd-MM-yyyy, and that causes it's own set of problems.
It's also be a damn sight easier to read as well...

这篇关于MSSQL server 2005-数据类型（smalldatetime）的问题，而它有5个smalldatetime字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！