Ok, I think I have resolved it. Basically, is in related to my quest for optimization. It is what you get penalized for, after searching for more than one day to resolve that issue, when you build Web applications. For such applications, as it was the case in VFP, I am opening up all data sources so this saves a lot of time for each Web server process. So, for this .NET framework, one I started to work on since about a year, when I wanted to optimize in that direction, I decided to create all data source connections when the application loads. I thought I would them be able to pass them as a parameter by the use of the data provider object.

This works really well for desktop and ASP.NET applications. But, for Web Services, when simultaneous hits are being received, it caused me all kind of problems. Basically, as the same connection object was passed to the data provider object, when simultaneous hits were received, one session was overwriting the other and so on.

So, to resolve the issue, I am passing the connection string instead and create the connection object inside the SQLExec() method. By doing that, the entire data objects are encapsulated within that method only and no mix up of environments can happen. Here is the new code of the Framework.Data() class:

Imports System.Data.OleDb

Public Class Data

    Public oDataAdapter As OleDbDataAdapter
    Public oDataSet As DataSet
    Public oDataView As DataView
    Public cSQL As String
    Public cConnectionString As String
    Public oConnection As OleDbConnection
    Public nCount As Integer

    ' SQL exec
    ' expC1 SQL command
    ' expO1 Connection
    Public Function SQLExec(ByVal tcSQL As String, ByVal tcConnection As String) As Boolean
        Dim llSuccess As Boolean
        Dim loConnection As OleDbConnection
        Dim lnStatus As Integer
        llSuccess = False
        cSQL = tcSQL
        cConnectionString = tcConnection
        Try
            lnStatus = 1
            loConnection = New OleDbConnection(tcConnection)
            lnStatus = 2
            loConnection.Open()
            lnStatus = 3
            oDataAdapter = New OleDbDataAdapter
            lnStatus = 4
            oDataAdapter.SelectCommand = New OleDbCommand(tcSQL, loConnection)
            lnStatus = 5
            oDataSet = New DataSet
            lnStatus = 6
            oDataAdapter.Fill(oDataSet, "Temp")
            lnStatus = 7

            ' Record count
            nCount = oDataSet.Tables("Temp").Rows.Count

            ' If we have at least one record
            If nCount > 0 Then
                oDataView = New DataView
                oDataView = oDataSet.Tables("Temp").DefaultView
            End If

            loConnection.Close()
            llSuccess = True
        Catch loError As Exception
            App.ErrorSetup(loError, "Framework.Data.SQLExec()", lnStatus.ToString)
        End Try
        Return llSuccess
    End Function

End Class

It is when I decided to add a status after each line that I was able to locate the exact place where all those errors were coming from. It was in the line where I fill the dataset. After about two hours, this is when I realized that the connection object should be created in here for the encapsulation purpose.

I don't know if this would be as fast as before. Because, for every data request, I need to create a connection.