在VB.NET中使用date数据类型在SQL列中插入日期 [英] Insert date in SQL column with date datatype in VB.NET

问题描述

我正在尝试在SQL表中插入日期，日期数据类型列从文本框格式化为DD / MM / YYYY



1.如果日期是01/11/2018它在sql表中显示为2018-01-11



2.如果日期是16/11/2018，则转换日期时错误转换失败和/或来自字符串的时间。



我尝试了什么：



<

 pre>私有 Sub  ButTest_Click（发件人 As   Object ，e  As  EventArgs） Handles  ButTest.Click 
  Dim  ErrorMsg  As   String  
 
 尝试 
 
 调用 DBCnConnection（）
 
  Dim  Cmd  As  新 SqlClient.SqlCommand 
 
 Cmd.Connection = DBCnCommon 
 
 Cmd.CommandText =  插入TblTest （Entno，Posdate）& 
  值（'&  1 &  '，'& TbxPostDate.Text&  '） 
 
 Cmd.ExecuteNonQuery（）
 DBCnClose（）
  Catch  ex  As 异常
 ErrorMsg = ex.Message 
 System.IO .File.AppendAllText（  d：\ Log\Log.text，ex.ToString& ; vbNewLine& vbNewLine）
 MsgBox（ErrorMsg）
 结束 尝试 
 
 DBCnClose（）
 
 结束  Sub  

解决方

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



连接字符串时会导致问题，因为SQL会收到如下命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  Baker' s Wood ' < span class =code-string>  

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x';  DROP  表 MyTable;   -   ' 

哪个SQL看作三个单独的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x'; 

完全有效的SELECT

  DROP   TABLE  MyTable; 

完全有效的删除表格通讯和

   -   ' 

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。您是否定期进行备份，不是吗？



因此，使用DateTime.TryParse将文本框转换为实际的DateTime对象 - 将任何问题报告给用户 - 然后通过查询参数直接将DateTime传递给您的数据库。

 Dim postDate As DateTime 
 
 If Not DateTime.TryParse（TbxPostDate，postDate）然后
 ...向用户报告问题
返回
结束如果
 

使用con As New SqlConnection（strConnect）
 con.Open（）
使用com作为新的SqlCommand（INSERT INTO myTable（myColumn1，myColumn2）VALUES（@ C1，@ C2），con）
 com.Parameters.AddWithValue（@ C1，myValueForColumn1）
 com.Parameters.AddWithValue（@ C2，myValueForColumn2）
 com.ExecuteNonQuery（）
 End using 
 End使用
 

SQL注入是在20多年前确定的，为什么我们每天都会看到易受影响的代码？



最好的办法是将值读入正确类型的变量并验证它们是否在您想要的范围内。



然后使用参数化查询将这些值添加到SQL命令中。这样做的好处是，当您以这种方式添加值时，ADO将知道什么类型的值，以便它可以正确格式化命令。

  //  你需要转置到VB  
 
 DateTime Posdate =（DateTime）TbxPostDate.Text;  //  我之前会验证这个 
 
 Cmd.CommandText =   INSERT TblTest（Entno，Posdate）VALUES（1，@ Postdate）; 
 Cmd.Paramaters.AddWithValue（  @ Posdate，Posdate）; 

i am trying to insert date in SQL table with date datatype column from text box formatted to "DD/MM/YYYY"

1. if the date is 01/11/2018 it shows in sql table as 2018-01-11

2. if date is 16/11/2018 it gives error conversion failed when converting date and /or time from character string.

What I have tried:

<

pre>Private Sub ButTest_Click(sender As Object, e As EventArgs) Handles ButTest.Click
        Dim ErrorMsg As String

        Try

            Call DBCnConnection()

            Dim Cmd As New SqlClient.SqlCommand

            Cmd.Connection = DBCnCommon

            Cmd.CommandText = " Insert Into TblTest(Entno,Posdate)" &
                "Values('" & 1 & "','" & TbxPostDate.Text & "')"

            Cmd.ExecuteNonQuery()
            DBCnClose()
        Catch ex As Exception
            ErrorMsg = ex.Message
            System.IO.File.AppendAllText("d:\Log\Log.text", ex.ToString & vbNewLine & vbNewLine)
            MsgBox(ErrorMsg)
        End Try

        DBCnClose()

    End Sub

解决方案

Not like that! 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. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

So use DateTime.TryParse to convert your textbox to an actual DateTime object - reporting any problem back to the user - and then pass the DateTime to your DB directly via a query parameter.

Dim postDate As DateTime

If Not DateTime.TryParse(TbxPostDate, postDate) Then
    ... report problem to user
    Return
End If

Using con As New SqlConnection(strConnect)
    con.Open()
    Using com As New SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con)
        com.Parameters.AddWithValue("@C1", myValueForColumn1)
        com.Parameters.AddWithValue("@C2", myValueForColumn2)
        com.ExecuteNonQuery()
    End Using
End Using

SQL Injection was identified over 20 years ago, why do we see code which is susceptible every day still?

The best thing to do would be to read the values into properly typed variables and validate that they are within the range that you want.

Then use Parameterized Queries to add these values to your SQL Command. The bonus to this is that when you add the values this way, ADO will know what types of values so it can format the command correctly.

// you will need to transpose to VB

DateTime Posdate = (DateTime)TbxPostDate.Text; // I would validate this prior

Cmd.CommandText = "INSERT TblTest(Entno,Posdate) VALUES (1, @Posdate)";
Cmd.Paramaters.AddWithValue("@Posdate", Posdate);

这篇关于在VB.NET中使用date数据类型在SQL列中插入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！