vb.net 从字符串转换日期和/或时间时转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17187194/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-17 13:58:13 来源:igfitidea点击:
Conversion failed when converting date and/or time from character string
提问by user2500378
sql = "insert into tbl_nurse(nurseid,nursename,deptname,dob,doj,qualification,salary)"
sql = sql & "values('" & txtNurseid.Text & "','" & TxtNursename.Text & "','" & Cmbdept.Text & "',convert(date,'" & DateTimePicker1.Value & "',103),convert(date,'" & DateTimePicker2.Value & "',103),'" & Txtqualification.Text & "','" & txtsalary.Text & "')"
conn.Execute(sql)
回答by Tim Schmelter
You should use sql-parametersto avoid sql-injectionand to prevent from conversion issues like this.
您应该使用sql-parameters来避免sql-injection并防止出现这样的转换问题。
Example presuming SQL-Server:
假设 SQL-Server 的例子：
Const sql = "INSERT INTO tbl_nurse(nurseid,nursename,deptname,dob,doj,qualification,salary)" & vbCrLf & _
"VALUES(@nurseid, @nursename, @deptname, @dob, @doj, @qualification, @salary)"
Using con = New SqlConnection("Insert Your Connection String Here")
Using cmd = New SqlCommand(sql, con)
cmd.Parameters.AddWithValue("@nurseid", txtNurseid.Text)
cmd.Parameters.AddWithValue("@nursename", TxtNursename.Text)
cmd.Parameters.AddWithValue("@deptname", Cmbdept.Text)
' -- No conversion problems anymore because you pass a DateTime -- '
cmd.Parameters.AddWithValue("@dob", DateTimePicker1.Value)
' ... other parameters ... '
con.Open()
Dim affectedRecords As Int32 = cmd.ExecuteNonQuery()
End Using
End Using
回答by matzone
Try to change like this ..
试着像这样改变..
sql = "insert into tbl_nurse(nurseid,nursename,deptname,dob,doj,qualification,salary)"
sql = sql & " values('" & txtNurseid.Text & "','" & TxtNursename.Text & "','" & Cmbdept.Text & "',#" & format(DateTimePicker1.Value.Date) & "#,#" & format(DateTimePicker2.Value.Date) & "#,'" & Txtqualification.Text & "','" & txtsalary.Text & "')"
conn.Execute(sql)
As Tim Scmelter said .. you better use parameterize input
正如 Tim Scmelter 所说 .. 你最好使用参数化输入
