none
Syntax error in INSERT INTO statement. RRS feed

  • Question

  • I am getting an INSERT INTO error in my code when trying to write to a Microsoft Access mdb database.  The code worked before I substantially increased the number of variables.  I've checked and rechecked the code below but cannot find an error. The error comes on the "cmd.ExecuteNonQuery()" line.

    If someone could help I would greatly appreciate it.

    Thanks,

    Private Sub btnCommit_Click(sender As Object, e As EventArgs) Handles btnCommit.Click
            Dim sqlCode As String
            Dim connection As New OleDb.OleDbConnection

            sqlCode = "INSERT INTO tblContacts (ID2, Title1, YearReleased, Rating, Minutes, " _
                & "Color, Type_Loc_1, Type_Loc_2, IMDbRating, Pre1900, Action, Adventure, " _
                & "Animation, Biographical, Comedy, Crime, Documentary, Drama, Family, Fantasy, " _
                & "FilmNoir, Forin, Historical, Holiday, Horror, Music, Musical, Mystery, " _
                & "Romance, SciFi, Silent, Sports, Thriller, War, Western)" _
                & "Values ('" & txtID2.Text & "', '" &
                txtTitle1.Text & "', '" &
                txtYearReleased.Text & "', '" &
                txtRating.Text & "', '" &
                txtMinutes.Text & "', '" &
                txtColor.Text & "', '" &
                txtType_Loc_1.Text & "', '" &
                txtType_Loc_2.Text & "', '" &
                txtIMDbRating.Text & "', '" &
                strPre1900 & "', '" &
                strAction & "', '" &
                strAdventure & "', '" &
                strAnimation & "', '" &
                strBiographical & "', '" &
                strComedy & "', '" &
                strCrime & "', '" &
                strDocumentary & "', '" &
                strDrama & "', '" &
                strFamily & "', '" &
                strFantasy & "', '" &
                strFilmNoir & "', '" &
                strForin & "', '" &
                strHistorical & "', '" &
                strHoliday & "', '" &
                strHorror & "', '" &
                strMusic & "', '" &
                strMusical & "', '" &
                strMystery & "', '" &
                strRomance & "', '" &
                strSciFi & "', '" &
                strSilent & "', '" &
                strSports & "', '" &
                strThriller & "', '" &
                strWar & "', '" &
                strWestern &
                "')"

            connection.ConnectionString = dbProvider & dbSource

            connection.Open()

            cmd = New OleDb.OleDbCommand(sqlCode, connection)
            cmd.ExecuteNonQuery()

            cmd.Dispose()
            MessageBox.Show("Record Added.")
            connection.Close()
     End Sub

    Friday, March 31, 2017 4:22 PM

All replies

  • Hello,

    For the record, it makes sense the error comes from cmd.ExecuteNonQuery() as all the presiding code is preparation to execute the query.

    With that said, it's unwise to use string concatenation with values in the SQL statement. You need to construct your statement with parameters e.g. in the following class which is called from a form I'm using a accdb instead of a mdb but by changing the connection string this will work for mdb file type database also. Note how I constructed the INSERT statement once then it can be used many times. There are two simple examples for AddNewRow with a few parameters, same works for many parameters. The first benefit is say a string is passed in with an apostrophe, this will blow up you query if written as you are currently but with parameters the apostrophe will be escaped for you. The second is your query is more secure against tampering which is know as sql injection.

    Note that the full source (see link below) is pristine while the code below has alterations to assist with forum questions. 

    ''' <summary>
    ''' Responsible for all database loading and inserting
    ''' </summary>
    ''' <remarks>
    ''' LoadSingleRow and AddNewRow(ByVal CompanyName As String, ByVal ContactName As String...)
    ''' were added in for assisting with a online question where the person was using VS2008
    ''' so there are differences with object initialization to fit into VS2008 syntax
    ''' </remarks>
    Public Class Operations
    
        ''' <summary>
        ''' Creates our connection string to the database which is easy to follow
        ''' and there is no string concatenation done here
        ''' </summary>
        ''' <remarks></remarks>
        Private Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
            }
    
        ''' <summary>
        ''' Used to get all customers at program startup
        ''' </summary>
        ''' <remarks></remarks>
        Private SelectStatement As String =
        <SQL>
            SELECT 
                Identifier, 
                CompanyName, 
                ContactName, 
                ContactTitle
            FROM Customer;
        </SQL>.Value
    
        ''' <summary>
        ''' Responsible for inserting rows into the customer table
        ''' </summary>
        ''' <remarks></remarks>
        Private InsertStatement As String =
        <SQL>
        INSERT INTO Customer 
            (
                CompanyName,
                ContactName,
                ContactTitle
            ) 
        Values
            (
                @CompanyName,
                @ContactName,
                @ContactTitle
            )
        </SQL>.Value
    
        ''' <summary>
        ''' Get a list of titles, in this case there are only two.
        ''' For a real app we would have a contact title table rather
        ''' then get them from existing row. 
        ''' </summary>
        ''' <returns></returns>
        Public Function ContactTitles() As List(Of String)
            Dim titleList As New List(Of String)
            Using cn As New OleDb.OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = "SELECT DISTINCT ContactTitle FROM Customer"}
                    cn.Open()
                    Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
                    If reader.HasRows Then
                        While reader.Read
                            titleList.Add(reader.GetString(0))
                        End While
                        reader.Close()
                    End If
                End Using
            End Using
    
            Return titleList
    
        End Function
    
        ''' <summary>
        ''' Used to open the database in Windows Explorer
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub ViewDatabase()
            Process.Start(Builder.DataSource)
        End Sub
    
        ''' <summary>
        ''' Load existing customers into a BindingSource which becomes
        ''' the DataSource for a DataGridView
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function LoadCustomers() As DataTable
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                ContactTitle
                            FROM Customer;
                        </SQL>.Value
    
                    Dim dt As New DataTable With {.TableName = "Customer"}
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
    
                    Return dt
    
                End Using
            End Using
        End Function
        Public Function LoadSingleRow(ByVal Identifier As Integer) As String
            Dim sb As New Text.StringBuilder
            Using cn As New OleDb.OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDb.OleDbCommand("", cn)
                    cmd.CommandText = "SELECT CompanyName, ContactName, ContactTitle FROM Customer WHERE Identifier =" & Identifier.ToString
                    cn.Open()
                    Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader
                    If Reader.HasRows Then
                        Reader.Read()
    
                        sb.AppendLine("Company name [" & Reader.GetString(0) & "]")
                        sb.AppendLine("Contact [" & Reader.GetString(1) & "]")
                        sb.AppendLine("Contact name [" & Reader.GetString(2) & "]")
                    Else
                        sb.AppendLine("Operation failed")
                    End If
                End Using
            End Using
    
            Return sb.ToString
        End Function
        ''' <summary>
        ''' Used to add one row to the Customer table and on success
        ''' return the new primary key.
        ''' 
        ''' Here we pass in a customer but could have very well passed in
        ''' an object array or one parameter for each field.
        ''' 
        ''' All fields are of type string but you can add other types
        ''' too.
        ''' 
        ''' AddWithValue is used here but we could also use Add and
        ''' control the parameters.
        ''' 
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="Identfier"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function AddNewRow(ByVal sender As Customer, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
    
            Try
                Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
    
                        cmd.CommandText = InsertStatement
    
                        cmd.Parameters.AddWithValue("@CompanyName", sender.CompanyName)
                        cmd.Parameters.AddWithValue("@ContactName", sender.ContactName)
                        cmd.Parameters.AddWithValue("@ContactTitle", sender.ContactTitle)
    
                        cn.Open()
    
                        cmd.ExecuteNonQuery()
    
                        cmd.CommandText = "Select @@Identity"
                        Identfier = CInt(cmd.ExecuteScalar)
    
                    End Using
                End Using
    
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function AddNewRow(ByVal CompanyName As String, ByVal ContactName As String, ByVal ContactTitle As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
    
            Try
                Using cn As New OleDb.OleDbConnection(Builder.ConnectionString)
                    Using cmd As New OleDb.OleDbCommand("", cn)
    
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,ContactTitle) Values (@CompanyName,@ContactName,@ContactTitle)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", CompanyName.Trim)
                        cmd.Parameters.AddWithValue("@ContactName", ContactName.Trim)
                        cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle.Trim)
    
                        cn.Open()
    
                        cmd.ExecuteNonQuery()
    
                        cmd.CommandText = "Select @@Identity"
                        Identfier = CInt(cmd.ExecuteScalar)
    
                    End Using
                End Using
    
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Sub New()
        End Sub
        Public Overrides Function ToString() As String
            Return "Demo adding rows"
        End Function
    End Class
    Full source in my MSDN code sample.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, March 31, 2017 4:40 PM
    Moderator
  • Karen,

    I am trying to adapt your code to my program but I get an underline error on Builder in the following line.

     Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString} 

    Could you please help.

    Thanks,

    Friday, March 31, 2017 10:50 PM
  • Builder is a variable in the code I posted.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, March 31, 2017 10:55 PM
    Moderator
  • Fred,

    You get written "Insert Into" when you do an executenonquery. 

    Never heard about such an error mostly it is a text which tells what is the error.. 


    Success
    Cor

    Saturday, April 1, 2017 3:03 PM