database - 将数据从VB.NET插入到MS Access : Syntax error in INSERT INTO statement [duplicate]

This question already has an answer here:

Syntax error in INSERT INTO Statement when writing to Access

(1个答案)


5年前关闭。




我正在使用Microsoft Visual Studio 2013，并且尝试使用VB.NET为我的帐户数据库创建注册表单。到目前为止，这是我的代码:

Private Sub btnRegistery_Click(sender As Object, e As EventArgs) Handles btnRegistery.Click
    Dim usernme, passwrd As String
    usernme = txtUsernm.Text
    passwrd = txtpasswrd.Text

    Dim myconnection As OleDbConnection
    Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\hasan\Documents\Visual Studio 2012\Projects\hasan\Login_Info.accdb"
    myconnection = New OleDbConnection(constring)
    myconnection.Open()

    Dim sqlQry As String

    sqlQry = "INSERT INTO tbl_user(username, password) VALUES(usernme , passwrd)"

    Dim cmd As New OleDbCommand(sqlQry, myconnection)
    cmd.ExecuteNonQuery()
End Sub

该代码可以很好地编译，但是当我尝试注册任何新信息时，我收到以下消息:

    A first chance exception of type 'System.Data.OleDb.OleDbException'
    occurred in System.Data.dll
    Additional information: Syntax error in INSERT INTO statement.
    If there is a handler for this exception, the program may be safely continued.

什么是解决方案并导致此问题？

最佳答案

您的查询似乎有误:... VALUES(usernme, passwrd)...-
这里usernme和passwrd不是数据库的变量，而只是查询中的纯文本。

使用参数，如下所示:

Dim usernme, passwrd As String
usernme = txtUsernm.Text
passwrd = txtpasswrd.Text
Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\hasan\Documents\Visual Studio 2012\Projects\hasan\Login_Info.accdb"
Using myconnection As New OleDbConnection(constring)
    myconnection.Open()
    Dim sqlQry As String = "INSERT INTO [tbl_user] ([username], [password]) VALUES (@usernme, @passwrd)"
    Using cmd As New OleDbCommand(sqlQry, myconnection)
        cmd.Parameters.AddWithValue("@usernme", usernme)
        cmd.Parameters.AddWithValue("@passwrd", passwrd)
        cmd.ExecuteNonQuery()
    End using
End using