Testint success of a DELETE-statements against an Access-MDB compared to SQL Server (.Net 2.0) ..?

  • Thread starter Thread starter Olaf Rabbachin
  • Start date Start date
O

Olaf Rabbachin

Hi *,

using an OleDbCommand I issue a DELETE statement against an Access-MDB.
This roughly looks like this:

Try
Dim cmd As New OleDbCommand("qry_DeleteSomething", DB_Connection)
cmd.CommandText = "DELETE FROM tblXY WHERE ID = 123"

If DB_Connection.State <> ConnectionState.Open Then DB_Connection.Open()
Dim intRecsAffected As Integer = cmd.ExecuteNonQuery
DB_Connection.Close()

Catch exOLEDB As OleDbException
...
Catch ex As Exception
...
End Try

I would like to execute this code even if the DELETE might fail, i.e.
because there is a record in a different table that is linked to the one
that is to be deleted (i.e. attempting to delete a customer record while
there is invoices that are associated with that customer).
Instead, I was assuming that, just like what happens when you try to delete
a record from within Access, an exception is being raised. This doesn't
happen though - no exception to catch. Even worse, <intRecsAffected> in the
above sample will equal <1>, as if the underlying DELETE statement had been
executed successfully!?
If I do the exact same thing with a SQL Server database, I can catch
<SqlException.Number=547> if the DELETE fails.

Thus, how can I find out as to whether a DELETE was actually successful or
not? There just has to be something other than doing a SELECT after the
DELETE, just to confirm that the record isn't there anymore!?
Also, the same applies to INSERTs and UPDATEs where i.e. a unique index
prevents adding or changing a record ...

Cheers & TIA,
Olaf
 
Olaf Rabbachin said:
Instead, I was assuming that, just like what happens when you try to
delete a record from within Access, an exception is being raised.
This doesn't happen though - no exception to catch.
Click to expand...

I've tried it and I do get an exception if there are related records and
cascaded deletes are not enabled.


Armin
 
Hi Armin,

Armin said:
I've tried it and I do get an exception if there are related records and
cascaded deletes are not enabled.
Click to expand...

drat. You're right, of course. It was just the dumb person that operated
the keyboard, not remembering that - instead of deleting - records are just
being flagged as <Deleted>. Hence the command (just a query's name in .Net
via a OleDbCommand plus a couple of parameters) was rather an update-query.

I'm getting old ... :-)

Cheers,
Olaf
 
Back
Top