Update/ Add new record to Access Database from VB.Net?

Question

Hallo,

I have a following code which loads the database to datagridview without any problem. When i add new row to dataset and i try to update the database i get the following error "System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement'. Any idea how to correct this problem?. Thanks

Public Class Form1

 Public da As New OleDb.OleDbDataAdapter
 Public DS As New DataSet

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

      Dim CxnStr As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ProjectDB.accdb")

      Dim sql As String
      Dim cmd As New OleDb.OleDbCommand

            sql = "Select * from ProjectsDB"
            cmd.CommandText = sql
            da.SelectCommand = cmd

            da.Fill(DS, "Projects")


            Dim table As DataTable = DS.Tables("Projects")
            Dim column As DataColumn = table.Columns("ID")
            column.AutoIncrement = True
            column.ReadOnly = True
            column.Unique = True

            DS.Tables("Projects").PrimaryKey = New DataColumn() {DS.Tables("Projects").Columns("ID")}

            BS_Projects.DataSource = DS
            BS_Projects.DataMember = "Projects"
            ProjectBNavigator.BindingSource = BS_Projects
            DGV_Projects.DataSource = BS_Projects

            CxnStr.Close()

End Sub

Private Sub btn_saveProj_Click(sender As Object, e As EventArgs) Handles btn_saveProj.Click

BS_Projects.EndEdit()
Dim cb As New OleDb.OleDbCommandBuilder(da)
da.Update(DS.Tables("Projects"))    

End Sub

End Class

Answer 1

Here's a minimal bit of code to demo how to do it. In this case I'm assuming only 2 columns in your table. I'm assuming you are clicking a button outside the grid to save the changes so you are forcing the DGV to finish edit. I have broke the loading/saving of the data into separate functions so you can more easily drop them where you need them to go rather than mixing concerns.

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim ds = LoadData()

        BS_Projects.DataSource = ds
        BS_Projects.DataMember = "Projects"

        DGV_Projects.DataSource = BS_Projects
    End Sub

    Private Sub btn_saveProj_Click(sender As Object, e As EventArgs) Handles btn_saveProj.Click
        BS_Projects.EndEdit()

        SaveData(BS_Projects.DataSource)
        DGV_Projects.Refresh()
    End Sub

    Private Function LoadData() As DataSet
        Using conn As New OleDbConnection(connString)

            Dim selectCommand As New OleDbCommand("SELECT ID, Title FROM ProjectsDB", conn)
            Dim ds As New DataSet

            ' Load data
            Dim da As New OleDbDataAdapter(selectCommand)
            conn.Open()
            da.Fill(ds, "Projects")

            Return ds
        End Using
    End Function

    Private Sub SaveData(ds As DataSet)
        Using conn As New OleDbConnection(connString)

            Dim selectCommand As New OleDbCommand("SELECT ID, Title FROM ProjectsDB", conn)
            Dim da As New OleDbDataAdapter(selectCommand)

            ' For simple tables you can get the commands auto-generated, otherwise create the commands manually
            ' and set the appropriate properties on the da
            Dim builder As New OleDbCommandBuilder(da)
            da.InsertCommand = builder.GetInsertCommand()
            da.UpdateCommand = builder.GetUpdateCommand()
            da.DeleteCommand = builder.GetDeleteCommand()

            Dim changes = ds.GetChanges()

            ' Save data
            conn.Open()
            da.Update(ds, "Projects")
        End Using
    End Sub

    Private Const connString = "..."
End Class

Note that if you're using Access and you are putting the DB into the output directory of the project then ensure that each time you run the debugger you haven't configured it to overwrite the modified DB otherwise you will never see your changes.

Answer 2

Hi,
I think you have invalid characters (for SQL INSERT) in your column names in Access. In this case you must use quotes in command builder, like this:

 Private Sub btn_saveProj_Click(sender As Object, e As EventArgs) Handles btn_saveProj.Click

 BS_Projects.EndEdit()
 Dim cb As New OleDb.OleDbCommandBuilder(da)
    cb.QuotePrefix = "["     ' <--------------------------------------------'
    cb.QuoteSuffix = "]"     ' <--------------------------------------------'
 da.Update(DS.Tables("Projects"))    

 End Sub

Answer 3

The error indicates your INSERT command is incorrect. Since you are trying to insert/update/delete from the DB table you need to set the corresponding InsertCommand, UpdateCommand and DeleteCommand on the da data adapter you're using. You would normally do this when you're setting the SelectCommand.

   da.SelectCommand = cmd  
   da.InsertCommand = New OleDbCommand("INSERT INTO ProjectDB (Column1, Column2) VALUES (@col1, @col2)")  
   da.InsertCommand.Parameters.Add("@col1", OleDbType.VarChar, 80).Value = "SomeValue"  
   da.InsertCommand.Parameters.Add("@col1", OleDbType.Integer).Value = 20  
     
   da.UpdateCommand = ...  
   da.DeleteCommand = ...  

These tell the adapter how to do the insert/update/delete commands. The full docs can be found here.

Note also that you should generally not open the connection to the DB when your form loads and keep it open. Network connectivity or a long running app can cause problems. So you should really move your DB logic into a standalone type (or at least functions) that create the data adapter, open the connection, do the work and then close the connection. This will make your app resilient to network and database issues.

Answer 4

Hallo,

I have change my code but still not working. I am getting error "ExecuteNonQuery: Connection property has not been initialized"

Try
If CxnStr.State = ConnectionState.Closed Then CxnStr.Open()

            da.SelectCommand = cmd

            da.InsertCommand = New OleDbCommand("INSERT INTO ProjectsDB ProjectName,ProjNumber,Country,Location,BusinessUnit,ProjCurrency,Product,Note) VALUES (@ProjectName,@ProjNumber,@Country,@Location,@BusinessUnit,@ProjCurrency,@Product,,@Note)", CxnStr) 

            da.InsertCommand.Parameters.Add("@ProjectName", OleDbType.BSTR).Value = tbx_ProjectName.Text
            da.InsertCommand.Parameters.Add("@ProjNumber", OleDbType.BSTR).Value = tbx_ProjNumber.Text
            da.InsertCommand.Parameters.Add("@Country", OleDbType.BSTR).Value = cbx_country.Text
            da.InsertCommand.Parameters.Add("@Location", OleDbType.BSTR).Value = tbx_ProjLoc.Text
            da.InsertCommand.Parameters.Add("@BusinessUnit", OleDbType.BSTR).Value = cbx_bu.Text
            da.InsertCommand.Parameters.Add("@ProjCurrency", OleDbType.BSTR).Value = cbx_PCurrency.Text
            da.InsertCommand.Parameters.Add("@Product", OleDbType.BSTR).Value = tbx_product.Text               
            da.InsertCommand.Parameters.Add("@Note", OleDbType.BSTR).Value = tbx_Note.Text

            Dim i = cmd.ExecuteNonQuery
            If i > 0 Then
                MsgBox("New record has been inserted successfully!")
            Else
                MsgBox("No record has been inserted successfully!")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)

        Finally
            CxnStr.Close()
        End Try

Answer 5

I am still having problems and confusion.

FYI i have a primary key column in Database called ID.

I am Changing/updating my data via bindingsource (BS_Projects) to change my data and then i want to update my database. I also have separate databindings for textboxes like below so i can choose a project in a datagridview and edit them in textbox controls.

tbx_ProjectName.databindings.add("text", BS_Projects, "tbx_ProjectName")
...
....

I would prefer to update my database through bindingsource . what would be the procedure ?. Sorry for confusion.

Sample code posted here was an alternative method i tried both did not work .

Thanks