CODEWITHSUNDEEP
databasevb.netoledb
MaylorTaylor
MaylorTaylor

Reputation: 5051

Insert data into oleDb table

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.

 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.

Upvotes: 0

Views: 9015

Answers (2)

Steve
Steve

Reputation: 216352

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))

Upvotes: 1

Mike Dinescu
Mike Dinescu

Reputation: 55760

It looks like you are combining two concepts/objects there. You could insert the records just using the 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()

Upvotes: 0

Related Questions