Wednesday, May 2, 2018

Show Database Record in MSHFlexGrid through ADODC in VB6.0

Show Database Record in MSHFlexGrid through ADODC in VB6.0

In this post, we will define process to learn about database connection and use MS_Access with MSHFlexGrid to show record through ADODC. First, drag ADODC component from Toolbox. If you want to show MS-Access record in MSHFlexGrid, create MS-Access database.

Create Database and Table:
Suppose my database name is "Record_Details.mdb". Now, we create table in this mdb file with table name "Record_Tbl" and add some Fields as shown bellow.

Go to ‘Design View’ of "Record_Tbl"and add following field.
Field Name
Data Type
Srno
Number
E_Name
Text
E_Age
Text
E_Mono
Text
After that save design view and open "Record_Tbl" table in ‘Datasheet View’ and add some records in this table.
Record_Tbl
Srno
E_Name
E_Age
E_Mono
1
ABCD
23
000000000
2
EFGH
32
111222222
3
HIJK
26
222233333
4
PQRS
18
444555555
Create Column Header with Name:
Now, create Column header name with ‘Srno’, ‘E_Name’, ‘E_Age’, ‘E_Mono’. And add column header through Form’s Load event code.

Here, change MSHFlexGrid1 Name as "DataMSFGrid".

Private Sub Form_Load()

   With DataMSFGrid

      .Clear
      .Rows = 2
      .TextMatrix(0, 0) = " Srno "
      .TextMatrix(0, 1) = " E_Name "
      .TextMatrix(0, 2) = " E_Age "
      .TextMatrix(0, 3) = " E_Mono "

   End With

End Sub

Controls Description for show record in MSHFlexGrid
Controls Description

Drag control and establish DB Connection:
After that add command button and set this Caption as ‘Show DB Data’ and Name as ‘Show_Data’. First we establish connection. You have drag ADODC control to Form1. After that write code on ‘Show_Data’ command button’s click events. 
Connection CODE:

Private Sub Show_Data_Click() 

Dim cnn As New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Record_Details.mdb;Persist Security Info=False"

End Sub

Retrieve DB Records:
After write code for connection, you have to write code for retrieve data from database to this control. You can write code just below to connection code. But, you have to define data set for it. Mainly 2 ADODB objects you have to define.

1 ADODB.Connection
2 ADODB.Recordset

 Now come to the code of showing data.

Code:

Private Sub Show_Data_Click() 

   'cnn use for connection
   Dim cnn As New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Record_Details.mdb;Persist Security Info=False"

   'rcset use for table's data
   Dim rcset As New ADODB.Recordset

   Dim i As Double
   i = 1

   'open recordset through Query
   rcset.Open "Select * from Record_Tbl;", cnn, adOpenKeyset, adLockOptimistic

   'Loop recordset process till Last data
   While Not rcset.EOF

       'Increament row for every data
       DataMSFGrid.Rows = i + 1

       'Apply condition if particular Field's data is Null or Blank.
       If Not rcset!Srno = "" Then

            DataMSFGrid.TextMatrix(i, 0) = rcset!Srno

            Else

            DataMSFGrid.TextMatrix(i, 0) = ""

       End If

       If Not rcset!E_Name = "" Then

           DataMSFGrid.TextMatrix(i, 1) = rcset!E_Name

           Else

           DataMSFGrid.TextMatrix(i, 1) = ""
 
        End If

        If Not rcset!E_Age = "" Then

           DataMSFGrid.TextMatrix(i, 2) = rcset!E_Age

           Else

           DataMSFGrid.TextMatrix(i, 2) = ""

        End If

        If Not rcset!E_Mono = "" Then

            DataMSFGrid.TextMatrix(i, 3) = rcset!E_Mono

            Else

            DataMSFGrid.TextMatrix(i, 3) = ""

        End If

       'Increament i for increament row of MSHFGrid

        i = i + 1

        'MoveNext is use for go to Next data of Table.

        rcset.MoveNext

       'DoEvents use during loop for hanging situation

        DoEvents

        'wend is use for closing Loop

   Wend

   'Close recordset

   rcset.Close

   'cnn.Close is use for close connection

    cnn.Close

End Sub

Note: DoEvents is very important to use inside loop code, because it allows your running application not hang during loop process.
There is image shows the MS-Access data.
Database Table Record show in MSHFlexGrid
Table's data
Show DB Record at Run time:
Now come to the output of run time. The same record of MS-Access table will present in running application's control. An important point is that it is bit slower in processing than Listview.
Record Output for show database record in MSHFlexGrid
data Output

Now, learning tips to show database record in MSHFlexGrid through ADODC in vb6.0 at runtime SKOTechLearn describe easily.

0 comments: