vb.net 向oleDb表中插入数据

Insert data into oleDb table

提问by MaylorTaylor

I'm working on my first database program and I am having trouble having the data be updated inside the access database. Here is the code for the 'add criteria' button on the winform.

我正在开发我的第一个数据库程序，但在访问数据库中更新数据时遇到了问题。这是 winform 上“添加条件”按钮的代码。

 Dim TaxConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ConfigurationManager.AppSettings("Database")
    Dim dbConnection As OleDbConnection = New OleDbConnection(TaxConnStr)
    Dim dt As New DataTable
    Dim da As New OleDbDataAdapter

    Try
        dbConnection.Open()

        Dim cmd As New OleDbCommand
        cmd.CommandText = "INSERT INTO UserCriteria ( UserName, State, County, Type, Amount, Rank) VALUES ( @UserName, @State, @County, @Type, @Amount, @Rank)"
        Me.DataGridView1.DataSource = dt
        cmd.ExecuteNonQuery()

        dt.Columns.Add("UserName")
        dt.Columns.Add("State")
        dt.Columns.Add("County")
        dt.Columns.Add("Type")
        dt.Columns.Add("Amount")
        dt.Columns.Add("Rank")
        dt.Rows.Add(New String() {
                                            boxAssignTo.Text, _
                                            boxState.Text, _
                                            boxCounty.Text, _
                                            boxAmount.Text, _
                                            boxType.Text, _
                                            boxRank.Text})





    Catch ex As Exception
    Finally
        dbConnection.Close()
    End Try

I know i'm missing something or doing it wrong, So i am asking for you guru's guidance.

我知道我遗漏了一些东西或做错了，所以我请求你大师的指导。

回答by Steve

Something is very wrong in your code.

您的代码中有些地方非常错误。

Try with this

试试这个

Dim TaxConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                            ConfigurationManager.AppSettings("Database")
Using dbConnection = New OleDbConnection(TaxConnStr)
    Dim dt As New DataTable
    Dim da As New OleDbDataAdapter
    Try
        dbConnection.Open()
        Dim cmd As New OleDbCommand
        cmd.CommandText = "INSERT INTO UserCriteria ( UserName, State, County, " & _
                          "Type, Amount, Rank) VALUES " & _
                          "(@UserName, @State, @County, @Type, @Amount, @Rank)"
       cmd.Parameters.AddWithValue("@UserName", boxAssignTo.Text)
       cmd.Parameters.AddWithValue("@State", boxState.Text)
       cmd.Parameters.AddWithValue("@County", boxCounty.Text)
       cmd.Parameters.AddWithValue("@Type", boxType.Text)
       cmd.Parameters.AddWithValue("@Amount", boxAmount.Text)
       cmd.Parameters.AddWithValue("@Rank", boxRank.Text)
       cmd.ExecuteNonQuery()
   Catch ex As Exception
   Finally
       dbConnection.Close()
   End Try
End Using

As you can see to add a record inside a database table you use a OleDbCommand with a command text and set its parameters to the values expected in the database table.
There is also the problem of the datatype of your columns. From your example it is not possible to infer the datatype, but probably Type, Rank and Amount are numeric columns and so you need to convert the textbox value to an appropriate number (Convert.ToInt32(textbox.text))

如您所见，要在数据库表中添加记录，您可以使用带有命令文本的 OleDbCommand 并将其参数设置为数据库表中预期的值。
还有列的数据类型问题。从您的示例中，无法推断数据类型，但可能 Type、Rank 和 Amount 是数字列，因此您需要将文本框值转换为适当的数字 (Convert.ToInt32(textbox.text))

回答by Mike Dinescu

It looks like you are combining two concepts/objects there. You could insert the records just using the OleDbCommand:

看起来您在那里组合了两个概念/对象。您可以仅使用 OleDbCommand 插入记录：

Dim cmd As New OleDbCommand
cmd.CommandText = "INSERT INTO UserCriteria ( UserName, State, County, Type, Amount, Rank) VALUES ( @UserName, @State, @County, @Type, @Amount, @Rank)"
cmd.Parameters.Add(new OleDbParameter("@UserName", DbType.String))
cmd.Parameters.Add(new OleDbParameter("@State", DbType.String))
cmd.Parameters.Add(new OleDbParameter("@County", DbType.String))
cmd.Parameters.Add(new OleDbParameter("@Type", DbType.String))       ' DbType depends on column type in database
cmd.Parameters.Add(new OleDbParameter("@Amount", DbType.Integer))    ' DbType depends on column type in database
cmd.Parameters.Add(new OleDbParameter("@Rank", DbType.Integer))      ' DbType depends on column type in database

cmd.Prepare();   

cmd.Parameters("@UserName").Value = boxAssignTo.Text
cmd.Parameters("@State").Value = boxState.Text
cmd.Parameters("@County").Value = boxCounty.Text
cmd.Parameters("@Type").Value = boxType.Text
cmd.Parameters("@Amount").Value = boxAmount.Text
cmd.Parameters("@Rank").Value = boxRank.Text

cmd.ExecuteNonQuery()