CODEWITHSUNDEEP
vb.netvisual-studioms-access
Pixel_
Pixel_

Reputation: 1

I cannot save new row into Microsoft Access Database via Visual Basic

This code works elsewhere in my program but in this form it is no longer working. I have come with the error

System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.'

This errors happens at the point

da.Update(ds, "EmployeeTable")

The rest of the code is below

Imports System.Data.OleDb
'Allows an connection of a type of Database to VB
Public Class frmAdmin
    ReadOnly dbConnection As New OleDb.OleDbConnection
    'Declares a variable that will handle the connection to Visual Basic
    ReadOnly ds As New DataSet
    'Declares the variable that will handle the datasets from the record
    Dim da As OleDb.OleDbDataAdapter
    'This declares the variable that will handle the data adapted that connects to the Database to VB
    Dim sql As String
    'A string that will handle SQL which tells the location of data

Private Sub BtnNewEmp_Click(sender As Object, e As EventArgs) Handles btnNewEmp.Click

        Dim strProvider As String
        Dim strSource As String
        Dim strConnString As String

        strProvider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        strSource = "../FuelAgencyDataBase.accdb"
        strConnString = strProvider & strSource
        dbConnection.ConnectionString = strConnString

        dbConnection.Open()

        sql = "SELECT * FROM EmployeeTable"
        da = New OleDbDataAdapter(sql, dbConnection)
        da.Fill(ds, "EmployeeTable")

        dbConnection.Close()

        For Each row In ds.Tables("EmployeeTable").Rows
            Dim Test As String = (row.item(1)) & " " + (row.item(2)) & " - " & (row.item(6))
            lbxHours.Items.Add(Test)
        Next

        Dim cb As New OleDbCommandBuilder(da)
        'Variable handles how Visual Studio connects to the database
        Dim dsNewRow As DataRow
        'Variable handles how new rows are added to the record

        dsNewRow = ds.Tables("EmployeeTable").NewRow()
        dsNewRow.Item(1) = StrFirstName + " " + StrLastName
        dsNewRow.Item(2) = StrUsername
        dsNewRow.Item(3) = StrPassword
        dsNewRow.Item(4) = StrProfession

        ds.Tables("EmployeeTable").Rows.Add(dsNewRow)

        da.Update(ds, "EmployeeTable")
        MsgBox("Employee has been added into the database")
    End Sub

I have a live connection to the database as other code has connected to the database. The table name is called "EmployeeTable". Can anybody else please find a solution to this error?

Upvotes: 0

Views: 106

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54457

This usually happens because one of your column names is a reserved word or contains spaces or some other special characters. When you use a command builder, it just copies the column names into the action commands as is. The two main options are as follows:

  1. Don't use a wildcard in your SELECT statement. Write out the column list in full and then you will need to escape any invalid column names and the command builder will follow suit.
  2. Set the QuotePrefix and QuoteSuffix properties of the command builder so that it wraps all column names in those characters and thus escapes the ones that need it. For Access, the values would be "[" and "]" respectively.

Upvotes: 1

Related Questions