Internal .NET framework data provider error 12

Hi,

I'll give as good an explanation as I can for the error I "occasionally" am getting.

I have a VB.NET DLL that gets called from an application on a server farm, maybe 100 users and also a website sat on the farm which calls the DLL.

Since all of my users are from different companies, they all have their own databases, so the DLL queries a table to return a connectionstring for the appropriate database for that user.

Sometimes there are lots of calls from various DLLs that perform various functions, but call that table to get its connectionstring and then go do some work on that database, whether it is doing an INSERT/UPDATE/DELETE or SELECT as well as looped datatables for data-processing. So there are a LOT of connections going on to SQL2000.

The app is currently running in VB6 as well and we've never had any issue (in over 10 years!), but since I have started upgrading it to .NET 4.0 Im starting to get

Quote:

Internal .Net Framework Data Provider error 12

and
sometimes preceeded with:

Quote:

Object reference not set to an instance of an object

which I presume is probably because of a failed connection.

Doing various searches on the issue, seems to be something to do with my DLL connectivity hammering the SQL Server (2000) database - I am using OLEDB for connectivity and the google results where I have looked, seem to point to OLEDB threading which is not very good - my DLL and other apps do not utilise threads directly but I guess with it getting called multiple times from a VB6 app, some .net win forms apps and multiple vb.net DLLs is just too much.

I just dont understand how VB6 has competently been handling this ok for over a decade and then when I run it in vb.net its falling over in a heap. When I run things as a single user or run it locally on my VM dev machine, I dont get an issue, its when things are getting hammered by multiple users the errors start appearing.

Any help would be really appreciated, its driving me up the wall and I dont know where to look next :(

Thanks in advance :)

Carl

IMPROVE QUESTION:

So I have DLL (CalculateScore) that I call from a VB6 DLL that gets called from a classic ASP page. (I have the DLLs to do the background coding as the same code gets shared with a VB6 and Winforms App)

The CalculateScore DLL, first calls another .net dll, called CONNMAN, which goes and fetches the connection string and returns it back to calculatescore so it knows what database to open on SQL Server 2000. The reason I have a CONNMAN dll, is that there are other DLLS that call that DLL to return the appropriate connection string.

The CalculateScore DLL function, opened up Connman:

Public Function OpenPassConnection(ByVal CorporationID As String, ByVal TestProj As Boolean) As Boolean

       Try


           Dim NewConn As New Connman.ConnMan

           myConnectionString = NewConn.ReturnConnectionString(P3AddON_CORPID)

           NewConn = Nothing


           If myConnectionString <> "" Then
               GConn = New OleDb.OleDbConnection
               GConn.ConnectionString = myConnectionString
               GConn.Open()
           End If

       Catch ex As Exception
           Using sw As IO.StreamWriter = IO.File.AppendText(System.IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly().Location) & "\PASS4_CALCSCORE" & CorporationID & "_errorlog.txt")
               sw.WriteLine(Now & " - OpenPassConnection - " & ex.Message)
           End Using
           '  Windows.Forms.MessageBox.Show("OpenPassConnection:" & Err.Description, "Pass4", Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error)
           Return False
       End Try

       Return True

   End Function

This "CONNMAN" dll opens up like so:


resultstr=

OpenPassConnection(ByVal CorporationID As String) As String

The function looks at a text file in the current directory, to pull up a connection to a SQL Server 2000 database that is holding records, per company and returns a connection string from a field then opens up the connection and does some work (I'll paste that after this function below):

Public Function OpenPassConnection(ByVal CorporationID As String) As String


       Dim myConnectionString As String = ""
           Dim Pass4DBConnectionStr As String = ""
           Dim SQLStr As String

       Dim path As String = System.IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly().Location) & "\Connection\ConnStr.txt"

       Try

           If FileIO.FileSystem.FileExists(path) = False Then
               Return "Unable to find file:" & path
           End If

           Dim sr As StreamReader = New StreamReader(path)
           myConnectionString = sr.ReadLine()
           sr.Close()

           If myConnectionString <> "" Then
               Gconn = New OleDb.OleDbConnection
               Gconn.ConnectionString = myConnectionString
               Gconn.Open()
           End If

           ' Now lets get the main connectionstring for the database and return it
           SQLStr = "SELECT [ServerDBConnString] FROM [Pass4_Connections] WHERE [CorpID]='" & FixsQuote(CorporationID) & "'"
           Pass4DBConnectionStr = GetAnyDetail(CorporationID, SQLStr, "ServerDBConnString", "")


       Catch ex As Exception
           Return "ERROR:" & ex.Message
       End Try

       Return Pass4DBConnectionStr

   End Function

so when the connection string is returned to CalculateScore and opens that connection and gets to this bit of code:

SQLstr = "SELECT CREATE_PERCENT,impacts,propertyref,subjective,increase_improve,mechanism,[assessmentsummary].* from [questionheading] inner join [assessmentsummary] on [questionheading].[questionheadingref]=[assessmentsummary].questionheadingref where [assessmentsummary].assessmentsummaryref=" & AssRef
           da = New OleDb.OleDbDataAdapter(SQLstr, GConn)
           ds = New DataSet
           da.Fill(ds, "GettingItem")
           If ds.Tables(0).Rows.Count = 0 Then
               da.Dispose()
               ds.Dispose()
               da = Nothing
               ds = Nothing

               Using sw As IO.StreamWriter = IO.File.AppendText(System.IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly().Location) & "\PASS4_" & CorporationID & "_errorlog.txt")
                   sw.WriteLine(Now & " - NO RECORDS ERROR")
               End Using

               Return "No Records, ERROR"
           End If

If I call this code through a winforms app and debug and follow it through, I get no errors. <<sometimes>> it is falling over on this code with (different errors, depending on how heavy usage is):

ExecuteReader requires an open and available Connection. The connection's current state is connecting.

Internal .Net Framework Data Provider error 12.

Object reference not set to an instance of an object.

Looking at these errors (in particular the current state is connecting) has led me to something you have mentioned about "Using" and "End Using" - now Im fine enclosing everything in that, instead of having a global function to open, but I do use .close .dispose and =nothing at the end of my usage (im quite OCD on making sure I close things after it has been opened) - but can anyone tell me <<why>> using "Using" and "End Using" is better (is it?) than opening a connection in a function and closing at the end of my code which is what I have always done in VB? I know I have a LOT to learn from moving from VB to .NET, but I'd like to understand. Thanks!

What I have tried:

I have found that in VB6, my DLLs are "Apartment Threaded" - there's no such option in VB.NET studio 2015 - I guess I need to make my DLLs apartment-threaded - Im not too bothered about performance as I have had no problems with the VB6 setup, I just need to eliminate my issue

Tags: Visual Basic, Database Development, OleDb