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
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