无法将数据添加到Access数据库 [英] Can't add data to Access database

问题描述

由于某种原因，当我尝试向数据库中添加数据时，我会不断得到：

For some reason when I try to add data to my database I keep getting:

System类型的首次机会例外。 Data.OleDb.OleDbException发生在System.Data.dll中。

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

其他信息：SQL语句末尾缺少分号（;）。

Additional information: Missing semicolon (;) at end of SQL statement.

这是我的代码。现在，我已经安装了数据库引擎，并将cpu更改为x86。

Here is my code. Now I have installed the database engine, and I have changed the cpu to x86.

Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click


    If DirectCast(AdminCheckBox, CheckBox).Checked = True Then

        Dim result1 As Integer
        Dim cmd As New OleDb.OleDbCommand
        cnn = New OleDb.OleDbConnection
        cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & "C:\Users\daNo\Documents\Visual Studio 2013\Projects\WindowsApplication1\WindowsApplication1\bin\chem.accdb;"

        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()

        End If

        result1 = MsgBox("Are you sure you want to add this user a admin?", MsgBoxStyle.YesNo, "Add New User")

        If result1 = vbYes Then

            cmd.Connection = cnn
            cmd.CommandText = "INSERT INTO Users(UserName, [password], Email) " & _
                              "VALUES(?,?,?)'"

            cmd.Parameters.AddWithValue("@p1", Me.UserNameTextBox.Text)
            cmd.Parameters.AddWithValue("@p2", Me.PasswordTextBox2.Text)
            cmd.Parameters.AddWithValue("@p3", Me.EmailTextBox.Text)

            cmd.ExecuteNonQuery()

            MsgBox("Account has been created!", MsgBoxStyle.OkOnly, "Add New User")

            Dim ACCSETTINGS As New ACCSETTINGS
            ACCSETTINGS.Show()
            Me.Hide()

            Me.Close()

        ElseIf result1 = vbNo Then

            Dim NEWUSER As New NewUser
            NEWUSER.Show()
            Me.Hide()

        End If

    End If

推荐答案

用户名，密码和电子邮件是文本字段，因此您需要传递sing中包含的值报价。您的命令在构建命令的字符串串联中的值周围缺少这些引号。像这样的东西

Username, Password and EMail are text fields, so you need to pass the values for them enclosed in single quotes. Your command misses these quotes around the values in the string concatenation that build the command. Something like this

"INSERT INTO Users(UserName, [password], Email) VALUES('" & Me.UserNameTextBox.Text & "'....

但这是构造sql命令的一种非常糟糕的方法。您应该始终使用参数化查询

but this is a really bad way to construct an sql command. You should ALWAYS use a parameterized query

Dim result1 = MsgBox("Are you sure you want to add this user a admin?", MsgBoxStyle.YesNo, "Add New User")
If result1 = vbYes Then
    Using cnn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;.....")
    Using cmd = new OleDb.OleDbCommand("INSERT INTO Users(UserName, [password], Email) " & _
                                        "VALUES(?,?,?)"
        cnn.Open()
        cmd.Parameters.AddWithValue("@p1",Me.UserNameTextBox.Text)
        cmd.Parameters.AddWithValue("@p2",Me.PasswordTextBox2.Text)
        cmd.Parameters.AddWithValue("@p3",Me.EmailTextBox.Text)
        Dim rowAdded = cmd.ExecuteNonQuery()
        if rowAdded > 0 Then
              MessageBox.Show("Account has been created!")
              ....
        Else
              MessageBox.Show("Problems!")
        End If
End If

参数化查询消除了通过连接输入片段来构造复杂查询文本的需要用户的值，添加字符串所需的引号（最终解析为嵌入的引号），在需要时检查正确的小数点分隔符，以正确的格式传递日期，但是最重要的是，参数化查询消除了SQL注入，您的恶意用户在其中特别准备格式化的字符串可能会破坏数据库。

Parameterized query removes the need to construct complex query text by concatenating pieces of input values from your user, adding the required quotes for strings (eventually parsing for embedded quotes), checking for correct decimal separator when needed, passing dates in correct format, but the most important thing, a parameterized query removes the possibility of Sql Injection where your malicious user prepare special formatted string that could wreak havoc the database.

作为补充说明。从安全角度来看，以明文形式存储密码确实是一个坏习惯。根据您的应用程序的上下文，这是不可轻视的事情。如果有人可以获取您数据库文件的副本，那么他可以读取存储的每个用户密码。

As a side note. Storing passwords in clear text is really a bad practice from a security standpoint. Depending on the context of your application this is a thing to not downplay. If someone could grab a copy of your database file he could read every user password stored.

EDIT

再次检查连接字符串中使用的字符串来设置数据库名称，这似乎是错误的。

Checking again the string used in the connection string to set the database name it appears really wrong.

在这里，为了可读性而拆分

Here, splitted for readability

"...;Data Source=" & Application.StartupPath & "C:\Users\daNo\Documents\" & _
"Visual Studio 2013\Projects\WindowsApplication1\WindowsApplication1\bin\chem.accdb;..."

这将导致错误的数据库路径。如果您在固定位置存储accdb文件，则可以删除该 Application.StartupPath

This results in a wrong path to the database. If you have a fixed location where you store the accdb file then you could remove that Application.StartupPath

这篇关于无法将数据添加到Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！