无法将数据插入ACCESS数据库。 [英] Trouble inserting data into ACCESS database.

问题描述

我在将数据插入ACCESS数据库时遇到问题。下面的代码是我正在使用的，但它抛出异常。 查询值和目标字段的数量不一样。

I'm having trouble inserting data into an ACCESS database. The code below is what I'm using but it's throwing an exception. Something along the lines of "Number of query values and destination fields are not the same."

<pre lang="vb"> Dim cmd As OleDbCommand = New OleDbCommand

        cmd.CommandType = CommandType.Text
        cmd.Connection = myConnection
        cmd.CommandText = "INSERT INTO users VALUES ('" & tbUsername.Text & "', '" & tbPassword1.Text & "', '" & cbRole.SelectedItem & "');"

        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "username"
        cmd.Parameters.Item("username").Value = tbUsername.Text
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "pwd"
        cmd.Parameters.Item("pwd").Value = tbPassword1.Text
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "role"
        cmd.Parameters.Item("role").Value = cbRole.SelectedItem

        myConnection.Open()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message)
        End Try

        myConnection.Close()

任何想法都会有所帮助。我在这里完全失败了...



我尝试过的事情：



我曾尝试将列添加到SQL语句中，但之后它说有语法错误。我检查了ACCESS保留字列表，我没有将它们用作表或列名。



我已多次浏览此代码，看起来它应该可以工作，我已经验证表中只有3列，我只传递了三个变量写入表格。

Any thoughts would be helpful. I am at a complete loss here...

What I have tried:

Ive tried adding the columns to the SQL statement but then it says there's a syntax error. Ive checked the ACCESS reserved words list and I'm not using those as table or column names.

Ive walked through this code multiple times and it seems like it should work and Ive verified there are only 3 columns in the table and I'm only passing three variables to be written into the table.

推荐答案

这可能是因为，虽然你已经为参数添加了值，但在查询中你实际上并没有使用这些参数。

将您的查询修改为以下内容并检查是否有帮助 -

That's could be because, although you have added values to parameters but in the query you are not actually using those parameters.
Modify your query to something like following and check if that helps-

cmd.CommandText = "INSERT INTO users VALUES (@username, @pwd, @role);"

希望，它有帮助：）

Hope, it helps :)

我实际修好了这个：

I actually fixed it like this:

cmd.CommandText = "INSERT INTO users (username,pwd,role) VALUES ('" + tbUsername.Text + "','" + tbPassword1.Text + "','" + cbRole.SelectedItem + "')"

我添加了列名。我之前做过这个，但是我不认为它在逗号后面是空格。

I added the column names. I did this before, but I don't think it likes spaces after the commas.

这是我使用的最终代码，它正在工作......不确定如何或为什么。

This is the final code that I went with and it's working... not sure how or why.

Dim cmd As OleDbCommand = New OleDbCommand

        cmd.CommandType = CommandType.Text
        cmd.Connection = myConnection
        cmd.CommandText = "INSERT INTO users (username,pwd,role) VALUES ('" + tbUsername.Text + "','" + tbPassword1.Text + "','" + cbRole.SelectedItem + "')"

        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "username"
        cmd.Parameters.Item("username").Value = tbUsername.Text
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "pwd"
        cmd.Parameters.Item("pwd").Value = tbPassword1.Text
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "role"
        cmd.Parameters.Item("role").Value = cbRole.SelectedItem

        myConnection.Open()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message)
            Exit Sub
        End Try

        myConnection.Close()

        MsgBox("User information has been saved.")

        fill_listbox()
        tbUsername.Clear()
        tbPassword1.Clear()
        tbPassword2.Clear()
        cbRole.Items.Clear()
        tbUsername.Focus()

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