Syntax in INSERT INTO

[Ancient Dragon]

I have a program with DataGridView which is bound to MS Access table. When I addept to update I get "Syntax error in INSERT INTO", but no explanation about the syntax error. Column names in the table have a space in them, such as "Last Name". Could that be the problem? Most of this code comes from a tutorial I'm studying.

Public Class Form1
    Dim binding_source As New BindingSource()
    Dim data_table As New DataTable()
    Dim data_adapter As OleDb.OleDbDataAdapter
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.DataGridView1.DataSource = Me.binding_source
        Dim conString As String = My.Settings.MyContactsConnectionString
        Dim con As New OleDb.OleDbConnection(conString)
        con.Open()
        data_adapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contacts", con)
        Dim cb As New OleDb.OleDbCommandBuilder(data_adapter)

        data_adapter.Fill(data_table)
        binding_source.DataSource = data_table

    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Me.data_adapter.Update(CType(Me.binding_source.DataSource, DataTable))
        MessageBox.Show("Database Updated")
    End Sub
End Class

[scudzilla]

Wrap column names containing spaces with [ ].

[pritaeas]

Column names in the table have a space in them, such as "Last Name". Could that be the problem?

Yes, very likely. They'd have to be escaped in brackets. Not sure how to fix that though, unless you rename the columns.

[Reverend Jim]

I don't see an INSERT query anywhere but if I had to guess it would be the embedded spaces in the column names. It's always a bad idea to use names with spaces. With a non-spaced name you can do

INSERT INTO myTable (idnum,FirstName) VALUES(123,'Jim')

But when you have a space (or when you are using a reserved word) you have to use

INSERT INTO myTable (idnum,[First Name]) VALUES(123,'Jim')

I find it easier to use names like FirstName or First_Name.

[Ancient Dragon]

line 19 does the insert -- data_adapter.Update() does all that. It does INSERT for each row in the DataGridView that is not in the dataset that was read from the database, and UPDATE or DELETE for the others.

[edit]I removed the spaces in column names, deleted all rows, and attempted to add two new rows via the DataGridView -- now it produces syntax error in UPDATE statement!

[scudzilla]

Hmm try this after declaring cb and before filling data_table?

cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

[Ancient Dragon]

Problem solved: I repalces the spaces with '-' and SQL doesn't like that either. I just removed the space altogether and now everything is ok.

[Ancient Dragon]

I tried the suggestion that scudzilla posted and it still doesn't work after resetting the db back to it's oritinal state with spaced in column names. Update still fails.

Public Class Form1
    Dim binding_source As New BindingSource()
    Dim data_table As New DataTable()
    Dim data_adapter As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim cb As OleDb.OleDbCommandBuilder
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.DataGridView1.DataSource = Me.binding_source
        Dim conString As String = My.Settings.MyContactsConnectionString
        Dim con As New OleDb.OleDbConnection(conString)
        con.Open()
        data_adapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contacts", con)
        cb = New OleDb.OleDbCommandBuilder(data_adapter)
        cb.QuotePrefix = "["
        cb.QuoteSuffix = "]"

        data_adapter.Fill(data_table)
        binding_source.DataSource = data_table

    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Me.data_adapter.Update(CType(Me.binding_source.DataSource, DataTable))
        MessageBox.Show("Database Updated")
    End Sub
End Class

[Reverend Jim]

This is why I prefer to do (as a coworker used to call it) "buck naked" programming with ADO instead of adding all of the data adapter/data table layers. Too many layers between you and the data and too hard to find out where things are [m]ucking up.

[scudzilla]

Strange, it works for me though. Without the prefix and suffix, it gives me the same error. With the prefix and suffix, it gets updated correctly. I used your code exactly. What provider do you use for your oledb? What's your connectionstring and access version?

[Ancient Dragon]

Here is the app.conf file. This is for MS Access 235 (2013 for cloud), attached is the MS Access file.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="MyContactList.My.MySettings.MyContactsConnectionString"
            connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Users\Melvin\Documents\MyContacts.accdb"
            providerName="System.Data.OleDb" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

[scudzilla]

When I include the Attachments column, update generates an Update error regarding a multivalued field. Excluding Attachment, the database gets updated correctly.

[Ancient Dragon]

Oh, looking at the table with MS Access one of the columns is a list of 3 items. I used MS Access to add a row in the table, then tried to update it with my program -- update works. Insert a new row into the table doesn't work (An INSERT INTO query cannot contain a multivalued field). Anyone know a work-around?

[edit]Googling around a bit I see that is a common problem with newer versions of MS Access -- it's apparently the only one in the world that uses multivalued fields. Too bad that is such a problem with VB.NET. Guess I'll just get rid of that field.

[scudzilla]

Are you referring to Category? Adding a new row with any string for Category works. It's still a single valued field. It's the attachments that's the multivalued field. Excluding attachments from your select query will get no errors in updating or inserting. You'll have to tackle attachments in amother way if you want to include that in the program. Unfortunately, I'm currently on the road so I can't be of much use for a few hours.

[Ancient Dragon]

Got it all fixed now -- I removed that Attachments column from the db. After that, the next problem I encountered was "concurency error" when I attempted to update a second time. I found the solution to that one by refreshing the grid after update which apparently forces the dataset to flush itself. I only mentioned this just in case someone else has a similar "concurency" problem.

   Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Me.data_adapter.Update(CType(Me.binding_source.DataSource, DataTable))
        data_table.Clear()
        data_adapter.Fill(data_table)
        MessageBox.Show("Database Updated")
    End Sub