Cool Features of Visual Basic 6.0 Objects!

In this article, we will see some of the basic but yet powerful features of Visual Basic 6.0 Objects which when applied in right place can improve the application performance to a greater extent.

1. ADODB.Recordset Object

 

This section assumes a basic working knowledge in developing database applications using ActiveX Data Objects.

 

ADODB Recordset object has an important property known as “Filter”. It’s been neglected by some of the developers treating it as less important one while developing applications.

 

Let us see the importance of this property with a simple code snippet,

 

Sub RetrieveDeptDetails()


cn.Open "specify connection string to connect to appropriate database"

cn.CursorLocation = adUseClient

 

'open emp_mstr recordset and fetch emp_no, emp_name field values

'assume table will have below values


'emp_no     emp_name

'-----------------------------
'100        dxyz1

'200        dxyz2

'300        dxyz3

rsEmp.Open "select emp_no, emp_name from emp_mstr", cn, adOpenStatic

 

'open dept master recordset and fetch emp_no, dept_no & dept_name field values

'assume table will have below values


'emp_no     dept_no     dept_name

'-------------------------------------------
'100        D100           d1xyz1

'100        D101           d2xyz1

'100        D102           d3xyz1

'200        D200           d1xyz2

'200        D201           d2xyz2

'300        D300           d1xyz3

'300        D301           d2xyz3

'300        D302           d3xyz3

'300        D303           d4xyz3

 

Conventional Looping Approach #1


 

If rsEmp.RecordCount > 0 Then

    rsEmp.MoveFirst

    While Not rsEmp.EOF

        'Assume you want to fetch dept details from dept_mstr based on the

        'Emp_no fetched from rsEmp recordset, the normal way is to

        'open rsDept recordset as below,

        rsDept.Open "select emp_no,dept_no,dept_name from dept_mstr where
        emp_no =" _

            & rsEmp.Fields("emp_no"), cn, adOpenStatic

        If rsDept.RecordCount > 0 Then

            'process dept details in whatever way you want

            MsgBox rsDept.Fields("dept_no")

            MsgBox rsDept.Fields("dept_name")

        End If

        'close the recordset if its open

        If rsDept.State = adStateOpen Then rsDept.Close

        rsEmp.MoveNext

    Wend

End If

 

The above code snippet is one way of filtering the recordset values based on some pre-defined values(Emp_no in this case).

 

The above logic can be re-written using Filter property and it will definitely makes the code simple and reduces the overhead of the application.

 

Approach #2 using Filter Property


 

'instead of opening rsDept everytime inside the loop, open it outside the while loop

rsDept.Open "select emp_no,dept_no,dept_name from dept_mstr ", cn, adOpenStatic


If rsEmp.RecordCount > 0 Then

    rsEmp.MoveFirst

    While Not rsEmp.EOF

        'apply the filter condition here for rsDept recordset,

        'emp_no is the field selected from rsDept recordset

        'and we are filtering the recordset based on rsemp.emp_no field value

        'incase if the rsEmp.Fields("emp_no") is 300

        'initial value of rsDept.recordcount before applying the filter will be 9

        'according to above sample values

        rsDept.Filter = " emp_no =" & rsEmp.Fields("emp_no")

        'after applying the filter value i.e, rsDept.Filter = " emp_no =300"

        'rsdept.recordcount will become 4

        If rsDept.RecordCount > 0 Then

            'process dept details in whatever way you want

            MsgBox rsDept.Fields("dept_no")

            MsgBox rsDept.Fields("dept_name")

        End If

        'refresh the filter to bring to initial state i.e, make the coount as 9

        rsDept.Filter = ""

        rsEmp.MoveNext

    Wend

End If


End Sub

 

 

The major disadvantage of using above Approach #1 is,

 

Incase if the rsDept recordset is going to fetch lot of values in real-time environment based on the EmpNo parameter value, opening the Recordset everytime and closing it inside the While Loop will definitely bring down the performance of the Application. To avoid this Approach #2 can be very well used which will make the developer as well as the application life easy.

2. Scripting.Dictionary Object:

 

Dictionary Object is used to store information in name/value pairs (referred to as key and item). We are going to see in a comparative basis “How Dictionary Object is better than Collection Object”. Collection Object is also used to store information in name/value pairs. Collection/Dictionary Objects are used whenever there’s a need for dynamic array handling. But Dictionary Object provides extra methods & properties which makes it more powerful compared to Collection Object.

 

Let us see the importance of Dictionary Object with an below example,

 

Dim itm

Dim objCol As Collection

Set objCol = New Collection

 

'Add Method takes parameter Item, key, before, after

'Count Property returns Collection Count

'Item(index) - retrieve value based on index/key

'Remove(index) - remove value from object based on index/key

 

'Let's add some values to this object

'and associate keys to each values

 

objCol.Add "100", "a"

objCol.Add "200", "b"

objCol.Add "300", "c"

objCol.Add "400", "d"

objCol.Add "500", "e"

objCol.Add "600", "f"

objCol.Add "700", "g"

 

'let's assume you want to find a value 700 inside the collection

'we will be writing a loop to find that value whether it exists in the collection

 

For Each itm In objCol

    If itm = "700" Then

        MsgBox "Found 700"

    End If

Next

 

To find an Item 700, the above For loop has to search the collection object 7 times assume your collection object holds a count of 1000 records and you want to find a value which is at the end position ie., 1000th position, your looping logic might induce a delay in application performance.

 

Also another disadvantage is that we don’t have a way list all the possible Key Values added to the Collection Object.

 

To avoid all these problems, we can use Scripting.Dictionary Object,

 

Collection Object provides only 4 Methods/Properties,

 

  • Add
  • Item
  • Remove
  • Count

 

But Scripting.Dictionary Object provides a lot more (please see below the properties & methods provided by Dictionary Object) which it makes it more powerful compared to Collection,

 

  • Add
  • CompareMode
  • Count
  • Exists
  • Item
  • Items
  • Key
  • Keys
  • Remove
  • RemoveAll

 

Let us take the same sample values, which we have used for building the Collection Object,

 

Dim itm

Dim objDict As Scripting.Dictionary

Set objDict = New Scripting.Dictionary

 

 'Let's add some values to this object

'and associate keys to each values

 

'in Collection Object add method takes parameter item, key

'but here Add Method takes parameter key, item

objDict.Add "100", "a"

objDict.Add "200", "b"

objDict.Add "300", "c"

objDict.Add "400", "d"

objDict.Add "500", "e"

objDict.Add "600", "f"

objDict.Add "700", "g"

 

'let's assume you want to find a key 700 inside the dictionary object

'you can use a exists method instead of looping the Dictionary Object on the

'whole to find a single element

'Exists Method returns TRUE if match is made , else returns FALSE

MsgBox objDict.Exists("700")

 

We will also be able to list all the KEYS associated with each values in the Dictionary Object using Keys Method of the Dictionary Object.

 

Please note that inorder to use this Dictionary Object, we need to create Project -> Reference to Microsoft Scripting Runtime Library.

 

References:

 

Please refer to MSDN for a detailed description about the various properties and methods of Collection & Dictionary Objects.

By sundar k   Popularity  (8509 Views)