如何使用VB 2010在sql server 2008中插入datetime？ [英] How to insert datetime in sql server 2008 using VB 2010?

问题描述

我是vb.net的新手，我正在尝试在我的表单中使用日期时间选择器。

在datetime列中将日期插入sql server时我收到一条错误，指出它无法转换到目前为止的字符串。



请帮我正确的代码。这是我正在使用的代码。

I am new to vb.net and I am trying to use a datetime picker in my form.
While inserting date to the sql server in datetime column I am getting an error stating it cannot convert the string to date.

please help me with the right code. Here is the code I am using.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connection As New SqlConnection()
        connection.ConnectionString = SqlConnectionString
        connection.Open()

        Dim ID_Penerima As String = txtIdPenerima.Text
        Dim Message_HL7 As String = txtMessageHl7.Text


        Dim sqlStatement As String = "insert FROMPenerima(IDPenerima, MessageHL7, Tanggal Penerima) select '" & ID_Penerima & "', '" & Message_HL7 & "', " & DateValue(DateTimePicker1) & ""
        Dim cmd As New SqlCommand(sqlStatement, connection)

        cmd.ExecuteNonQuery()
        connection.Close()

    End Sub
End Class

推荐答案

永远不要使用这样的查询！使用存储过程 [ ^ ]而是！



参见：

配置参数和参数数据类型 [ ^ ] - 在页面底部你会找到完整的代码。

演练：仅使用存储过程（Visual Basic） [ ^ ] - linq to sql example



By The Way，正确< a href =https://msdn.microsoft.com/en-us/library/ms174335.aspx> INSERT [ ^ ]语句应如下所示：

Never use query like this! Use Stored procedure[^] instead!

See:
Configuring Parameters and Parameter Data Types[^] - at the bottom of page you'll find complete code.
Walkthrough: Using Only Stored Procedures (Visual Basic)[^] - linq to sql example

By The Way, the proper INSERT[^] statement should looks like:

INSERT INTO Penerima (IDPenerima, MessageHL7, [Tanggal Penerima])
VALUES ('StringID', 'Message', 'DateValue')

修复 SQL注入 [ ^ ]漏洞也会修复错误消息：

Fixing the SQL Injection[^] vulnerability in your code will also fix the error message:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Using connection As New SqlConnection(SqlConnectionString)
        Using cmd As New SqlCommand("INSERT FROMPenerima (IDPenerima, MessageHL7, [Tanggal Penerima]) VALUES (@IDPenerima, @MessageHL7, @TanggalPenerima)", connection)
            
            cmd.Parameters.AddWithValue("@IDPenerima", txtIdPenerima.Text)
            cmd.Parameters.AddWithValue("@MessageHL7", txtMessageHl7.Text)
            cmd.Parameters.AddWithValue("@TanggalPenerima", DateValue(DateTimePicker1))
            
            connection.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

一个普通的插入语句（一个好的练习）如下所示：

A plain insert-statement (a good-practise one) looks like this:

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

一件重要的事情是你完全遗漏了代码： Sql-Parameters [ ^ 。它们由于各种原因很好：避免所谓的Sql-Injection-Attacks，避免奇怪的Sql-Syntax-Errors并使你的Sql-Statements更易读和可维护。立即使用它们，你可能（可能;））从来没有遇到过麻烦。



Sql-参数在你的Sql-Statements中是类似变量的东西。在Sql-Server的情况下，你在前面加上 @



示例：

One important thing is completely missing from your code: Sql-Parameters[^]. They're good for various reasons: Avoiding so-called Sql-Injection-Attacks, avoiding "strange" Sql-Syntax-Errors and making your Sql-Statements better readable and maintainable. Use them right away and you'll (probably ;)) never have troubles with that.

Sql-Parameters are "something like variables" in your Sql-Statements. And in case of Sql-Server you prefix them with an @

Example:

INSERT INTO Penerima (IDPenerima, MessageHL7, [Tanggal Penerima]) VALUES (@id, @msg, @tp);

与Sql-Insert一起提供这些参数的所需值 - 对Sql-Server的声明你必须创建SqlParameters的实例并将它们添加到你的SqlCommand-Object的Parameter-Collection中（另请注意我省略了DateValue（..）并在你的DateTimePicker中添加了.Value）避免你遇到的错误）：

To "deliver" the desired values of these parameters along with your Sql-Insert-Statement to the Sql-Server you have to create instances of SqlParameters and add them to the Parameter-Collection of your SqlCommand-Object (also note I left out the "DateValue(..)" and added ".Value" to your DateTimePicker to avoid the error you experienced):

Dim cmd As New SqlCommand(sqlStatement, connection)
cmd.Parameters.AddWithValue("@id", ID_Penerima)
cmd.Parameters.AddWithValue("@msg", Message_HL7)
cmd.Parameters.AddWithValue("@tp", DateTimePicker1.Value)

最后，为了做好事（至少和我现在想的一样多，把它全部包装成 Try-Catch-Finally [ ^ ]（只是那个）我们不需要这里的Catch-Part，这将确保您的连接始终正确关闭，即使在打开和关闭它之间应该抛出异常：

Finally, to make it good practise (at least as much as I can think of right now), wrap it all into a Try-Catch-Finally[^] (just that we don't need the Catch-Part here) which will ensure that your connection always gets properly closed even if there should an exception be thrown between opening and closing it:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connection As New SqlConnection()
    connection.ConnectionString = SqlConnectionString
    connection.Open()

    Try
        Dim sqlStatement As String = "INSERT INTO Penerima (IDPenerima, MessageHL7, [Tanggal Penerima]) VALUES (@id, @msg, @tp);"

        Dim cmd As New SqlCommand(sqlStatement, connection)

        cmd.Parameters.AddWithValue("@id", txtIdPenerima.Text)
        cmd.Parameters.AddWithValue("@msg", txtMessageHl7.Text)
        cmd.Parameters.AddWithValue("@tp", DateTimePicker1.Value)

        cmd.ExecuteNonQuery()
    Finally
        connection.Close()
    End Try
End Sub

一个提示：Visual Studio 2013社区版比VS2010更好（至少如果你不喜欢）有2010年终极版，特别是如果你有2010快递），它是免费的！ :)在此处下载： https://www.visualstudio.com/en-我们/产品/ visual-studio-community-versusaspx [ ^ ]



编辑：如果应该存在语法错误代码（我对VB没有经验但是尽我所能）请告诉你是否自己无法修复它，我会尽力帮助。

And one tip: Visual Studio 2013 Community Edition is way better than VS2010 (at least if you don't have 2010 Ultimate and especially if you have 2010 Express) and it's FREE! :) Download it here: https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx[^]

If there should be syntax-errors in the code (I'm unexperienced with VB but tried my best) please tell if you can't fix it yourself and I will try to help.

这篇关于如何使用VB 2010在sql server 2008中插入datetime？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！