question

FredPeters-1282 avatar image
0 Votes"
FredPeters-1282 asked FredPeters-1282 answered

Failed Record Deletion in Database : Visual Studio 2015: MS Access 2019

When opening the form and following the Form structure to delete the record, it does delete from my Form.datagridview, but when closing and reloading the form, the record is there.

my source database shows in the 'server explorer' with the "red x" and remains red throughout the life of the form, start to finish and restart.

I'm wracking my brains. My poor brains. I have tries myriads of tableadapter and binding source combinations, to no avail.
Does anyone have any ideas?

  Public Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
         If Me.EnableEditingCheckbox.Checked Then
    
             If Me.AddNewButton.Text = "Cancel" Then
                 Exit Sub
             End If
    
             If Not DataGridView1.Rows.Count = 0 Then
                 Try
    
                     Dim result As DialogResult
    
                     result = MessageBox.Show("Do you want to permantely DELETE the selected record?", "DELETION Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
    
                     If result = DialogResult.Yes Then
    
                         Me.TTimeBindingSource.RemoveCurrent()
                         Me.TTimeBindingSource.EndEdit()
                         Me.TTimeTableAdapter.Update(DocketMV4DataSet1Apr1.tTime)
    
                     End If
    
                 Catch ex As Exception
    
                 End Try
             End If
    
         Else
             MessageBox.Show("Access Denied!" & Environment.NewLine & "You need permission to perform this action" & Environment.NewLine & Environment.NewLine & "Please contact the programmer or ask Google!", "Delete operation failed", MessageBoxButtons.OK, MessageBoxIcon.Stop)
             Return
     Exit Sub
         End If
    
     End Sub

dotnet-visual-basicoffice-access-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


If you have a database file in your project folder, and another file which is copied automatically to output folder (probably “bin\Debug” subfolder), then the program will work with the latter one. The original file is not affected. If you want to keep the changes, you can copy the output file manually to original location. (Also make a backup copy of the original database).

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered karenpayneoregon commented

If the database is part of the project such as a MS-Access database or a local SQL-Server database, right click on the database in solution explorer, select properties. Look at Copy to Output folder, for you set it to copy if newer as the default is to copy always which means each time the project runs a fresh copy is placed in the debug folder wiping former changes.

If the database is a SQL-Server (server based or express edition) this should not happen when you have a primary key set.

Lastly, make sure there is a delete command being generated if this is using a TableAdapter (highly not recommend using them).

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Karen,
Thank you for your quick response.

My mdb file shows in both iin Server Explorer/Data Connections as well as Solution Explorer.
In Solution Explorer, I did reset the CopyToOutput to copy if newer. I was so excited..... It did not change the mdb, Probably due to your last line as referenced last paragraph here.

I am not using SQL-Server but I do have a primary key set.

"Lastly, make sure there is a delete command being generated if this is using a TableAdapter (highly not recommend using them)." ??? I am using a TableAdapter but would you have a sample line to show parameters?

Thank you again!!!
Fred

0 Votes 0 ·

We don't care about what's shown in Server Explorer or Data Connections. As indicated in my last reply, you need to check the property of the database in Solution Explorer e.g. (in this case I have a .accdb as .mdb is not wise).

84109-f1.png

For full details on Copy to Output Directory see my Microsoft article.

Regarding table adapters

At first glance, this way of working with data appears easy yet if time is not taken to learn about them after using the data wizard there can be countless times of frustrations. The main thing about table adapters is when the backend database changes and you have data bindings on a form which are not updated by the data wizard, you need to manually do this and the form may become unstable. Out of room here to write more.


0 Votes 0 ·
f1.png (16.2 KiB)

BTW The Delete command is in the designer.vb e.g.

 Me._adapter.DeleteCommand.CommandText = "DELETE FROM [dbo].[Customers] WHERE (([CustomerIdentifier] = @Original_CustomerId"& _ 
     "entifier) AND ([CompanyName] = @Original_CompanyName) AND ((@IsNull_ContactName "& _ 
     "= 1 AND [ContactName] IS NULL) OR ([ContactName] = @Original_ContactName)))"

0 Votes 0 ·
FredPeters-1282 avatar image
0 Votes"
FredPeters-1282 answered

Thank You Karen,

I am reading your articles/postings you referenced earlier.
I was not able to make a connection string to an accbd. "not registered" Thats why I changed my data back into an mdb.
I AM able to delete records, but not update and am trying to work through that.
Thank you. Its a nagging problem, but when resolved, I'm sure I'll be that much better of a ??

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.