locked
Unable to Insert Data into Access Database using OLEDB RRS feed

  • Question

  • User1427713770 posted

    I have a form wilh couple of Drop downs and text boxes and a Data Grid View which displays data from Access Table. When I hit add button on the form, I can see the data being added to the Grid View. But when I close the form and open the Access Database File (.mdb), the respective table is empty. The Access Table is not being updated. Second time if I open the form, the Grid View is also empty.

    I am pasting my code here. Please let me know if I am doing anything wrong

    Public Class Home
        Shared OleDbConnection As System.Data.OleDb.OleDbConnection
        Shared ExpensesDataAdapter As System.Data.OleDb.OleDbDataAdapter

    Private Sub ButtonAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonAdd.Click
            Connect()
            InsertData(subcategory, category, description, amount, store)
            LoadDataGrid()
            Disconnect()

    End Sub

    Shared Sub Connect()
            OleDbConnection = New System.Data.OleDb.OleDbConnection()
            OleDbConnection.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source = ExpenseDB.mdb"
            Try
                OleDbConnection.Open()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    End Sub

    Shared Sub Disconnect()
            Try
                OleDbConnection.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    End Sub

    Public Sub InsertData(ByVal subcategory As String, ByVal category As String, ByVal description As String, ByVal amount As String, ByVal store As String)
            Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
            OleDbUpdateCommand = New System.Data.OleDb.OleDbCommand

            OleDbUpdateCommand.CommandText = _
                    "Insert into expensedetails (subcategory,category,description,amount,store,transdate) values" & _
                    "('" + subcategory + "','" + category + "','" + description + "','" + amount + "','" + store + "','" + DateTimePicker1.Text.ToString + "')"

            OleDbUpdateCommand.Connection = OleDbConnection
            ExpensesDataAdapter.UpdateCommand = OleDbUpdateCommand
            ExpensesDataAdapter.UpdateCommand.ExecuteNonQuery()
            LoadDataGrid()
    End Sub

    Public Sub LoadDataGrid()
            DataGridViewExpenses.DataSource = GetDetails("select * from expensedetails")
    End Sub

    End Class

    Thursday, September 2, 2010 11:07 AM

Answers

  • User-1828494216 posted

    <strike>Becouse you have to call DataBind()</strike>

    Create blank project and try my code again. Allso make sure your parameters get their values. Is the event firing at all? Do some checking...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 2, 2010 5:19 PM

All replies

  • User-1828494216 posted

    <strike>change </strike>

    <strike>ExpensesDataAdapter.UpdateCommand.ExecuteNonQuery()</strike>

    <strike>
    with</strike>

    <strike>OleDbUpdateCommand.ExecuteNonQuery()</strike>


    Neverming this, found multiple error, doing sample right now...

    Thursday, September 2, 2010 12:17 PM
  • User-1828494216 posted

    this is how to do it:

                Dim conn As New OleDbConnection
                conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source = ExpenseDB.mdb"
                Dim queryString As String = "Insert into expensedetails (subcategory,category,description,amount,store,transdate) values" & _
                    "(@subcategory,@category,@description,@amount,@store,@DateTimePicker1)"
                Using conn
                    Dim command As New OleDbCommand(queryString, conn)
    
                    'WRITE ALL PARAMETERS LIKE THIS:
                    command.Parameters.Add("@subcategory", OleDbType.Char, 50).Value = subcategory
                    command.Parameters.Add("@category", OleDbType.SmallInt, 50).Value = category
                    command.Parameters.Add("@DateTimePicker1", OleDbType.Date, 50).Value = DateTimePicker1.Text
    
                    conn.Open()
                    command.ExecuteNonQuery()
                    conn.Close()
                End Using
                DataGridViewExpenses.DataSource = GetDetails("select * from expensedetails")


    I don't know you paramter types so you should set them up yourselfe.


    i'm allso not sure what is this for

    ExpensesDataAdapter.UpdateCommand = OleDbUpdateCommand
            ExpensesDataAdapter.UpdateCommand.ExecuteNonQuery()



    Thursday, September 2, 2010 12:28 PM
  • User1427713770 posted

    I have tried your code. But its the same. I can see the data grid populated but database is not getting updated once i rebuild the project.

    Please help.


    Thursday, September 2, 2010 5:16 PM
  • User-1828494216 posted

    <strike>Becouse you have to call DataBind()</strike>

    Create blank project and try my code again. Allso make sure your parameters get their values. Is the event firing at all? Do some checking...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 2, 2010 5:19 PM