run multiple SQL statement in OleDbCommand

12 Jul 2006 2:19 AM

wesbird

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

12 Jul 2006 3:38 PM

AlBruAn

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