database 将数据从 VB.NET 插入 MS Access：INSERT INTO 语句中的语法错误

Inserting data from VB.NET to MS Access: Syntax error in INSERT INTO statement

提问by user3140727

I'm using Microsoft Visual Studio 2013 and im trying to make a registration form for my account database using VB.NET. This is my code so far:

我正在使用 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

The code compiles fine, but when i try to register any new information i get the following message:

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

    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.

What could be a solution and cause for this problem?

这个问题的解决方案和原因是什么？

回答by Fabio

Your query seems wrong: ... VALUES(usernme, passwrd)...-- Here the usernmeand passwrdare not variables for database, but just plain text in the query.

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

Use parameters, like this:

使用参数，如下所示：

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

回答by LarsTech

You aren't including the actual variable information missing the quotations, like

您不包括缺少引号的实际变量信息，例如

VALUES ('" & usernme & '", ...etc

You should be using parameters to avoid errors and sql injection:

您应该使用参数来避免错误和 sql 注入：

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

Dim cmd As New OleDbCommand(sqlQry, myconnection)
cmd.Parameters.AddWithValue("@usernme", usernme)
cmd.Parameters.AddWithValue("@passwrd", passwrd)
cmd.ExecuteNonQuery()

回答by user3682803

Dim cnn As New OleDb.OleDbConnection

Private Sub RefreshData()
    If Not cnn.State = ConnectionState.Open Then
        '-------------open connection-----------
        cnn.Open()
    End If

    Dim da As New OleDb.OleDbDataAdapter("select stdID as [StdIdTxt]," &
                                       "Fname as [FnameTxt] ,Lname,BDy,age,gender,address,email,LNO,MNO,course" &
                                       "from studentTB order by stdID", cnn)

    Dim dt As New DataTable
    '------------fill data to data table------------
    da.Fill(dt)



    'close connection
    cnn.Close()


End Sub



Private Sub AddNewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewBtn.Click
    Dim cmd As New OleDb.OleDbCommand

    '--------------open connection if not yet open---------------
    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If
    cmd.Connection = cnn

    '----------------add data to student table------------------
    cmd.CommandText = "insert into studentTB (stdID,Fname,Lname,BDy,age,gender,address,email,LNO,MNO,course)" &
        "values (" & Me.StdIdTxt.Text & "','" & Me.FnameTxt.Text & "','" & Me.LNameTxt.Text & "','" &
        Me.BdyTxt.Text & "','" & Me.AgeTxt.Text & "','" & Me.GenderTxt.Text & "','" &
        Me.AddTxt.Text & "','" & Me.EmailTxt.Text & "','" & Me.Hometxt.Text & "','" & Me.mobileTxt.Text & "','" & Me.Coursetxt.Text & "')"


    cmd.ExecuteNonQuery()

    '---------refresh data in list----------------
    'RefreshData()

    '-------------close connection---------------------
    cnn.Close()

回答by Hareesh S Haridasan

This insert error is nothing but a syntax error, there is no need for changing your code. please avoid reserved words like "password" form your database. This error is due to the field name password

这个插入错误只不过是一个语法错误，没有必要改变你的代码。请避免在您的数据库中使用诸如“密码”之类的保留字词。这个错误是由于字段名密码

回答by Lauren

The SQL string should look like this

SQL 字符串应如下所示

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

The values usernme & passwrd aren't valid to the database. Beyond that you really should look into using a Command object and parameters.

值 usernme 和 passwrd 对数据库无效。除此之外，您真的应该考虑使用 Command 对象和参数。