Access database not updated when records are deleted

[Guest]

The code for my 'delete record' button is:

Dim dc As New OleDbConnection
Dim dbLocation As String = "E:\NITLC\VB.NET\VideoClub\VideoClub.mdb"
dc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
dbLocation
Dim daCategorySelectText As String = "SELECT * FROM tblCategories"
Dim daCategory As OleDbDataAdapter = New
OleDbDataAdapter(daCategorySelectText, dc.ConnectionString)
Dim ds As New DataSet
Dim ButtonSubject, strItem, strDeletePrompt, strDeleteTitle As String
Dim result As DialogResult
strDeleteTitle = "Delete a "
strDeletePrompt = "You are about to delete the "
dc.Open()
daCategory.FillSchema(ds, SchemaType.Source, "tblCategories")
daCategory.Fill(ds, "tblCategories")
ButtonSubject = "Category"
strItem = ListBox1.GetItemText(ListBox1.SelectedItem)
Dim cmdBuilder As New OleDbCommandBuilder(daCategory)
ds.Tables("tblCategories").Rows(ListBox1.SelectedIndex).Delete()
strDeleteTitle += ButtonSubject
strDeletePrompt += ButtonSubject + " '" + strItem + "'." + Chr(13) + Chr(10)
strDeletePrompt += "Are you sure you want to delete it?"
result = MessageBox.Show(strDeletePrompt, strDeleteTitle,
MessageBoxButtons.YesNo)
If result = DialogResult.Yes Then
ds.Tables("tblCategories").AcceptChanges()
daCategory.Update(ds, "tblCategories")
Else
ds.Tables("tblCategories").RejectChanges()
End If
ListBox1.DataSource = ds.Tables("tblCategories")
dc.Close()

I've written similar code for add and edit operations, and those DO update
the database. The above code SEEMS to work - the highlighted entry disappears
- but when I browse the database in Access 2003, or exit from the app and run
it again, the deleted record is still there!

What am I missing?

 

[Miha Markic [MVP C#]]

Hi,

Oooo, this is a classic one.
You don't need and you shouldn't call AcceptChanges (before Update) in your
case.

 

[Cor Ligthert]

Miha,

Which gives mostly a lot of answers what will it be today, however I sand
this message.



Cor

 

[Grzegorz Danowski]

The code for my 'delete record' button is:

Dim dc As New OleDbConnection
Dim dbLocation As String = "E:\NITLC\VB.NET\VideoClub\VideoClub.mdb" (...)

If result = DialogResult.Yes Then
ds.Tables("tblCategories").AcceptChanges()
daCategory.Update(ds, "tblCategories")

Please reverse order:
daCategory.Update(ds, "tblCategories")
ds.Tables("tblCategories").AcceptChanges()

Regards,
Grzegorz

 

[Guest]

I reversed the order as Grzegorz suggested, but the record I supposedly
deleted still wouldn't stay dead. I even tried commenting out AcceptChanges -
same result. The database is definitely not read-only; the code for adding
and editing a record works. I used Debug to display the RowState for that
row, and it returned Deleted - as it should.

What, other than the usual Microsoft weirdness, is going on?

(sigh) I'll do some more research...as if I haven't done enough research
already in the course of learning VB.NET and ADO.NET. But I WILL crack this.
I do not allow code to defy me, I'm too stubborn for that. This app WILL do
what I want it to. It WILL concede my mastery. It will comply. Resistance is
futile.

Thanks, guys!

 

[Guest]

Okay. I am now definitely living in Weirdsville, Microsoftland.

To explain: I looked up the OleDbCommandBuilder class in VB.NET help, and I
tried creating a delete command explicity. It STILL didn't work, so I
commented it out again and single-stepped through the code, trying to figure
out what was going wrong.

And nothing did. The record I tried to delete WAS deleted, as I discovered
when checking it in Access.

After I'd finished gaping at the screen, just to confirm that there was new
weirdness going on and I hadn't lost what remains of my mind, I deleted the
database and replaced it with the unpopulated, pristine copy I keep as
required by the terms of my MCAD assignment. I added a few records, edited
one or two and then tried to delete one. The app quite happily obliged; the
record was deleted, and stayed deleted.

Essentially I've put the code back the way it was before I explicitly
created the delete command, with Update and AcceptChanges as Grzegorz
suggested. Nothing else has changed. So why does it work now when it didn't
before?!?

Oh well. At least I've cracked it. Thanks for the help, troops!

 

[Guest]

Okay. I am now officially living in Weirdsville, Microsoftland.

To explain: I looked up OleDbCommandBuilder in Help, and I tried explicitly
creating a Delete command as shown. It STILL didn't work, so I commented it
out and tried single-stepping through the code, trying to figure out what
was/wasn't happening...

....and it worked. THE RECORD I WAS TRYING TO DELETE *WAS* DELETED!

After I'd retrieved my jaw from the floor and glued it back into place, I
decided to test this new, baffling event just to confirm it really WAS
happening and I hadn't lost what remains of my mind. I deleted the database
and replaced it with the unpopulated copy I keep as required by the terms of
my MCAD assignment. I added a few records; no problem. I edited one or two;
all changes were written to the database witthout a hitch. So far so good.

I tried deleting one. It was deleted.

Essentially the code is as it was before I tried creating an explicit Delete
command (although I've discovered that AcceptChanges is indeed unnecessary).
So why the f*** is it working now when it didn't before?! Why is the damn
code now looking back nonchalantly at my baffled face as if to say: 'What?'
?! What IS it with these damn apps?!

Oh well. At least it works now, and I can get on with creating the rest of
the app. Thanks for the help, troops!

 

[Guest]

Oops. I got a network error when sending the first version of the above post,
so I thought it hadn't been received. Obviously it was.

Isn't there a way of deleting your own messages from the board?

 

[Miha Markic [MVP C#]]

Essentially the code is as it was before I tried creating an explicit
Delete
command (although I've discovered that AcceptChanges is indeed
unnecessary).
So why the f*** is it working now when it didn't before?!

AcceptChanges consolidates RowStates and it is implicitly called after
Update executes.