ADODB Issues in VB.NET with an Oracle Database

Oracle Database logo

During my recent VB6 to VB.NET Forms upgrade adventure, I had to wade through quite a bit of code that, while still functional, should be upgraded further when time permits.  Most of this code is used for data access, and being that it was written back in the VB6 / Classic ASP days, Active Data Objects (ADO) was used rather than ADO.NET, which is the newer .NET equivalent of ADO.
A common feature of ADO which was widely used in this project was the Recordset.  An ADO Recordset Object is defined by Microsoft as an object that “represents the entire set of records from a base table or the results of an executed command.  At any time, the Recordset object refers to only a single record within the set as the current record.” (Link)

One of the problems in using the recordsets object with VB.NET and Oracle is that if the recordset is not closed and garbage collected once the program is done with it, an Oracle error reporting too many open cursors (ORA-01000) is often thrown.  Many Web sites say that one possible fix to this error is to increase the number of allowed open cursors, but this solution only hides bad code.  Instead, you must make certain that the object is closed, set to Nothing, and garbage collected.

To resolve this problem, I created a module that contains methods to destroy serveral types of objects: ADODB Connections, ADODB Recordsets, Excel applications, and Scripting FileSystemObjects.

Module modDestroyObjects

Public Sub DestroyConnection(ByRef cn As ADODB.Connection)
If cn IsNot Nothing Then
If Not cn.State = 0 Then
cn.Close()
End If
cn = Nothing
CollectGarbage("ADODB.Connection")
End If
End Sub

Public Sub DestroyRecordset(ByRef rs As ADODB.Recordset)
If rs IsNot Nothing Then
If Not rs.State = 0 Then
rs.Close()
End If
rs = Nothing
CollectGarbage("ADODB.Recordset")
End If
End Sub

Public Sub DestroyExcelApp(ByRef xlApp As Microsoft.Office.Interop.Excel.Application)
If Not (xlApp Is Nothing) Then
For i As Integer = xlApp.Workbooks.Count To 1 Step -1
xlApp.Workbooks(i).Close(False)
Next i
xlApp.Quit()
xlApp = Nothing
CollectGarbage("Excel.Application")
End If
End Sub

Public Sub DestroyFileSystemObject(ByRef fso As Scripting.FileSystemObject, ByVal str As String)
If Not (fso Is Nothing) Then
If fso.FileExists(str) Then
fso.DeleteFile(str)
End If
CollectGarbage("FileSystemObject")
End If
End Sub

Private Sub CollectGarbage(ByVal strName As String)
GC.Collect()

'' Uncomment below to see a Message Box each time Garbage Collection is manually invoked.
'MsgBox("Object " & strName & " has been collected.", MsgBoxStyle.OkOnly)
End Sub

End Module

By calling the appropriate method, the object is removed from memory immediately, rather than waiting for automatic garbage collection to take place.  While this does increase system overhead somewhat, I noticed no decrease in performance.  On an older / slower computer, this increase in overhead could be noticeable.  However, this ensures that — at least until I can rewrite the code using ADO.NET — the program can run many queries without throwing the dreaded ORA-01000 error.

Check / Uncheck All Child Nodes and Uncheck Parent Node in TreeView

Visual Basic logo

As mentioned in my last post, I am working with a TreeView control that has been upgraded from VB6 to Visual Basic .NET (2012).  To programmatically cause the TreeNodes to be checked/unchecked properly, we must use the AfterCheck event.  Here is the code which is called from the AfterCheck event:

Public Sub CheckChildNodes(ByVal iNode As TreeNode)
Try
UnCheckParentNodes(iNode)
For Each sNode As TreeNode In iNode.Nodes
sNode.Checked = iNode.Checked
CheckChildNodes(sNode)
Next
Catch ex As Exception
End Try
End Sub

Public Sub UnCheckParentNodes(ByVal iNode As TreeNode)
Try
If iNode.Checked = False AndAlso iNode.Parent IsNot Nothing Then
iNode.Parent.Checked = False
UnCheckParentNodes(iNode.Parent)
End If
Catch ex As Exception
End Try
End Sub

And here is the code in the AfterCheck event that calls the above code:

Private Sub tvwDataCategory_AfterCheck(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.TreeViewEventArgs) Handles tvwDataCategory.AfterCheck
If eventArgs.Action = TreeViewAction.ByKeyboard Or eventArgs.Action = TreeViewAction.ByMouse Then
CheckChildNodes(eventArgs.Node)
End If
End Sub

Differences between the VB6 TreeNode and .NET System.Windows.Forms.TreeNode

Visual Basic logo

In working on the conversion of a VB6 Forms application to VB.NET (as mentioned in my last post), I have discovered many instances where Microsoft decided to keep the name (and sometimes even the associated syntax) of an element, while changing the functionality of the methods related to that element.
One such example is that of the TreeView and its TreeNodes.

In the VB6 application, the code associated with the NodeCheck event was written like this:

Private Sub tvwDataCategory_NodeCheck(ByVal Node As MSComctlLib.Node)
Dim n As Integer

If Left(Node.Key, cTagLength) = cDataTypeTag Then
Node.ForeColor = DefaultForeColor
If Node.Children <> 0 Then
n = Node.Child.Index
While n <> Node.Child.LastSibling.Index
If Node.Checked Then
tvwDataCategory.Nodes(n).Checked = True
Else
tvwDataCategory.Nodes(n).Checked = False
End If
n = tvwDataCategory.Nodes(n).Next.Index
Wend
If Node.Checked Then
tvwDataCategory.Nodes(n).Checked = True
Else
tvwDataCategory.Nodes(n).Checked = False
End If
End If

Else
Call FormatChecks
End If

End Sub

Even after running the VB6 code through the Visual Studio upgrade wizard, the resulting code did not work as it did before.  The NodeCheck event in VB6 has become the AfterCheck event in .NET, and the new code should read something like this:

Private Sub tvwDataCategory_AfterCheck(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.TreeViewEventArgs) Handles tvwDataCategory.AfterCheck
Dim Node As System.Windows.Forms.TreeNode = eventArgs.Node
Dim n As Integer

If VB.Left(Node.Name, cTagLength) = cDataTypeTag Then
Node.ForeColor = DefaultForeColor

Dim nNodes As TreeNodeCollection = eventArgs.Node.Nodes

For Each nNode As TreeNode In nNodes
If nNodes.Count = 0 Then
If nNode.Checked Then
tvwDataCategory.Nodes.Item(n).Checked = True
Else
tvwDataCategory.Nodes.Item(n).Checked = False
End If
End If
Next
Else
Call FormatChecks()
End If
End Sub

I am still working to verify that this will traverse the entire node and all child branches.  As the FirstSibling and LastSibling functions have been made obsolete, new code must be written to duplicate that functionality.  The adventure continues…