Simple VB ADO.Net Transaction Processing

Posted on 2007-10-06
Last Modified: 2013-11-26
Anyone got any simple VB ADO.Net 2.0 Transaction processing examples?

I am fully conversant with classic ADO TP in VB6 but am now trying to do a simple task in VB.Net using SQL Server 2005 i.e.:

Start a transaction
Get the next record number from a control table and update it.
Create one master numbered form the control table and one detail record

I know that it can't be difficult, so any ideas anyone?
Question by:grwallace
    LVL 16

    Accepted Solution

    I hope this isn't too much for you, you won't need all of it....

    Imports System.Transactions
    Dim options As New TransactionOptions()
    options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
    options.Timeout = New TimeSpan(0, 2, 0)
    Using scope As New TransactionScope(TransactionScopeOption.Required, options)

    Using connA As New SqlConnection(connStringA)

    Using cmdA As New SqlCommand(sqlStmtA, connA)

    Dim rowsAffectedA As Integer = cmdA.ExecuteNonQuery()        

    If (rowsAffectedA > 0) Then

    Using connB As New SqlConnection(connStringB)

    Using cmdB As New SqlCommand(sqlStmtA, connB)

    Dim rowsAffectedB As Integer = cmdB.ExecuteNonQuery()

    If (rowsAffectedB > 0) Then


    End If

    End Using ' Dispose the second command object.

    End Using ' Dispose (close) the second connection.

    End If

    End Using ' Dispose the first command object.

    End Using ' Dispose (close) the first connection.

    End Using ' Dispose TransactionScope object, to commit or rollback transaction.
    LVL 16

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video discusses moving either the default database or any database to a new volume.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now