database - '在 SQL 语句结束后找到的字符。'

Question

首先，我想澄清一下，我正在尝试编写一个注册表单，它将用户在文本框中输入的任何内容插入到访问数据库的字段中。但是，我遇到了一个奇怪的错误，告诉我在 SQL 语句之后插入一个分号，我这样做了，因此收到另一个错误，告诉我这是一个无效字符。为了解决这个问题，我正确插入了分号......在）之后sqlRegister = sqlRegister & txt_email.Text & "' );"

现在，我收到一个新错误： System.Data.OleDb.OleDbException: 'Characters found after end of SQL statement.'

这是我的 runSQL 模块：

Public Function runSQL(ByVal query As String) As DataTable 'connection variable is declared to establish connection to database
    Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=library_db.accdb")
    'dt variable is declared as a new table where data from the access database will be stored
    Dim dt As New DataTable 'variable declared to search through list of records in access database
    Dim DataAdapter As OleDb.OleDbDataAdapter

    Connection.Open() 'Initialise the connection to the access database
    DataAdapter = New OleDb.OleDbDataAdapter(query, Connection) 'Retrieves records as instructed by query
    dt.Clear() 'The data of the data table is cleared and filled with data retrieved by the DataAdapter
    DataAdapter.Fill(dt)
    Connection.Close() 'Connection to the access database is closed to reduce the number of open connections
    Return dt
End Function 
End Module

这是我的注册表：

        username = txt_user.Text
        password = txt_pass.Text
        email = txt_email.Text

        If txt_user.Text = "" Or txt_pass.Text = "" Or txt_email.Text = "" Or emailCheck(txt_email.Text) Then
            MsgBox("Please fill in all boxes in order to register")
        Else
            sqlRegister = "Insert into accounts(userID, "
            sqlRegister = sqlRegister & "Username, [Password], Email) values (' "
            sqlRegister = sqlRegister & userID & "', '"
            sqlRegister = sqlRegister & txt_user.Text & "', '"
            sqlRegister = sqlRegister & txt_pass.Text & "', '"
            sqlRegister = sqlRegister & txt_email.Text & "');'"
            runSQL(sqlRegister)
            MessageBox.Show("Registered!")

        End If

标签： databasevb.netms-access

Answer 1

为了使您的数据访问代码和用户界面代码分开，您不应在用户界面中构建 sql 查询，只需将数据传递给数据访问代码。

ADO.net 提供的连接和其他几个类在内部使用非托管代码。他们有.Dispose释放这些资源的方法。编码器负责调用该.Dispose方法。幸运的是，.net 提供了Using...End Using块来为我们处理这个问题。

您没有检索数据，因此不需要 aDataTable或DataAdapter。始终使用参数来避免 sql 注入。当您有一个自动编号字段时，您不会传递任何值。该值由数据库提供。您必须检查数据库中字段的数据类型和大小，因为我不得不猜测。您正在插入一个用户，因此您不想.ExecuteNonQuery使用数据表.Fill。

看来您正在将密码保存为纯文本。即使在示例代码中也不应该这样做，但那是为了您的研究。

Public Sub InsertUser(UName As String, PWord As String, EMail As String)
    Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=library_db.accdb"),
            cmd As New OleDbCommand("Insert Into accounts (UserName, [Password], Email) Values (@UName, @PWord, @Mail);", cn)
        cmd.Parameters.Add("@UName", OleDbType.VarChar, 100).Value = UName
        cmd.Parameters.Add("@PWord", OleDbType.VarChar, 100).Value = PWord
        cmd.Parameters.Add("@Mail", OleDbType.VarChar, 200).Value = EMail
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Private Sub OPCode()
    If txt_user.Text = "" OrElse txt_pass.Text = "" OrElse txt_email.Text = "" OrElse emailCheck(txt_email.Text) Then
        MsgBox("Please fill in all boxes in order to register")
        Exit Sub
    End If
    Try
        InsertUser(txt_user.Text, txt_pass.Text, txt_email.Text)
        MessageBox.Show("Registered!")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub