I normally use SQL as the backend database however for a new project I need to use MSAccess.
I am trying to insert a new record, this works fine if I only have on fielid on the row but as soon as I have a second field I get an error.
"Syntax error in INSERT INTO statement."

can anyone point me in the right direction?

      Dim sql As String = "INSERT INTO TblReelResults (ID , Section) VALUES(@ID ,@Section)"
        Using conn = New System.Data.OleDb.OleDbConnection(_connectionstring)
            Using sqlCom = New System.Data.OleDb.OleDbCommand(sql, conn)
                conn.Open()
                sqlCom.Parameters.Add("@ID", OleDbType.VarWChar).Value = Me.lb_id.Text
                sqlCom.Parameters.Add("@Section", OleDbType.VarWChar).Value = Me.txtSection.Text

                Dim icount As Integer = sqlCom.ExecuteNonQuery
            End Using

        End Using

10-06-2020_16-17-23.png

Recommended Answers

What are the values you are trying to insert?

What do you see when you click "View detail" in the exception box?

Jump to Post

All 3 Replies

What are the values you are trying to insert?

What do you see when you click "View detail" in the exception box?

Function OleDbCommand.ExecuteNonQuery() always returns an integer value of affected row
So, to get the number of affected Row you must have to use it as function type not statement type.
i.e.

Dim icount As Integer = sqlCom.ExecuteNonQuery()

This is my last project (part of the code with query importing into MS Access). Maybe this could help you.

Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\data.accdb")
        Try
            con.Open()
            Dim sql As String
            Dim cmd As New OleDb.OleDbCommand
            For Each row2 As DataGridViewRow In DataGridView2.Rows
                sql = "INSERT INTO table (BR,DAT,SIF,KAT,NA,GRUP,N_GRUP,TOTAL) VALUES ('" SOME VALUES "', '" SOME VALUES + "', '" + SOME VALUES + "', '" + SOME VALUES + "', '" + SOME VALUES + "', '" + SOME VALUES + "', '" + SOME VALUES + "', '" + SOME VALUES + "')"

                cmd.Connection = con
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.