none
OleDbCommand - INSERT INTO RRS feed

  • Question

  • So I'm having a little trouble with adding a record to a database through a data grid view.

    I have it working on one of my forms but am getting a syntax error on a different form even though the code is practically the same.

    Working Code:

    Private Sub button_Add_Click(sender As Object, e As EventArgs) Handles button_Add.Click
    
            If textBox_Surname.Text = "" Or textBox_Forename.Text = "" Or textBox_Gender.Text = "" Or textBox_Form.Text = "" Or textBox_PhoneNumber.Text = "" Or textBox_Address.Text = "" Or textBox_DateOfBirth.Text = "" Then
    
                MessageBox.Show("Please enter all fields.", "Information")
    
            Else
    
    
                Try
                    ConnectedDBManageForm = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data Source = C:\Users\Brad\Desktop\New Files\Student_Database.accdb;Persist Security Info=False")
                    'Asigns the location of the database to the variable 'ConnectedDBManageForm'
                    Try
                        Call ConnectedDBManageForm.Open()
                        'Attemmpts to connect to the database
                    Catch ex As Exception
                        MessageBox.Show("Cannot connect to database.", "Information")
                        'A message box to tell the user that the program cannot connect to the database
                    End Try
    
                    If ConnectedDBManageForm.State = ConnectionState.Open Then
                        'Checks if the program has connected to the database
                        Dim SQL As New OleDb.OleDbCommand("SELECT * FROM Tbl_Pupils", ConnectedDBManageForm)
                        'Asigns the data from the database to the vaiable 'SQL'
                        Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                        'Asigns the contents of SQL to the data adapter variable
                        Dim DataTable As New DataTable("ManageForm")
                        'Creates a new data table called 'ManageForm'
                        DataAdapter.Fill(DataTable)
                        'Fills the data table with the data in the data adapter
                        dataGridView_ManageForm.DataSource = DataTable
                        'Gives the data grid view a data source
                    End If
    
                Catch ex As Exception
                    MessageBox.Show("SQL error is " & ex.ToString)
                    'A message box to tell the user the SQL error
                End Try
    
                'ADDING A STUDENT
    
                Dim AutoNumber As Integer
    
                'CONNECTING TO DATABASE
    
                Try
                    ConnectedDBManageForm = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Brad\Desktop\New Files\Student_Database.accdb;Persist Security Info=False;")
                    'Asigns the location of the database to the variable 'ConnectedDBManageForm'
                    Try
                        Call ConnectedDBManageForm.Open()
                        'Attemmpts to connect to the database
                    Catch ex As Exception
                        MessageBox.Show("Cannot connect to database." & ex.ToString, "Information")
                        'A message box to tell the user that the program cannot connect to the database
                    End Try
    
                    If ConnectedDBManageForm.State = ConnectionState.Open Then
                        'Checks if the program has connected to the database
                        Dim SQL As New OleDb.OleDbCommand("SELECT MAX(PupilID) FROM Tbl_Pupils", ConnectedDBManageForm)
                        'Asigns the highest auto number to the variable 'SQL'
                        Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                        'Asigns the contents of SQL to the data adapter variable
                        Dim DataTable As New DataTable("ManageForm")
                        'Creates a new data table called 'ManageForm'
                        DataAdapter.Fill(DataTable)
                        'Fills the data table with the data in the data adapter
                        dataGridView_ManageForm.DataSource = DataTable
                        'Gives the data grid view a data source
                    End If
    
                Catch ex As Exception
                    MessageBox.Show("SQL error is " & ex.ToString)
                    'A message box to tell the user the SQL error
                End Try
    
                'AUTO NUMBER
    
                Me.dataGridView_ManageForm.CurrentCell = Me.dataGridView_ManageForm(0, 0)
                'Selects the first cell
                AutoNumber = Me.dataGridView_ManageForm.CurrentCell.FormattedValue + 1
                'Asigns the selected value to the variable 'AutoNumber'
    
                'AutoNumber = AutoNumber + 1
                'Adds one onto the value stored in the variable 'AutoNumber'
    
                'EMAIL ADDRESS
                Dim Email As String
                'Variable which stores the Email
                Dim FirstCharacter As String
                'Variable for the first character of the forename
                FirstCharacter = textBox_Forename.Text.Substring(0, 1)
                'Asigns the first character to the variable
                Email = FirstCharacter & "." & textBox_Surname.Text & "@TreeRoad.org"
                'Creates the email address
    
    
                Email = FirstCharacter & "." & textBox_Surname.Text & "@treeroadschool.org"
    
    
                'ADDING A STUDENT
    
                Try
                    ConnectedDBManageForm = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Brad\Desktop\New Files\Student_Database.accdb;Persist Security Info=False;")
                    'Asigns the location of the database to the variable 'ConnectedDBManageForm'
                    Try
                        Call ConnectedDBManageForm.Open()
                        'Attemmpts to connect to the database
                    Catch ex As Exception
                        MessageBox.Show("Cannot connect to database.", "Information")
                        'A message box to tell the user that the program cannot connect to the database
                    End Try
    
                    If ConnectedDBManageForm.State = ConnectionState.Open Then
                        'Checks if the program has connected to the database
                        Dim SQL As New OleDb.OleDbCommand("INSERT INTO Tbl_Pupils (PupilID, Surname, Forename, DateOfBirth, HomeAddress, HomePhoneNumber, Gender, TutorGroup, EmailAddress) VALUES (" & AutoNumber & ", '" & textBox_Surname.Text & "', '" & textBox_Forename.Text & "', #" & textBox_DateOfBirth.Text & "#, '" & textBox_Address.Text & "', " & textBox_PhoneNumber.Text & ", '" & textBox_Gender.Text & "', '" & textBox_Form.Text & "', '" & Email & "')", ConnectedDBManageForm)
                        'Asigns the highest auto number to the variable 'SQL'
                        Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                        'Asigns the contents of SQL to the data adapter variable
                        Dim DataTable As New DataTable("ManageForm")
                        'Creates a new data table called 'ManageForm'
                        DataAdapter.Fill(DataTable)
                        'Fills the data table with the data in the data adapter
                        dataGridView_ManageForm.DataSource = DataTable
                        'Gives the data grid view a data source
                    End If
    
                Catch ex As Exception
                    MessageBox.Show("SQL error is " & ex.ToString)
                    'A message box to tell the user the SQL error
                End Try
    
                Try
                    ConnectedDBManageForm = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data Source = C:\Users\Brad\Desktop\New Files\Student_Database.accdb;Persist Security Info=False")
                    'Asigns the location of the database to the variable 'ConnectedDBManageForm'
                    Try
                        Call ConnectedDBManageForm.Open()
                        'Attemmpts to connect to the database
                    Catch ex As Exception
                        MessageBox.Show("Cannot connect to database.", "Information")
                        'A message box to tell the user that the program cannot connect to the database
                    End Try
    
                    If ConnectedDBManageForm.State = ConnectionState.Open Then
                        'Checks if the program has connected to the database
                        Dim SQL As New OleDb.OleDbCommand("SELECT * FROM Tbl_Pupils", ConnectedDBManageForm)
                        'Asigns the data from the database to the vaiable 'SQL'
                        Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                        'Asigns the contents of SQL to the data adapter variable
                        Dim DataTable As New DataTable("ManageForm")
                        'Creates a new data table called 'ManageForm'
                        DataAdapter.Fill(DataTable)
                        'Fills the data table with the data in the data adapter
                        dataGridView_ManageForm.DataSource = DataTable
                        'Gives the data grid view a data source
                    End If
    
                Catch ex As Exception
                    MessageBox.Show("SQL error is " & ex.ToString)
                    'A message box to tell the user the SQL error
                End Try
    
                textBox_Surname.Text = ""
                'Removes text from the text box
                textBox_Forename.Text = ""
                'Removes text from the text box
                textBox_Form.Text = ""
                'Removes text from the text box
                textBox_Gender.Text = ""
                'Removes text from the text box
                textBox_Address.Text = ""
                'Removes text from the text box
                textBox_PhoneNumber.Text = ""
                'Removes text from the text box
                textBox_DateOfBirth.Text = ""
                'Removes text from the text box
            End If
    
    
        End Sub

    Code with Error:

    Private Sub button_Add_Click(sender As Object, e As EventArgs) Handles button_Add.Click
    
            If textBox_Surname.Text = "" Or textBox_Forename.Text = "" Or textBox_Gender.Text = "" Or textBox_Form.Text = "" Or textBox_PhoneNumber.Text = "" Or textBox_Duration.Text = "" Or textBox_Date.Text = "" Then
    
                MessageBox.Show("Please enter all fields.", "Information")
    
            Else
    
    
                Try
                    ConnectedDBDetentions = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data Source = C:\Users\Brad\Desktop\New Files\Detentions.accdb;Persist Security Info=False")
                    'Asigns the location of the database to the variable 'ConnectedDBManageForm'
                    Try
                        Call ConnectedDBDetentions.Open()
                        'Attemmpts to connect to the database
                    Catch ex As Exception
                        MessageBox.Show("Cannot connect to database.", "Information")
                        'A message box to tell the user that the program cannot connect to the database
                    End Try
    
                    If ConnectedDBDetentions.State = ConnectionState.Open Then
                        'Checks if the program has connected to the database
                        Dim SQL As New OleDb.OleDbCommand("SELECT * FROM table_Detentions", ConnectedDBDetentions)
                        'Asigns the data from the database to the vaiable 'SQL'
                        Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                        'Asigns the contents of SQL to the data adapter variable
                        Dim DataTable As New DataTable("Detentions")
                        'Creates a new data table called 'ManageForm'
                        DataAdapter.Fill(DataTable)
                        'Fills the data table with the data in the data adapter
                        dataGridView_Detentions.DataSource = DataTable
                        'Gives the data grid view a data source
                    End If
    
                Catch ex As Exception
                    MessageBox.Show("SQL error is " & ex.ToString)
                    'A message box to tell the user the SQL error
                End Try
    
                'ADDING A STUDENT
    
                Dim AutoNumber As Integer
    
                'CONNECTING TO DATABASE
    
                Try
                    ConnectedDBDetentions = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source =C:\Users\Brad\Desktop\New Files\Detentions.accdb;Persist Security Info=False;")
                    'Asigns the location of the database to the variable 'ConnectedDBManageForm'
                    Try
                        Call ConnectedDBDetentions.Open()
                        'Attemmpts to connect to the database
                    Catch ex As Exception
                        MessageBox.Show("Cannot connect to database.", "Information")
                        'A message box to tell the user that the program cannot connect to the database
                    End Try
    
                    If ConnectedDBDetentions.State = ConnectionState.Open Then
                        'Checks if the program has connected to the database
                        Dim SQL As New OleDb.OleDbCommand("SELECT MAX(ID) FROM table_Detentions", ConnectedDBDetentions)
                        'Asigns the highest auto number to the variable 'SQL'
                        Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                        'Asigns the contents of SQL to the data adapter variable
                        Dim DataTable As New DataTable("Detentions")
                        'Creates a new data table called 'ManageForm'
                        DataAdapter.Fill(DataTable)
                        'Fills the data table with the data in the data adapter
                        dataGridView_Detentions.DataSource = DataTable
                        'Gives the data grid view a data source
                    End If
    
                Catch ex As Exception
                    MessageBox.Show("SQL error is " & ex.ToString)
                    'A message box to tell the user the SQL error
                End Try
    
                'AUTO NUMBER
    
                Me.dataGridView_Detentions.CurrentCell = Me.dataGridView_Detentions(0, 0)
                'Selects the first cell
                AutoNumber = Me.dataGridView_Detentions.CurrentCell.FormattedValue + 1
                'Asigns the selected value to the variable 'AutoNumber'
    
                'AutoNumber = AutoNumber + 1
                'Adds one onto the value stored in the variable 'AutoNumber'
    
    
    
    
                'ADDING A STUDENT
    
                Try
                    ConnectedDBDetentions = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Brad\Desktop\New Files\Detentions.accdb;Persist Security Info=False;")
                    'Asigns the location of the database to the variable 'ConnectedDBManageForm'
                    Try
                        Call ConnectedDBDetentions.Open()
                        'Attemmpts to connect to the database
                    Catch ex As Exception
                        MessageBox.Show("Cannot connect to database.", "Information")
                        'A message box to tell the user that the program cannot connect to the database
                    End Try
    
                    If ConnectedDBDetentions.State = ConnectionState.Open Then
                        'Checks if the program has connected to the database
                        Dim SQL As New OleDb.OleDbCommand("INSERT INTO table_Detentions (ID, Surname, Forename, Gender, Form, Phone Number, Date Of Detention, Duration Of Detention) VALUES (" & AutoNumber & ", '" & textBox_Surname.Text & "', '" & textBox_Forename.Text & "', '" & textBox_Gender.Text & "', '" & textBox_Form.Text & "', " & textBox_PhoneNumber.Text & ", #" & textBox_Date.Text & "#, '" & textBox_Duration.Text & "')", ConnectedDBDetentions)
                        'Asigns the highest auto number to the variable 'SQL'
                        Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                        'Asigns the contents of SQL to the data adapter variable
                        Dim DataTable As New DataTable("Detentions")
                        'Creates a new data table called 'ManageForm'
                        DataAdapter.Fill(DataTable)
                        'Fills the data table with the data in the data adapter
                        dataGridView_Detentions.DataSource = DataTable
                        'Gives the data grid view a data source
                    End If
    
                Catch ex As Exception
                    MessageBox.Show("SQL error is " & ex.ToString)
                    'A message box to tell the user the SQL error
                End Try
    
                Try
                    ConnectedDBDetentions = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data Source = C:\Users\Brad\Desktop\New Files\Detentions.accdb;Persist Security Info=False")
                    'Asigns the location of the database to the variable 'ConnectedDBManageForm'
                    Try
                        Call ConnectedDBDetentions.Open()
                        'Attemmpts to connect to the database
                    Catch ex As Exception
                        MessageBox.Show("Cannot connect to database.", "Information")
                        'A message box to tell the user that the program cannot connect to the database
                    End Try
    
                    If ConnectedDBDetentions.State = ConnectionState.Open Then
                        'Checks if the program has connected to the database
                        Dim SQL As New OleDb.OleDbCommand("SELECT * FROM table_Detentions", ConnectedDBDetentions)
                        'Asigns the data from the database to the vaiable 'SQL'
                        Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                        'Asigns the contents of SQL to the data adapter variable
                        Dim DataTable As New DataTable("Detentions")
                        'Creates a new data table called 'ManageForm'
                        DataAdapter.Fill(DataTable)
                        'Fills the data table with the data in the data adapter
                        dataGridView_Detentions.DataSource = DataTable
                        'Gives the data grid view a data source
                    End If
    
                Catch ex As Exception
                    MessageBox.Show("SQL error is " & ex.ToString)
                    'A message box to tell the user the SQL error
                End Try
    
                textBox_Surname.Text = ""
                'Removes text from the text box
                textBox_Forename.Text = ""
                'Removes text from the text box
                textBox_Form.Text = ""
                'Removes text from the text box
                textBox_Gender.Text = ""
                'Removes text from the text box
                textBox_Date.Text = ""
                'Removes text from the text box
                textBox_PhoneNumber.Text = ""
                'Removes text from the text box
                textBox_Duration.Text = ""
                'Removes text from the text box
            End If
    
    
        End Sub

    The error I'm getting is:

    SQL error is System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement.

    The data types in the two Access databases are the exact same.

    Could anyone help m out here? I feel like it is just a stupid mistake that I can't find.

    Cheers,

    Brad

    Working Database: http://www.mediafire.com/file/71wlt31sbglg15q/Student_Database.accdb

    Database with Error: https://www.mediafire.com/file/mg75cccxrd3bc4n/Detentions.accdb

    • Edited by MrSwalbert Monday, August 7, 2017 5:20 PM Links
    Monday, August 7, 2017 5:09 PM

Answers

  • If the column name contains spaces, then use “[ ]”. For example: [Phone Number].

    And consider parameterized queries, by the way.


    • Edited by Viorel_MVP Monday, August 7, 2017 5:23 PM
    • Marked as answer by MrSwalbert Monday, August 7, 2017 5:48 PM
    Monday, August 7, 2017 5:21 PM

All replies

  • If the column name contains spaces, then use “[ ]”. For example: [Phone Number].

    And consider parameterized queries, by the way.


    • Edited by Viorel_MVP Monday, August 7, 2017 5:23 PM
    • Marked as answer by MrSwalbert Monday, August 7, 2017 5:48 PM
    Monday, August 7, 2017 5:21 PM
  • What do you mean by "parameterized queries"?

    And thank you that worked! ;)

    • Edited by MrSwalbert Monday, August 7, 2017 5:48 PM
    Monday, August 7, 2017 5:45 PM
  • What do you mean by "parameterized queries"?

    And thank you that worked! ;)

    To parameterize and escape the command statement (which is what Viorel_ suggested), your Insert Into statement would turn into:

    Dim SQL As New OleDb.OleDbCommand("INSERT INTO table_Detentions ([ID], [Surname], [Forename], [Gender], [Form], [Phone Number], [Date Of Detention], [Duration Of Detention]) VALUES (@autoNumber, @surname, @forename, @gender, @form, @phone, @detentionDate, @detentionDuration", ConnectedDBDetentions)
    
    With SQL.Parameters
        .AddWithValue("@autoNumber", AutoNumber)
        .AddWithValue("@surname", textBox_Surname.Text)
        .AddWithValue("@forename", textBox_Forename.Text)
        .AddWithValue("@gender", textBox_Gender.Text)
        .AddWithValue("@form", textBox_Form.Text)
        .AddWithValue("@phone", textbox_PhoneNumber.Text)
        .AddWithValue("@detentionDate", textBox_Date.Text)
        .AddWithValue("@detentionDuration", textBox_Duration.Text)
    End With

    Monday, August 7, 2017 5:55 PM
  • Your insert statements will not work if the entered name contains an apostrophe, for example: O'Neill. Try it and see the results.

    It is also possible for attackers to harm your database:

    ·         https://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx

    ·         https://blogs.msdn.microsoft.com/apinedo/2007/06/19/sql-injection/

    The parameterized queries will help.



    • Edited by Viorel_MVP Monday, August 7, 2017 6:05 PM
    Monday, August 7, 2017 6:03 PM
  • It is also possible for attackers to harm your database

    Thanks for your concern but the program I am writing is only a computing assessment so doesn't really matter.
    Monday, August 7, 2017 6:43 PM