SQL BeginTransaction Command

Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyDataAdapter As SqlDataAdapter
 
        'Create a connection to the SQL Server.
        MyConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
        MyConnection.Open()
        'Create a DataAdapter, and then provide the name of the stored procedure.
        MyDataAdapter = New SqlDataAdapter("sp_GetTXNNumber", MyConnection)
 
        'Set the command type as StoredProcedure.
        MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
 
        'Create and add an output parameter to Parameters collection. 
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@TXNNumber", SqlDbType.Int, 8))
 
        'Set the direction for the parameter. This parameter returns the Rows returned.
        MyDataAdapter.SelectCommand.Parameters("@TXNNumber").Direction = ParameterDirection.Output
 
        DS = New DataSet() 'Create a new DataSet to hold the records.
        MyDataAdapter.Fill(DS, "TransactionNumber") 'Fill the DataSet with the rows returned.
 
        ' Start a local transaction
        Dim myTrans As SqlTransaction = MyConnection.BeginTransaction()
 
        Dim myCommand As New SqlCommand()
        myCommand.Connection = MyConnection
        myCommand.Transaction = myTrans
 
        Try
 
            myCommand.CommandText = "Insert into TXNBonusPoints (MemberCode, BonusDate, ReasonCode, BonusPoints, Comments, TXNNumber, UserAllowed, Active) values (@MemberCode , @BonusDate, @ReasonCode, @BonusPoints, @Comments, @TXNNumber, @UserAllowed, @Active)"
            Dim Param As New SqlParameter
            Param.ParameterName = "@MemberCode"
            Param.Value = txt_MemberCode.Text
            myCommand.Parameters.Add(Param)
 
            Dim Param1 As New SqlParameter
            Param1.ParameterName = "@BonusDate"
            Param1.Value = Date.Today.ToString("dd-MMM-yyyy")
            myCommand.Parameters.Add(Param1)
 
            Dim Param2 As New SqlParameter
            Param2.ParameterName = "@ReasonCode"
            Param2.Value = DDL_reasonCode.SelectedValue
            myCommand.Parameters.Add(Param2)
 
            Dim Param3 As New SqlParameter
            Param3.ParameterName = "@BonusPoints"
            Param3.Value = CInt(txt_BonusPoints.Text)
            myCommand.Parameters.Add(Param3)
 
            Dim Param4 As New SqlParameter
            Param4.ParameterName = "@Comments"
            Param4.Value = txt_Comments.Text
            myCommand.Parameters.Add(Param4)
 
            Dim Param5 As New SqlParameter
            Param5.ParameterName = "@TXNNumber"
            Param5.Value = MyDataAdapter.SelectCommand.Parameters(0).Value
            myCommand.Parameters.Add(Param5)
 
            Dim Param6 As New SqlParameter
            Param6.ParameterName = "@UserAllowed"
            Param6.Value = User.Identity.Name()
            myCommand.Parameters.Add(Param6)
 
            Dim Param7 As New SqlParameter
            Param7.ParameterName = "@Active"
            Param7.Value = 1
            myCommand.Parameters.Add(Param7)
 
            myCommand.ExecuteNonQuery()
            myCommand.Parameters.Clear()
 
            myCommand.CommandText = "Insert into TXNPointSummary (TXNNumber, MemberCode, PointsType, TXNDate, Points, UserUpdated, Comments) values (@TXNNumber, @MemberCode, @PointsType, @TXNDate, @Points, @UserUpdated, @Comments)"
 
            Dim SumParam As New SqlParameter
            SumParam.ParameterName = "@TXNNumber"
            SumParam.Value = MyDataAdapter.SelectCommand.Parameters(0).Value
            myCommand.Parameters.Add(SumParam)
 
            Dim SumParam1 As New SqlParameter
            SumParam1.ParameterName = "@PointsType"
            SumParam1.Value = "BP" 'Bp = Bonus Points
            myCommand.Parameters.Add(SumParam1)
 
            Dim SumParam2 As New SqlParameter
            SumParam2.ParameterName = "@MemberCode"
            SumParam2.Value = txt_MemberCode.Text
            myCommand.Parameters.Add(SumParam2)
 
            Dim SumParam3 As New SqlParameter
            SumParam3.ParameterName = "@TXNDate"
            SumParam3.Value = Date.Today.ToString("dd-MMM-yyyy")
            myCommand.Parameters.Add(SumParam3)
 
            Dim SumParam4 As New SqlParameter
            SumParam4.ParameterName = "@Points"
            SumParam4.Value = txt_BonusPoints.Text
            myCommand.Parameters.Add(SumParam4)
 
            Dim SumParam5 As New SqlParameter
            SumParam5.ParameterName = "@UserUpdated"
            SumParam5.Value = User.Identity.Name()
            myCommand.Parameters.Add(SumParam5)
 
            Dim SumParam6 As New SqlParameter
            SumParam6.ParameterName = "@Comments"
            SumParam6.Value = txt_Comments.Text
            myCommand.Parameters.Add(SumParam6)
 
            myCommand.ExecuteNonQuery()
 
            ' Attempt to commit the transaction.
            myTrans.Commit()
            Message = "Bonus Point Successfully Entered"
            MyMessageBoxInfo.Show(MyMessageBox.MessageType.Success, Message, 120, 300)
           MyConnection.Close()

Select allOpen in new window