The instruction INSERT INTO contains a syntaxerror.

[ud2008]

I run into a strange error.

When I close my application (created with vb2010), I copy all records from a mysql table to a local ms access database table.

The ms access table contains 2 tables (songs, email).

I want to copy the records from the online email table into the local database, but I got this strange error:

The instruction INSERT INTO contains a syntaxerror.

The strange thing is, that when I try to copy the songs table, it works perfect, so there is something wrong, but what.

Here is the code I use:

Private Sub SplashScreen2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If My.Computer.Network.Ping("195.128.174.38") Then
            Dim dt As New DataSet()
            Try
                'Get Data into DataTable from MySQL Server database    
                Using cnn As New MySqlConnection("Server=host;Database=db;Uid=user;Pwd=pw")
                    Dim cmdSelect As MySqlCommand = New MySqlCommand("Select * From email")
                    cmdSelect.Connection = cnn
                    Dim ad As New MySqlDataAdapter(cmdSelect)
                    ad.AcceptChangesDuringFill = False
                    ad.Fill(dt)
                End Using
                Try
                    ProgressBarX1.Maximum = dt.Tables(0).Rows.Count

                    DeleteRecords()
                    Try
                        'Insert Data from DataTable into Access database  
                        Using cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= |DataDirectory|\songlisteditor.accdb")
                            Dim cmdSelect As OleDbCommand = New OleDbCommand("Select * From email")
                            cmdSelect.Connection = cnn
                            Dim ad As New OleDbDataAdapter(cmdSelect)
                            Dim cmdBuilder As New OleDbCommandBuilder(ad)
                            Dim cmd As OleDbCommand = cmdBuilder.GetInsertCommand()
                            cmd.Connection = cnn
                            For Each row As DataRow In dt.Tables(0).Rows
                                ad.InsertCommand = cmd
                                ProgressBarX1.Value += 1
                            Next
                            ad.Update(dt)
                        End Using
                    Catch ex3 As Exception
                        MessageBox.Show(ex3.Message)
                    End Try
                Catch ex2 As Exception
                    MessageBox.Show(ex2.Message)
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        Else
            MessageBox.Show("No connection with database available!", "Addressbook", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
End sub

Private Sub DeleteRecords()
        Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= |DataDirectory|\songlisteditor.accdb")
        Dim cmd As New OleDbCommand

        cmd.Connection = cnn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "DELETE FROM email"
        Using cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub

Hope you guys can pinpoint the problem.

Thanks.

 

[jmcilhinney]

It's telling you that there's a syntax error in your INSERT statement. Did you actually look to see what your INSERT statement contains? You may or may not have been able to spot and fix the error but that should be the obvious first step.

When you get syntax errors with a command builder, it's usually because you have used a wildcard (*) in your query and one or more of your column names is a reserved word or contains special characters, e.g. spaces. The best option is to not use reserved words or special characters in identifiers. Failing that, you can set the QuotePrefix and QuoteSuffix properties of the command builder. Usually that would be "[" and "]" respectively or "`" for both.

Also, these lines are of no use at all:

                            Dim cmd As OleDbCommand = cmdBuilder.GetInsertCommand()
                            cmd.Connection = cnn
                            For Each row As DataRow In dt.Tables(0).Rows
                                ad.InsertCommand = cmd
                                ProgressBarX1.Value += 1
                            Next

The command builder generates the appropriate commands and the data adapter uses those commands automatically. All you need is the Update call, which is what saves the data regardless.

 

[ud2008]

When you get syntax errors with a command builder, it's usually because you have used a wildcard (*) in your query and one or more of your column names is a reserved word or contains special characters, e.g. spaces. The best option is to not use reserved words or special characters in identifiers. Failing that, you can set the QuotePrefix and QuoteSuffix properties of the command builder. Usually that would be "[" and "]" respectively or "`" for both.

You were right, I used a column name: Group, after I changed it the error was gone.

Thanks for you help.