Back to Back Dependent Stored Procedures

I have two stored procedures that save data from an online credit application to a SQL Server 2000 database. The first procedure saves basic application data to an application table and outputs the application ID. The second procedure uses the resulting application ID and saves applicant data to an applicant table. I am using two tables since any one application can have two applicants (i.e., primary and co-applicant). The applicant table is the "many" relationship to the application table's "one" relationship.

The VB.Net code that I use to call the stored procedures is as follows (note: this is after I have set up the database connection and all the parameters, etc.):

' Execute the first stored procedure to save application data to the application table.

cmdApplication.ExecuteNonQuery()

' Obtain application ID from above stored procedure (paramAppID) and fill a declared temporary variable (tmpAppID).

tmpAppID = CInt(paramAppID.Value)

' Use the tmpAppID variable as the application ID for the second stored procedure.

Dim paramAppID2 As New SqlParameter("@AppID", SqlDbType.Int, 4)
paramAppID2.Value = tmpAppID
cmdApplicant.Parameters.Add(paramAppID2)

' Execute the second stored procedure to save the applicant data to the applicant table.

cmdApplicant.ExecuteNonQuery()

' Close the database connection.

conApplicaiton.Close()


If there is a problem saving data to either the application table or the applicant table, I want the entire transaction rolled back (as though it were never done).

************
Given the above scenario, what is the best method for rolling back the transaction upon failure? Please provide an example. Thank you in advance.

************

Note: I have seen the Try..Catch method. However, I am not sure of how to rollback the transaction upon failure.