run multiple SQL statement in OleDbCommand

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:
I am using 2 datagrid in a master-detail, now if I want delete a record in
master, I should delete all child record of this master record before I
delete the master.
I don't know how to run mutiple SQL statement in my custom OleDbCommand?
I really appreciate your help.

here is the code:
Dim cmdDelete As New OleDbCommand("DELETE FROM detail WHERE id = ?",
_myUtilDB.irraConnection)
'Dim cmdDelete As New OleDbCommand("DELETE FROM master WHERE id =
?", _myUtilDB.irraConnection)
With cmdDelete.Parameters.Add("@p1", GetType(Double))
.SourceColumn = "id"
.SourceVersion = DataRowVersion.Original
End With

_da.DeleteCommand = cmdDelete
 
You should wrap the SQL commands inside an OleDbTransaction to prevent orphan
records should the row be deleted from the master table and not from the
detail table; ideally, the DB has been set up to prevent deletion of a master
record without having delete the child records previously. Anyway, here's a
sample (copied and modified from the MSDN help files) of how to accomplish
what you're wanting to do:

Public Sub ExecuteTransaction(ByVal connectionString As String)

Using connection As New OleDbConnection(connectionString)
Dim command As New OleDbCommand()
Dim transaction As OleDbTransaction

' Set the Connection to the new OleDbConnection.
command.Connection = connection

' Open the connection and execute the transaction.
Try
connection.Open()

' Start a local transaction.
transaction = connection.BeginTransaction()

' Assign transaction object for a pending local transaction.
command.Connection = connection
command.Transaction = transaction

' Execute the commands.
command.CommandText = _
"DELETE * FROM detail WHERE id=" + ID_of_record_to_be_deleted
command.ExecuteNonQuery()
command.CommandText = _
"DELETE * FROM master WHERE id=" + ID_of_record_to_be_deleted
command.ExecuteNonQuery()

' Commit the transaction.
transaction.Commit()
Console.WriteLine("All records for ID = " + ID + "were deleted
from the database.")

Catch ex As Exception
Console.WriteLine(ex.Message)
' Try to rollback the transaction
Try
transaction.Rollback()
Catch
' Do nothing here; transaction is not active.
End Try
End Try
' The connection is automatically closed when the
' code exits the Using block.
End Using
End Sub

Wrapping the OldDbCommand inside an OleDbTransaction ensures that the
records are either deleted from both tables or from neither table. If
there's a problem deleting records from only one of the tables, the
transaction.RollBack() command leaves both tables in their original state.

HTH

Allen Anderson
 
Back
Top