将日期和时间从 VB.Net 保存到 mysql 数据库日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21757293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Saving Date&Time from VB.Net to mysql Database Date?
提问by usminuru
I'm inserting client information into Clients table found in MySql database from VB.Net 2010. The problem is when it insert Date values, the MySql Date column shows: "0000-00-00".
我将客户端信息插入到 VB.Net 2010 的 MySql 数据库中的 Clients 表中。问题是当它插入日期值时，MySql 日期列显示：“0000-00-00”。
What I have to do to insert the compatible date format into MysQl Database?
我必须做什么才能将兼容的日期格式插入 MysQl 数据库？
This is the code that i have tried to do that:
这是我试图这样做的代码：
Dim dtb, dtr As DateTime
DateTimePicker1.Format = DateTimePickerFormat.Custom
DateTimePicker1.CustomFormat = "yyyy-mm-dd"
dtb = DateTimePicker1.MinDate
txtdtb.AppendText(dtb)
DateTimePicker2.Format = DateTimePickerFormat.Custom
DateTimePicker2.CustomFormat = "yyyy-mm-dd"
dtr = DateTimePicker1.MinDate
ExecSQL("INSERT INTO clients VALUES('" & clid.Text & "','" & clname.Text & "','" & clgen.Text & "','" & dtb & "','" & claddress.Text & "','" & clemail.Text & "','" & clphone.Text & "','" & clocp.Text & "','" & dtr & "')")
MsgBox("Record Saved", MsgBoxStyle.Information, "Save")
FillList(frmMember.lvMember, GetData("SELECT * FROM clients"))
End If
回答by Tim Schmelter
You are inserting DateTimePicker1.MinDateinstead of the actual values the user has chosen.
您正在插入DateTimePicker1.MinDate而不是用户选择的实际值。
Also, use uppercase months otherwise these are the minutes mm:
此外，使用大写月份，否则这些是分钟mm：
DateTimePicker1.CustomFormat = "yyyy-MM-dd"
DateTimePicker2.CustomFormat = "yyyy-MM-dd"
Finally, use sql-parametersinstead of string concatenation to prevent sql-injection and localization or conversion issues.
最后，使用 sql-parameters而不是字符串连接来防止 sql-injection 和本地化或转换问题。
Here's an example:
下面是一个例子：
Using con As New MySqlConnection(My.Settings.ConnectionString)
Using cmd = New MySqlCommand("INSERT INTO clients VALUES(@clid,@clname,@clgen,@dtb,@claddress,@clemail,@clphone,@clocp,@dtr)", con)
cmd.Parameters.AddWithValue("@clid", Int32.Parse(clid.Text))
' .... '
cmd.Parameters.AddWithValue("@dtr", dtr.Value)
con.Open()
Dim insertedClientCount = cmd.ExecuteNonQuery()
End Using
End Using
回答by Ayushman Mishra
In short you need DateTimePicker1.value.tostring("yyyy-M-d").
总之你需要DateTimePicker1.value.tostring("yyyy-M-d").
The CustomFormatproperty of DateTimePickeronly changes the way the component is displayed on the form, the actual date value of the DateTimePickeris stored in its Valueproperty:
该CustomFormat物业DateTimePicker只是改变了组件显示在表格上的方式，对实际日期值DateTimePicker存储在它的Value属性：
DateTimePicker1.Value
To change the format of the date you can just use the ToString(format as string)method:
要更改日期格式，您只需使用以下ToString(format as string)方法：
DateTimePicker1.Value.ToString("yyyy-M-d")
In MySQL the "date" data type stores date only and accepts date in the format "2017-03-20" hence we use the format "yyyy-M-d" in the ToStringfunction.
在 MySQL 中，“日期”数据类型仅存储日期并接受格式为“2017-03-20”的日期，因此我们在ToString函数中使用格式“yyyy-Md” 。
回答by Ohlin
I had problems like that as well and I solved it by specifying ToString("s") to all my DateTime variables
我也有类似的问题，我通过为所有 DateTime 变量指定 ToString("s") 来解决它
dtb.ToString('s')
dtr.ToString('s')
By writing this way the date has the correct format (2006-08-22T06:30:07) when inserting into MySQL.
通过这种方式，日期在插入 MySQL 时具有正确的格式 (2006-08-22T06:30:07)。
回答by Steve
Probably the problem of the zero date is due to the fact that you read the wrong property of the DateTimePicker (MinDate instead of Value), but you have a big problem here.
零日期的问题可能是由于您读取了 DateTimePicker 的错误属性（MinDate 而不是 Value），但您在这里遇到了大问题。
If you use string concatenation to form your Sql commands, you are writing a very weak code. First you are open to Sql Injections, second you cannot be sure to format correctly your input values to the likes of the database engine and third parsing error could arise if your user types a single quote in a string field.
如果您使用字符串连接来形成您的 Sql 命令，那么您正在编写一个非常弱的代码。首先，您对 Sql 注入持开放态度，其次您无法确保将输入值正确格式化为数据库引擎之类的格式，如果您的用户在字符串字段中键入单引号，则可能会出现第三个解析错误。
To fix all of these problems require a rewrite of your ExecSQL to receive a collection of parameters
要解决所有这些问题，需要重写 ExecSQL 以接收参数集合
For Example
例如
Dim pms As List(Of MySqlParameter) = new List(Of MySqlParameter)()
pms.Add(new MySqlParameter("@id",clid.Text) ' Convert.ToInt32(clid.Text) if this is numeric'
.... and so on for the other parameters ....
ExecSql("Insert into clients " & _
"VALUES(@id,@name,@gen,@dtb,@addr,@email,@phone,@clop,@dtr)",pms)
Public Sub ExecSQL(command As String, pms As List(Of MySqlParameter))
.. open the connection with using
Dim cmd = new MySqlCommand(command, connection)
cmd.Parameters.AddRange(pms.ToArray())
cmd.ExecuteNonQuery();
End Sub
As you can see, the command text is now more readable and there are no more string concatenations with embedded single quotes.
如您所见，命令文本现在更具可读性，并且不再有带有嵌入单引号的字符串连接。
