Unable to insert values into MS Access database

Question

Can anyone please look at below code and advise what is wrong in this?

I was trying to insert values into an MS Access database. Compiler throws no error but the values are not inserted in table.

Code:

Private Sub btnSignInOK_Click(sender As Object, e As EventArgs) Handles btnSignInOK.Click

    uniqid = "1"

    Try
        Dim ConnSignIn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Resources\DBpPNRGENERATORDATA.accdb;Persist Security Info=True")
        Dim CmdSignIn As New OleDb.OleDbCommand

        If Not ConnSignIn.State = ConnectionState.Open Then
            ConnSignIn.Open()
        End If

        CmdSignIn.Connection = ConnSignIn
        CmdSignIn.CommandText = "DELETE TEMPSIGNIN.* FROM TEMPSIGNIN WHERE IDENTIFIER='" + uniqid + "'"
        CmdSignIn.ExecuteNonQuery()


        CmdSignIn.CommandText = "INSERT INTO TEMPSIGNIN(IDENTIFIER,EPR,Partition,Host)VALUES('" & uniqid & "','" & tbSigninEPR.Text & "','" & cbSignInPartition.Text & "','" & tbSignInAl.Text & "')"
        CmdSignIn.ExecuteNonQuery()

    Catch ex As Exception

        MessageBox.Show(ex.Message)

    End Try

Answer 1

Okay, focusing on why there is no exception thrown no matter the syntax used. Are you sure you are targeting the right physical database?

If targeting the correct database, if the database is in Solution Explorer then select the database, select properties, check the property "Copy to Output Directory" the default is Copy Always, if you have this set then on each build the database in the project folder overwrite the database in the app folder directory thus no changes would be there.

Side note: Going with the recommendations provided so far, here is a pattern to consider. Place all database operations in it's own class. The class below is simple, read, update, add and remove from a MS-Access database table Customers with (for this sample) a primary key, company name and contact name.

Each operation in it's own function with error handling which on failure you can get back the exception (I simply use the exception message for this sample).

A BindingSource is used as this component makes life easier when traversing data bound controls such as TextBoxes and or a DataGridView.

The form code displays data in a DataGridView, there is a button to remove the current record and a button to add a new record (I left out assertion to see if the TextBoxes have data). I used two TextBox controls to get information for inserting records.

Form code

Public Class StackOverFlowForm1
    Private Operations As New Sample2
    Private bsCustomers As New BindingSource
    Private Sub StackOverFlowForm1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim ops As New Sample2
        If ops.LoadCustomers Then
            bsCustomers.DataSource = ops.CustomersDataTable
            DataGridView1.DataSource = bsCustomers
        Else
            MessageBox.Show($"Failed to load table data{Environment.NewLine}{ops.Exception.Message}")
        End If
    End Sub
    Private Sub cmdDeleteCurrent_Click(sender As Object, e As EventArgs) Handles cmdDeleteCurrent.Click
        If bsCustomers.Current IsNot Nothing Then
            Dim ops As New Sample2
            Dim currentIdentifier As Integer = CType(bsCustomers.Current, DataRowView).Row.Field(Of Integer)("Identifier")
            If Not ops.DeleteCustomer(currentIdentifier) Then
                MessageBox.Show($"Failed to remove customer: {ops.Exception.Message}")
            Else
                bsCustomers.RemoveCurrent()
            End If
        End If
    End Sub
    Private Sub cmdInsert_Click(sender As Object, e As EventArgs) Handles cmdInsert.Click
        Dim ops As New Sample2
        Dim newId As Integer = 0
        If ops.AddNewRow(txtCompanyName.Text, txtContact.Text, newId) Then
            CType(bsCustomers.DataSource, DataTable).Rows.Add(New Object() {newId, txtCompanyName.Text, txtContact.Text})
        Else
            MessageBox.Show($"Failed to add customer: {ops.Exception.Message}")
        End If
    End Sub
End Class

Then we have a class for database operations. The database is located in the app folder.

Public Class Sample2
    Private Builder As New OleDbConnectionStringBuilder With
    {
        .Provider = "Microsoft.ACE.OLEDB.12.0",
        .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
    }

    Private mExceptiom As Exception
    ''' <summary>
    ''' Each method when executed, if there is an exception thrown
    ''' then mException is set and can be read back via Exception property
    ''' only when a method returns false.
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly Property Exception As Exception
        Get
            Return mExceptiom
        End Get
    End Property
    ''' <summary>
    ''' Container for data read in from a database table
    ''' </summary>
    ''' <returns></returns>
    Public Property CustomersDataTable As DataTable
    Public Function LoadCustomers() As Boolean
        If Not IO.File.Exists(Builder.DataSource) Then
            Return False
        End If

        Try

            CustomersDataTable = New DataTable

            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT Identifier, CompanyName, ContactTitle FROM Customers"

                    cn.Open()

                    CustomersDataTable.Load(cmd.ExecuteReader)
                    CustomersDataTable.DefaultView.Sort = "CompanyName"
                    CustomersDataTable.Columns("Identifier").ColumnMapping = MappingType.Hidden

                End Using
            End Using

            Return True
        Catch ex As Exception
            mExceptiom = ex
            Return False
        End Try
    End Function
    ''' <summary>
    ''' Delete a customer by their primary key
    ''' </summary>
    ''' <param name="CustomerId"></param>
    ''' <returns></returns>
    Public Function DeleteCustomer(ByVal CustomerId As Integer) As Boolean
        Dim Success As Boolean = True
        Dim Affected As Integer = 0

        Try
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"

                    cmd.Parameters.AddWithValue("@Identifier", CustomerId)

                    cn.Open()

                    Affected = cmd.ExecuteNonQuery()
                    If Affected = 1 Then
                        Success = True
                    End If
                End Using
            End Using
        Catch ex As Exception
            Success = False
            mExceptiom = ex
        End Try

        Return Success

    End Function
    Public Function UpdateCustomer(ByVal CustomerId As Integer, ByVal CompanyName As String, ByVal ContactName As String) As Boolean
        Dim Success As Boolean = True
        Dim Affected As Integer = 0

        Try
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName, ContactName = @ContactName WHERE Identifier = @Identifier"

                    cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                    cmd.Parameters.AddWithValue("@ContactName", ContactName)
                    cmd.Parameters.AddWithValue("@Identifier", ContactName)

                    cn.Open()

                    Affected = cmd.ExecuteNonQuery()
                    If Affected = 1 Then
                        Success = True
                    End If
                End Using
            End Using
        Catch ex As Exception
            Success = False
            mExceptiom = ex
        End Try

        Return Success

    End Function
    ''' <summary>
    ''' Add new row, if successful provide the new record's primary key
    ''' </summary>
    ''' <param name="Name"></param>
    ''' <param name="ContactName"></param>
    ''' <param name="Identfier"></param>
    ''' <returns></returns>
    Public Function AddNewRow(ByVal Name As String, ByVal ContactName As String, ByRef Identfier As Integer) As Boolean
        Dim Success As Boolean = True

        Try
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customers (CompanyName,ContactName) Values(@CompanyName,@ContactName)"

                    cmd.Parameters.AddWithValue("@CompanyName", Name)
                    cmd.Parameters.AddWithValue("@ContactName", ContactName)

                    cn.Open()
                    Dim Affected As Integer = cmd.ExecuteNonQuery()
                    If Affected = 1 Then
                        cmd.CommandText = "Select @@Identity"
                        Identfier = CInt(cmd.ExecuteScalar)
                    End If
                End Using
            End Using
        Catch ex As Exception
            Success = False
            mExceptiom = ex
        End Try

        Return Success

    End Function
End Class

Answer 2

The DELETE command is not quite right. You do not need to specify the columns in a command as you are deleting the row not the columns:

DELETE FROM [TableName]

Next you should be using parameters when executing SQL commands. This is to reduce syntax issues but more importantly stops SQL injection. See Bobby Tables for more details on this. I use the ? placeholder within my SQL command when using parameters. I also specify the data type so consider using the OleDbParameter Constructor (String, OleDbType) to add your parameters.

I would also consider implementing Using:

Managed resources are disposed of by the .NET Framework garbage collector (GC) without any extra coding on your part. You do not need a Using block for managed resources. However, you can still use a Using block to force the disposal of a managed resource instead of waiting for the garbage collector.

You could implement a check for the value returned by ExecuteNonQuery() to see if the row was deleted before inserting the new row.

All together your code would look something like this:

uniqid = "1"
Dim rowDeleted As Boolean

Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Resources\DBpPNRGENERATORDATA.accdb;Persist Security Info=True")
    Using cmd As New OleDbCommand("DELETE FROM [TEMPSIGNIN] WHERE [IDENTIFIER] = ?", con)
        con.Open()

        cmd.Parameters.Add("@Id", OleDbType.Integer).Value = uniqid

        rowDeleted = cmd.ExecuteNonQuery() = 1
    End Using

    If rowDeleted Then
        Using cmd As New OleDbCommand("INSERT INTO [TEMPSIGNIN] ([IDENTIFIER], [EPR], [Partition], [Host]) VALUES (?, ?, ?, ?)", con)

            cmd.Parameters.Add("@Id", OleDbType.[Type]).Value = uniqid
            cmd.Parameters.Add("@EPR", OleDbType.[Type]).Value = tbSigninEPR.Text
            cmd.Parameters.Add("@Partition", OleDbType.[Type]).Value = cbSignInPartition.Text
            cmd.Parameters.Add("@Host", OleDbType.[Type]).Value = tbSignInAl.Text

            cmd.ExecuteNonQuery()
        End Using
    End If
End Using

Note that I have used OleDbType.[Type]. You will want to replace [Type] with the data type you've used on your database.

Answer 3

As mentioned above, your syntax for the DELETE SQL is wrong. So when the database tries to execute it, it fails, so the INSERT never runs. Change the DELETE to:

 CmdSignIn.CommandText = "DELETE FROM TEMPSIGNIN WHERE IDENTIFIER='" + uniqid + "'"

Also, you should learn how to use SQL Parameters and not use string concatenation to create a SQL string. Using parameters your DELETE query becomes something like (this is not 100%, it's off the top of my head)

CmdSignIn.CommandText = "DELETE FROM TEMPSIGNIN WHERE IDENTIFIER=@ID"
CmdSignIn.Parameters.Add("@ID", uniqid)
CmdSignIn.ExecuteNonQuery()