1,105,380 Community Members

Syntax in INSERT INTO

Member Avatar
Ancient Dragon
Achieved Level 70
27,645 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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
Member Avatar
scudzilla
Posting Whiz
380 posts since Mar 2007
Reputation Points: 32 [?]
Q&As Helped to Solve: 41 [?]
Skill Endorsements: 1 [?]
 
0
 

Wrap column names containing spaces with [ ].

Member Avatar
pritaeas
mod_pritaeas
11,315 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
1
 

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.

Member Avatar
Reverend Jim
Noli mentula
5,447 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 653 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
1
 

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.

Member Avatar
Ancient Dragon
Achieved Level 70
27,645 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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!

Member Avatar
scudzilla
Posting Whiz
380 posts since Mar 2007
Reputation Points: 32 [?]
Q&As Helped to Solve: 41 [?]
Skill Endorsements: 1 [?]
 
1
 

Hmm try this after declaring cb and before filling data_table?

cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
Member Avatar
Ancient Dragon
Achieved Level 70
27,645 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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.

Member Avatar
Ancient Dragon
Achieved Level 70
27,645 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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
Member Avatar
Reverend Jim
Noli mentula
5,447 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 653 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

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.

Member Avatar
scudzilla
Posting Whiz
380 posts since Mar 2007
Reputation Points: 32 [?]
Q&As Helped to Solve: 41 [?]
Skill Endorsements: 1 [?]
 
0
 

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?

Member Avatar
Ancient Dragon
Achieved Level 70
27,645 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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>
Attachments MyContacts.zip (291.29KB)
Member Avatar
scudzilla
Posting Whiz
380 posts since Mar 2007
Reputation Points: 32 [?]
Q&As Helped to Solve: 41 [?]
Skill Endorsements: 1 [?]
 
0
 

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

Member Avatar
Ancient Dragon
Achieved Level 70
27,645 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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.

Member Avatar
scudzilla
Posting Whiz
380 posts since Mar 2007
Reputation Points: 32 [?]
Q&As Helped to Solve: 41 [?]
Skill Endorsements: 1 [?]
 
0
 

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.

Member Avatar
Ancient Dragon
Achieved Level 70
27,645 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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
Question Answered as of 9 Months Ago by scudzilla, Reverend Jim and pritaeas
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: