如何使用VB 2010在sql server 2008中插入datetime?(How to insert datetime in sql server 2008 using VB 2010?)

88 IT屋

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
解决方案
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')

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,...);

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);

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)

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


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[^]

Edit: 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.net的新手,我正在尝试在我的表单中使用日期时间选择器。

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



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





 私人  Sub  Button1_Click( ByVal  sender  As < / span>系统。对象 ByVal  e  As  System.EventArgs)句柄 Button1.Click 
Dim connection As SqlConnection()
connection.ConnectionString = SqlConnectionString
connection.Open()

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


Dim sqlStatement As < / span> 字符串 = 插入FROMPenerima(IDPenerima,MessageHL7 ,Tanggal Penerima)选择'& ID_Penerima& ','& Message_HL7& ',& DateValue(DateTimePicker1)&
Dim cmd 作为 SqlCommand(sqlStatement,connection)

cmd.ExecuteNonQuery()
connection.Close()

结束 Sub
结束
解决方案
永远不要使用这样的查询!使用存储过程 [ ^ ]而是!



参见:

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

演练:仅使用存储过程(Visual Basic) [ ^ ] - linq to sql example



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

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

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

 私有  Sub  Button1_Click( ByVal 发​​件人作为系统。对象 ByVal  e  As  System.EventArgs)句柄 Button1.Click 
使用连接作为 SqlConnection(SqlConnectionString)
使用 cmd 作为 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()
结束 使用
结束 使用
结束 Sub

一个普通的插入语句(一个好的练习)如下所示:

  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的情况下,你在前面加上 @



示例:

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


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

  Dim  cmd 正如  SqlCommand(sqlStatement,connection)
cmd.Parameters.AddWithValue( @ id,ID_Penerima)
cmd.Parameters.AddWithValue( @ msg,Message_HL7)
cmd.Parameters.AddWithValue( @ tp,DateTimePicker1.Value)


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

 私有  Sub  Button1_Click( ByVal  s ender  As  System。 Object  ByVal  e  As  System.EventArgs)句柄 Button1.Click 
Dim connection As SqlConnection()
connection.ConnectionString = SqlConnectionString
connection.Open()

尝试
Dim sqlStatement 作为 字符串 = INSERT INTO Penerima(IDPenerima,MessageHL7,[Tanggal Penerima])VALUES(@ id,@ msg,@ tp);

Dim cmd 作为 SqlCommand(sqlStatement,con提示)

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

cmd.ExecuteNonQuery()
最后
connection.Close()
结束 尝试
结束 Sub




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



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

本文地址:IT屋 » 如何使用VB 2010在sql server 2008中插入datetime?