如何使用VB.NET将文本插入数据库表？ [英] How to INSERT text into database table using VB.NET ?

问题描述

我有一个名为testDB的数据库，VStudio已经连接并执行所有INSERT，DELETE和UPDATE操作。



我需要的是每当我使用new_dept_button_click添加一些部门时它会自动将NOT AVAILABLE文本添加到数据库的dept_modified列中。



数据库名称= mydatabase

表名= dept_table

列名称= dept_id，dept_name，dept_created，dept_modified。



因此我想在添加新内容时将NOT AVAILABLE文本添加到dept_modified中部门（new_dept_button_click）。



所以新部门添加了创建日期，但我无法在dept_modify中获得NOT AVAILABLE文本，因为部门刚刚创建未修改。



Ty了解我的英语不好。希望我能让你理解。



祝你好运我？



DGV应该在添加新系TEST之后看起来像



| ID | NAME |创建|修改|

---------------------------------------- -

| 1 |测试| 2/16/2018 | NA |

---------------------------------------- -



我尝试了什么：



Private Sub New_Department_Click（ sender As Object，e As EventArgs）Handles

 New_Department_Click.Click 
 connection = New SqlConnection 
 connection.ConnectionString =（< MY CONNECTION STRING>）
 Dim reader作为SqlDataReader 
尝试
 connection.Open（）
 Dim getDate As Date = Date.Now 
 Dim addDate As String = getDate.ToString（yyyy-MM-dd）
 Dim query As String 
 query =INSERT into department（dept_id，dept_name，dept_created，dept_modified）values（'"& D_ID_box.Text&'，'& D_NAME_BOX.Text& '，'& addDate&'，'NOT AVAILABLE'，ToString）
 command = New SqlCommand（query，connection）
 reader = command.ExecuteReader 
 MsgBox（ 部门 添加成功）
 load_dgv（）
 connection.Close（）
 Catch ex As Exception 
 MsgBox（ex.Message）
最后
 TextBox1.Text = String.Empty 
 TextBox2.Text = String.Empty 
 ComboBox1.Text = String.Empty 
 connection.Dispose（）
 End Try 
 End Sub 

解决方案

 query =   INSERT into department（dept_id，dept_name，dept_created，dept_modified）值（'& D_ID_box.Text&  '，'& D_NAME_BOX.Text&  '，'& addDate&  '，'  NOT  AVAILABLE < span class =code-comment>' ，ToString） 

我怀疑最后一个引用是错误的。



不是你问题的解决方案，而是你遇到的另一个问题。

永远不要构建一个SQL通过连接字符串进行查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

这已经部分回答，但我想补充几点：



- 如前所述，你不需要使用 ToString 来获取文字

 ...，  NOT AVAILABLE）'   

应该足够了，但如果插入值中的值始终相同，这也可以作为数据库中的默认值添加。 br />


- 比这更重要的是，永远不要将用户的输入作为文字连接到SQL语句。唯一正确的方法是使用参数以便安全地从SQL注入和帮助进行类型转换等。



- 第三件事是使用dispose或using。您应该始终丢弃一次性物品。我觉得最简单的方法是使用using语句而不是调用Dispose方法。例如，在您的代码中，您不会处理 SqlCommand 。



- 日期，永远不会将日期存储在任何日期中其他格式比日期列。在数据库中使用适当的数据类型时，可以轻松查询，修改，计算数据。因此，不要将日期转换为字符串，而是使用参数并按原样将 DateTime.Now 存储到其中。



- Dept_Id ，不确定这是否是密钥以及它应该如何处理，但是如果该列是作为表的实际主键，你可能应该让数据库来决定它的值，以防止重复。如果需要，请查看 IDENTITY 列，例如



- 通过调用<$执行insert语句C $ C>的ExecuteReader 。但是，由于这是一个insert语句，因此不希望从数据库返回任何内容。一个常见的做法是使用 ExecuteNonQuery



如果你有时间，我建议你去看看正确执行数据库操作 [ ^ ]。它解释了从不同的角度执行命令的基本原理。

我重新编码了你提供的代码片段并将其格式化得更好，并删除了一些冗余。



如果不包含代码，它的外观如下：

  INSERT   INTO 部门（dept_id，dept_name，dept_created，dept_modified）
  VALUES （  1， < span class =code-string> Department Name，  2018-2-15，' NOT AVAILABLE，ToString  ） 

正如您所知，NOT AVAILABLE不会显示为字符串（在Visual Studio中）导致我认为你已经看到但未发布的错误。使用 Strings.Format 方法，正如您所知，查询变得更易读，更容易编辑或更新。我还使用了创建SqlConnection时的对象初始值设定项（名为With）。下面提供的代码可以解决您遇到的任何问题。希望我帮忙！

  Dim  conn 作为 新 SqlConnection（）使用 {.ConnectionString =  < string>} 
  Dim  reader  As  SqlDataReader 
 尝试 
 conn.Open（）
 < span class =code-keyword> Dim  strDate  As   String  = DateTime.Now。 ToString（  yyyy-MM-dd）
  Dim 查询作为 字符串 =    INSERT INTO部门（dept_id，dept_name，dept_created，dept_modified） VALUES（ 
 query = query& 字符串 .Format（  {0}，{1} ，{2}，'未修改'），
 D_ID.box.Text，D_NAME_BOX.Text，strDate）
  Dim  command  As   new  SqlCommand（query，conn）
 command.ExecuteReader（）
 load_dgv（）
 conn.Close（）
  Catch  ex  As 异常
 MessageBox.Show（ex.Message）
 最后 
 TextBox1.Clear（）：TextBox2.Clear（）：TextBo3.Clear（ ）
 ComboBox1.Text =  String  .Empty 
 conn.Dispose（）
 MessageBox.Show（ 部门已成功添加。）
 结束 尝试 

I have a database called testDB and VStudio already connected and doing all INSERT, DELETE and UPDATE operations.

What I needed is whenever I add some department using new_dept_button_click" its automatically add "NOT AVAILABLE" text into dept_modified column on database.

Database Name = mydatabase
Table Name = dept_table
column Names = dept_id,dept_name,dept_created,dept_modified.

so I want to add "NOT AVAILABLE" text into 'dept_modified' when I add new department(new_dept_button_click).

So new department added the date of creation is showing but Im unable to get "NOT AVAILABLE" text in dept_modify since the department just created not modified.

Ty for understanding my bad English. hopefully i can make you understand.

Any luck for me?

DGV should looks like after adding new Department "TEST"

| ID | NAME | CREATED | MODIFIED |
------------------------------------------
| 1 | TEST | 2/16/2018 | N.A |
------------------------------------------

What I have tried:

Private Sub New_Department_Click(sender As Object, e As EventArgs) Handles

New_Department_Click.Click
        connection = New SqlConnection
        connection.ConnectionString = ("<MY CONNECTION STRING>")
        Dim reader As SqlDataReader
        Try
            connection.Open()
            Dim getDate As Date = Date.Now
            Dim addDate As String = getDate.ToString(" yyyy-MM-dd ")
            Dim query As String
            query = "INSERT into department(dept_id,dept_name,dept_created,dept_modified) values ('" & D_ID_box.Text & "','" & D_NAME_BOX.Text & "','" & addDate & "','"NOT AVAILABLE',ToString")"
            command = New SqlCommand(query, connection)
            reader = command.ExecuteReader
            MsgBox("Department added successfully")
            load_dgv()
            connection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            TextBox1.Text = String.Empty
            TextBox2.Text = String.Empty
            ComboBox1.Text = String.Empty
            connection.Dispose()
        End Try
    End Sub

解决方案

query = "INSERT into department(dept_id,dept_name,dept_created,dept_modified) values ('" & D_ID_box.Text & "','" & D_NAME_BOX.Text & "','" & addDate & "','"NOT AVAILABLE',ToString")"

I suspect one of the last quotes to be wrong.

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]

This has been already partially answered, but I'd like to add a few points:

- As already said, you don't need to use ToString for a literal

... ,"NOT AVAILABLE")'

should be sufficient, although this could also be added as a default in the database if the value is always the same in the insert.

- More important than that is that never ever concatenate the input from the user as literal to the SQL statement. The only correct way is to use parameters in order to be safe from SQL injection and help with type conversions etc.

- Third thing is the usage of dispose or using. You should always dispose the disposable objects. I feel that the easiest way is to use using statements instead of calling the Dispose method. For example in your code you do not dispose the SqlCommand.

- Dates, never store the dates in any other format than in a date column. When using proper data types in the database it's easy to query, modify, calculate with the data. So instead of converting the date into a string, use a parameter and store DateTime.Now into it as-is.

- Dept_Id, not sure if this is the key and how it's supposed to be handled, but if this column is intended to be the actual primary key for the table, you probably should let the database to decide the value for it in order to prevent duplicates. If needed, have a look at IDENTITY columns for example

- The insert statement is executed by calling ExecuteReader. However, as this is an insert statement, nothing is expected to be returned from the database. A commonn practice is to use ExecuteNonQuery

If you have time, I'd suggest going through Properly executing database operations[^] . It explains the fundamentals for executing commands from different point of views.

I re-coded the snippet you gave and formatted it a little better as well as removing some redundancies.

Here is how it would look without code included:

INSERT INTO department(dept_id, dept_name, dept_created, dept_modified) 
  VALUES ( "1", "Department Name", "2018-2-15", '"NOT AVAILABLE',ToString")

As you can tell, the "NOT AVAILABLE" would not be shown as a string (within Visual Studio) which leads to the errors I presume you have seen but not posted. By using Strings.Format method, as you can tell, the query becomes much more readable and easy to edit or updated. I also used the an object initializer (named With) when creating the SqlConnection. The code provided below should fix any problems you've had. Hope I helped!

Dim conn As New SqlConnection() With { .ConnectionString = "<string>" }
Dim reader As SqlDataReader
Try
    conn.Open()
    Dim strDate As String = DateTime.Now.ToString("yyyy-MM-dd")
    Dim query As String = "INSERT INTO department(dept_id, dept_name, dept_created, dept_modified) VALUES ("
    query = query & String.Format("{0}, {1}, {2}, 'NOT MODIFIED')",
                                  D_ID.box.Text, D_NAME_BOX.Text, strDate)
    Dim command As new SqlCommand(query, conn)
    command.ExecuteReader()
    load_dgv()
    conn.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    TextBox1.Clear() : TextBox2.Clear() : TextBo3.Clear()
    ComboBox1.Text = String.Empty
    conn.Dispose()
    MessageBox.Show("Department added successfully.")
End Try

这篇关于如何使用VB.NET将文本插入数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！