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
