Click here to Skip to main content
14,297,447 members
Rate this:
Please Sign up or sign in to vote.
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
Posted
Updated 17-Nov-16 2:29am
v3
Comments
Richard MacCutchan 16-Nov-16 6:50am
   
Object reference not set to an instance of an object means you have a bug somewhere, that was not revealed in VB6. And from what you have written above it is quite possible that this has something to do with mutli-threading. It is quite possible that your code is not thread-safe. But the only way to find out is by a lot of debugging.
Member 12561559 16-Nov-16 8:53am
   
I know the code works as it runs fine locally. The only thing I can see that is common where the errors have been occurring, and I dont know if this makes a difference or not as it has worked perfectly fine in VB6 and fine locally, is that I am passing a oledb.connection through a function parameter. I'll strip them out and globally declare the oledb connection. Will update this thread if that sorts it out. Thanks for getting back to me though, most appreciated.
Richard MacCutchan 16-Nov-16 10:09am
   
Wait! Before you change anything you need to be sure that is the problem. Just because something works in VB6 does not mean that it is bug free. It may just be that VB6 masks the problem.
Karthik_Mahalingam 16-Nov-16 6:59am
   
Its a generic error, use debugger to pin point the error.
Member 12561559 16-Nov-16 8:54am
   
I know the code works as it runs fine locally. The only thing I can see that is common where the errors have been occurring, and I dont know if this makes a difference or not as it has worked perfectly fine in VB6 and fine locally, is that I am passing a oledb.connection through a function parameter. I'll strip them out and globally declare the oledb connection. Will update this thread if that sorts it out. Thanks for getting back to me though, most appreciated.
Richard Deeming 16-Nov-16 10:04am
   
It sounds like you're sharing a single OleDbConnection object across multiple calls. That's not a good idea - the class wasn't designed to be thread-safe, and you'll end up corrupting its internal state, leading to strange errors.

Instead, you should create the OleDbConnection when you need it, and put it in a Using block to ensure that it gets disposed of properly.

This problem wouldn't have shown up with VB6, because VB6 was mostly single-threaded.

But as it stands, we can only guess what the problem is, because you haven't shared any of your code. If you want more specific advice, click "Improve question" and add the relevant parts of your code to the question.
Member 12561559 17-Nov-16 5:45am
   
Thanks Richard, I have updated the question - other searches have led me to the Using statement as well - if you wouldn't mind a response to what I have done - I am probably using old bad habits from VB and although its working in .NET, its not working "well". Thank you - I really appreciated the time you're putting into responses.
Member 12561559 17-Nov-16 11:37am
   
Would you mind answering one more question - more for my sanity than anything.
I have converted my code so I do not have a Public oledb connection, but instead use the USING block to open and do some work - however, I do need to call other functions in my DLL and have got it to pass the OLEDB connection opened in the USING statement, across to those functions - will that keep it thread safe or do I need to do more USING blocks in those separate functions to open up a new connection and close it when its finished?
Richard Deeming 17-Nov-16 12:07pm
   
That should be fine. You only need to worry if you're storing the connection object in a field. If you're passing it as a parameter, it's almost always going to be local to the current thread.
Member 12561559 18-Nov-16 1:36am
   
Perfect. You have been most helpful Richard, thank you very much.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

As I suspected, you're sharing a single OleDbConnection instance across multiple calls. All of the errors you're describing are caused by multiple threads trying to access that single object at the same time.

For example:
Thread 1: Gconn = New OleDb.OleDbConnection
Thread 1: Gconn.ConnectionString = myConnectionString
Thread 2: Gconn = New OleDb.OleDbConnection
Thread 1: Gconn.Open() - Error: no connection string set

Or:
Thread 1: Gconn = New OleDb.OleDbConnection
Thread 1: Gconn.ConnectionString = myConnectionString
Thread 1: Gconn.Open()
Thread 2: Gconn = New OleDb.OleDbConnection
Thread 2: Gconn.ConnectionString = myConnectionString
Thread 1: (Tries to use GConn) - Error: connection is not open

By changing your code to create the connection when it's required, rather than sharing a single global connection object, you will avoid all of these race conditions.


You're also using string concatenation to build your queries, which leaves your code vulnerable to SQL Injection[^]. You need to use properly parameterized queries instead.


Also, you should throw an exception when something goes wrong, rather than returning an invalid connection string. Otherwise, your calling code has to check for every possible "something went wrong" return value before using the returned value as a connection string.


Your "connman" function would look something like this:
Public Function OpenPassConnection(ByVal CorporationID As String) As String
    Dim path As String = System.IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly().Location) & "\Connection\ConnStr.txt"
    If Not System.IO.File.Exists(path) Then
        Throw New System.IO.FileNotFoundException(Nothing, path)
    End If
    
    Dim myConnectionString As String
    Using sr As System.IO.StreamReader = System.IO.File.OpenText(path)
        myConnectionString = sr.ReadLine()
    End Using
    
    If String.IsNullOrWhiteSpace(myConnectionString) Then
        Throw New InvalidOperationException("Pass4 connection string not found.")
    End If
    
    Using connection As New OleDb.OleDbConnection(myConnectionString)
        Using command As New OleDb.OleDbCommand("SELECT [ServerDBConnString] FROM [Pass4_Connections] WHERE [CorpID] = @CorpID", connection)
            command.Parameters.AddWithValue("@CorpID", CorporationID)
            
            connection.Open()
            Dim result As Object = command.ExecuteScalar()
            If result Is Nothing Then
                Throw New InvalidOperationException(String.Format("No connection string was found for corporation '{0}'", CorporationID))
            End If
            
            Return Convert.ToString(result)
        End Using
    End Using
End Function


In the OpenPassConnection function in "calculatescore", you'd remove any references to GConn. Instead, store just the connection string in a field.

Then, in any function where you need to connect to the database, you'd create a new OleDbConnection using the stored connection string:
Const SQLstr As String = "SELECT CREATE_PERCENT, impacts, propertyref, subjective, increase_improve, mechanism, [assessmentsummary].* from [questionheading] inner join [assessmentsummary] on [questionheading].[questionheadingref] = [assessmentsummary].questionheadingref where [assessmentsummary].assessmentsummaryref = @AssRef"

Using connection As New OleDb.OleDbConnection(myConnectionString)
    Using da As New OleDb.OleDbDataAdapter(SQLstr, connection)
        da.SelectCommand.Parameters.AddWithValue("@AssRef", AssRef)
        
        Dim ds As New DataSet()
        da.Fill(ds, "GettingItem")
        
        If ds.Tables.Count = 0 OrElse ds.Tables(0).Rows.Count = 0 Then
            ' TODO: This should be extracted to a common logging function - preferably using a library like NLog or Serilog.
            Dim logPath As String = System.IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly().Location) & "\PASS4_" & CorporationID & "_errorlog.txt"
            Using sw As IO.StreamWriter = IO.File.AppendText(logPath)
                sw.WriteLine(Now & " - NO RECORDS ERROR")
            End Using
        End If
    End Using
End Using



Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
   
Comments
Member 12561559 17-Nov-16 9:19am
   
The only thing that will be an issue with making the opening connection more often rather than something global, is that I will opening and closing connections - an awful lot. Is this something I just have to accept and put up with?
Richard Deeming 17-Nov-16 9:23am
   
It's not an issue in .NET - connection pooling[^] keeps a reusable pool of the underlying connections open, and reuses them in a thread-safe manner.
Member 12561559 17-Nov-16 9:30am
   
Sweet. That is great. Thanks!!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100