CODEWITHSUNDEEP
vb.net
Gimo Gilmore
Gimo Gilmore

Reputation: 229

Error - The transaction is either not associated with the current connection or has been completed

Having this error in my web app.

The transaction is either not associated with the current connection or has been completed.

#Region "Database Queries"
Private Shared oSqlConnection As SqlConnection
Private Shared oSqlDataAdapter As SqlDataAdapter
Private Shared oSqlCommand As SqlCommand
Private Shared oSqlTransaction As SqlTransaction

Private Shared _strCommand As String

Public Shared Property strCommand() As String
    Get
        Return _strCommand
    End Get

    Set(ByVal value As String)
        If Not InTransaction Then RollBack_Transaction()
        _strCommand = "SET DATEFORMAT mdy " & vbCrLf & value
        DbQuery()
    End Set
End Property

Protected Shared Sub DbQuery()
    'Try

    If Not InTransaction Then
        RollBack_Transaction()

        oSqlCommand = New SqlCommand(_strCommand, oSqlConnection)
    Else
        oSqlCommand = oSqlConnection.CreateCommand
        If _InTransaction_Initial Then
            oSqlConnection.Open()
            oSqlTransaction = oSqlConnection.BeginTransaction(IsolationLevel.ReadCommitted)
            _InTransaction_Initial = False
        End If

        oSqlCommand.Transaction = oSqlTransaction
        oSqlCommand.CommandText = _strCommand
        oSqlCommand.CommandTimeout = 0
    End If

    oSqlDataAdapter = New SqlDataAdapter(oSqlCommand)
    Ds = New DataSet
    oSqlDataAdapter.Fill(Ds) '**<- - - The error occurs here**'

    'Catch ex As Exception
    '    SetMessage(ex)
    '    ' MsgBox("Server: " & ServerName & vbCrLf & "User Id: " & SqlID & vbCrLf & "Password: " & SqlPassword & vbCrLf & "Database: " & MainDb)
    'End Try

    'oSqlConnection.Close()

End Sub

Private Shared _InTransaction_Initial As Boolean = False
Private Shared InTransaction As Boolean

Public Shared Sub StartTransaction()
    _InTransaction_Initial = True
    InTransaction = True
End Sub
Public Shared Sub RollBack_Transaction()
    Try
        oSqlTransaction.Rollback()
    Catch ex As Exception
    End Try
    Try
        oSqlConnection.Close()
    Catch ex As Exception
    End Try

    InTransaction = False
End Sub

Public Shared Sub Commit_Transaction()
    Try
        oSqlTransaction.Commit()
    Catch ex As Exception
    End Try
    Try
        oSqlConnection.Close()
    Catch ex As Exception
    End Try

    InTransaction = False
End Sub

Function dsCount() As Long
    Return Ds.Tables(0).Rows.Count
End Function

Please see the Bold text in the code.

Hope to hear positive response from you.

Regards,

Upvotes: 1

Views: 10566

Answers (1)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

It error happens because you have used Shared member variables.

You can uses shared functions, but you should avoid shared variables or members at all.
Don't "save" any information in shared context. These are available in application scope and that may be the same for different requests from different users.

Running data access operations in shared (static) functions should be no problem.
But having shared members will cause integrity and concurrency problems making the application unstable

placing these as variables inside methods 

oSqlConnection As SqlConnection
oSqlDataAdapter As SqlDataAdapter
oSqlCommand As SqlCommand
oSqlTransaction As SqlTransaction

will solve the problem.

Upvotes: 1

Related Questions