visual data tools

8/3/2019 Visual Data Tools

1/17

Visual Data Tools

Training Division, NIC, New Delhi

G 1

?Visual Data Tools

? Introduction

Visual Basic is a powerful tool to enhance and display your data when you are

working with databases. One can create basic custom database applications with just a few lines of program code, as VB implements the same databasetechnology that is included with MS access (a database engine called MS Jet).The figure below is a roadmap of data access technologies found in Visual

Basic.

The details of the above picture are explained in the slides.

A customised database application is a program that takes the fields and recordsof a database and displays them in a way that is meaningful to a specific group ofusers. For example, a public library might create a customised version of its card

catalog for a group of scientific researchers. Customised database applications

typically present a variety of commands to their users. The commands allowusers to search for, add, print, delete or modify records.

Visual Basic 6.0 is having following Visual Data Tools to access the data from various

databases like Dbase, Access, Oracle, SQL Server etc. on the form :

1) ADO Data Control2) Data Environment Designer3) Data Grid4) MSHFlexGrid control

? ADO Data Control (Used to create bound forms)

The bridge between the data providers and data consumers is through data sources

created using Microsoft ActiveX Data Objects (ADO), which is the primary method inVisual Basic to access data in any data source, both relational and non-relational. For

backward compatibility and project maintenance, Remote Data Objects (RDO) and DataAccess Objects (DAO) are still supported.


2/17

Visual Data Tools


G 2

?Difference between DAO, RDO, ADO

DAO : The data access objects(DAO) paradigm was the first object oriented interface

that allowed programmers to manipulate the Microsoft jet database engine. The jet

database engine is a technology to used to access the fields and records in MicrosoftAccess tables and other database sources. DAO is still popular and effective for single

system database applications and small-size workgroup networks.

RDO : The Remote Data Object(RDO) paradigm is an object-oriented interface to OpenDatabase Connectivity(ODBC) sources. RDO is the object modal used by most databasedevelopers who work intensively with Microsoft SQL Server, Oracle, and other large

relational database.

ADO : The ActiveX Data Objects(ADO) paradigm has been designed as successor toDAO and RDO, and it has a similar object modal. In ADO, programmable objects

represents all the local and remote data source available to your computer. Using newADO control, by binding data objects to intrinsic and ActiveX controls, by creatingDHTML application, and by using the new Data Environment Designer, can access these

data objects.

Most objects we create by using VB toolbox controls have the built-in ability to

display database information. These objects are called bound controls. An object isbound to a database when its DataSource property is set to a valid database name

and its Datafield property is set to a valid table in the database.

?To add ADO Data Control? to the form

Create a database (Employee) with a table(emp), whose structure is displayed in Fig 1with few records in MS-Access for the illustration,

Fig. 1

Now we will be using VB to create the front end interface as shown below and atthe back end MS Access Database is being used.

?By default VB toolbox contains Data Control which can be used to connect databases like Dbase,

Access, Excel etc. whereas ADO Data Control is used to connect database based on OLE DB technology

which includes SQL Server and databases accessed via ODBC in addition to the previous databases.


3/17

Visual Data Tools


G 3

Fig. 2

We will create the following form given in Fig. 2, first and then add the other

command buttons for the user. Let us see how it is done:

? In Visual Basic, open a new Standard Exe Project.? ClickProject>>Components.? Select Microsoft ADO Data Control 6.0 (OLE DB)1.? ClickOK.? Following data object picture will be displayed on the tool box

? In the form that appears, put the labels, text boxes and the ADOdata objectfrom the VB Toolbox, as shown in Fig. 2

1If erroe message of File in the Path not found or File not registerred then copy the file wrt that object

in the system folder and then register it with the command >regscvr32


4/17

Visual Data Tools


G 4

? As shown in the form above, set the properties of these objects as follows:

Object Property Value Remark

Name Adodc1

Caption Employee Details

ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Persist Security

Info=False;DataSource=C:\employee.mdb

path of databaseto be accessed

See Part-I of

Annexure I

ADO DataControl

RecordSource emp Name of table inthe database beingaccessed i.e. source

of recordsetSee Part-II of

Annexure I

Name txtempcodeDataSource Adodc1 Name of the Data

Control through

which database isbeing accessed

Datafield empcode

Text1

Text (Empty)

Name txtempname

DataSource Adodc1 -do-

Datafield empname

Text2

Text (Empty)

Name txtbasicDataSource Adodc1 -do-

Datafield basic

Text3

Text (Empty)

Name txtdesig


Datafield desig

Text4

Text (Empty)

Name txtdepartment


Datafield department

Text5

Text (Empty)

Note: It may be noted that the five text boxes have the same DataSource

Property but different field settings for the Datafield property.


5/17

Visual Data Tools


G 5

The five labels inserted in the form, should have the Caption property set to,Employee Code, Employee Name, Basic Pay, Designation, Department, respectively, as

shown in the Fig. 2.

? Now you may save and run the form EmployeeDetails. The arrows in the data

object allow you to navigate through the various records in the emp, as shown inFig. 3.

Fig. 3

You may recall that we have used the RecordsetType to identify the database

information as a table. In VB, a Recordset is an object representing the part of thedatabase you are working with in the program. The Recordset object includesproperties and methods that let you search for, sort, add and delete the records.We will now put the Find, Add , Delete and Quit buttons in the

EmployeeDetails form to achieve the following screen.

Fig. 4


6/17

Visual Data Tools


G 6

? To put the buttons of Find, Add, Delete and Quit, drag the button objects fromthe tool box to the Employee Details form.

? Giving the names as cmdFind, cmdAdd, cmdDelete and cmdQuit ,respectively tothe above four buttons, you can type the codes for their respective event

procedures as follows:

Buttons Name

Property

Code

Find cmdFind Private Sub cmdFind_Click()prompt$ = "Enter the Employee Code"searchstr$ = InputBox(prompt$, "Search Box")Adodc1.Recordset.MoveFirstAdodc1.Recordset.Find "empcode = '" & searchstr$ & "'"If Adodc1.Recordset.RecordCount = 0 ThenAdodc1.Recordset.MoveFirst

End If

End Sub

Add cmdAddPrivate Sub cmdAdd_Click()

pr$ = "Enter the new record,and then click the left arrowbutton"reply = MsgBox(pr$, vbOKCancel, "add Record")If reply = vbOK Then

txtempname.SetFocus

Adodc1.Recordset.AddNewEnd if

End Sub

Delete cmdDelete Private Sub cmdDelete_Click()prompt$ = "Do you really want to delete this Record?"reply= MsgBox(prompt$, vbOKCancel, "Delete Record")If reply =vbOK Then

Adodc1.Recordset.DeleteAdodc1.Recordset.MoveNextEnd If

End Sub

Quit cmdQuit Private Sub cmdQuit_Click()End

End Sub

? You may now run your program and try finding, adding and deleting a record.


7/17

Visual Data Tools


G 7

? Data Environment Designer

This feature allows you to quickly and easily create, design-time and run-time ADOobjects. That is rather than having to create ADO objects, instantiate them, set their

properties in code.

?Using Data Environment designer you can :

1. Create connection objects.2. Create command objects based on stored procedures, tables, views, synonyms and

SQL statements3. Create hierarchy of commands based on grouping of command objects, or by relating

one or more command objects together.4. Write and run code for Connection and RecordSet objects.5. Drag fields within a Command object from the Data Environment Designer onto a

form or the Data Report Designer.

?Main features of Data Environment designer includes :

1. Data Environments are reusable : Once the object is set, they can be used over andover within the same project and they can be shared among the different projects.

2. Data Environments are scalable : It creates objects that are available both on clientside and the middle tier.

?How to work with Data Environment Designer ?

For the illustration same MS-Access database created in ADO Data Control Object

session will be in use. In addition to the emp table, create one more table called trainingwhich will contain the training details of an employee.Structure is given as below :

Add Data Environment Designer to the project

? Open the Standard Exe project.? ClickProject>>Add Data Environment. Following screen will appear :


8/17

Visual Data Tools


G 8

? Right Click on Connection1 as indicated by arrow in the above figure.? Click on Prope rties. Following screen will appear to select the OLE DB Provider :

? ClickNext.? Select Database name from the screen shown below :


9/17

Visual Data Tools


G 9

? ClickOK.? Right Click on Connection1 again.? Click on Add Command. Following screen will appear:

? Right Click on Command1 again.? ClickProperties.? Select the Object Type and Name in the following screen :


10/17

Visual Data Tools


G 10

? ClickOK.? Drag Command1 on the form. Automatically bound fields will be created on the

form.? Later on buttons can be created for navigation, addition, deletion, search etc.

Note : If data in the table is to be edited or added, set the LockType in the Advanced tabas Optimistic.

Few code samples are given below :

1. For Moving to next record :DataEnvironment2.rsCommand1.MoveNext

2. For Moving to previous record :

DataEnvironment2.rsCommand1.MovePrevious

3. For Moving to first record :DataEnvironment2.rsCommand1.Movefirst

4. For Moving to last record :DataEnvironment2.rsCommand1.Movelast

5. For Addition of Record:DataEnvironment2.rsCommand1.AddNew


11/17

Visual Data Tools


G 11

? DataGrid Control

The DataGrid control is a spreadsheet-like bound control that displays a series of rowsand columns representing records and fields from a Recordset object. It is used to create

an application that allows the end user to read and write to most databases.

At the design time

If DataGrid control is configured at design time then control is automatically filled andits column headers are automatically set from the data source's recordset. The grid's

columns can be edited; delete, rearrange, add column headers to, or adjust any column'swidth.

At run timeThe DataSource can be programmatically switched to view a different table, or you can

modify the query of the current database to return a different set of records.

Usage

? View and edit data on a remote or local database.? Used in conjunction with another data-bound control, such as the DataList control,

use the DataGrid control to display records from one table that are linked through acommon field to another table displayed by the second data-bound control.


12/17

Visual Data Tools


G 12

?How to Create Data Grid

DataEnvironment created in earlier is used here also.

? Create a new form and select Project >>Components. Check the Microsoft

DataGrid Control 6.0 as shown below.

? Following data object picture will be displayed in the tool box.? Using this toolbox object, create a data grid on the form.? Set the datasource property of the grid control to the DE (i.e. dataenvironment1) and

set the DataMemberproperty to the appropriate command object (i.e. command1) asshown below:


13/17

Visual Data Tools


G 13

Now right click on the DataGrid and select "Retrieve Fields" as shown below. Thiswill fill in the grid columns

The DataGrid's Menu

The result of this is that every field that is in the recordset (from the command object)

shows up in the grid as shown below.

when you run this form, the output will be appeared as

If you would like to hide some of the fields, for instance "empcode", use the grid's

property window to set the visible property to False. From design time, right click andselect the Properties menu item to bring up the Properties window for the Grid Control

(see below) and uncheck the Visible property ofLayoutoption to hide the column.


14/17

Visual Data Tools


G 14

?Working with Parent-child relationship in Data Grid

In Data Environment , Create Command1 and Command2 having parent-child

relationship as shown below.

Drag Command1 over a form and Delete MSHFlexGrid created from form.

Create DataGrid over the same form and set its properties.DataSource DataEnvironment1

DataMember Command1

AllowAddNew TrueClick right mouse button and select Retrieve fields


15/17

Visual Data Tools


G 15

The form will be appear as :

Write the following code for the buttons and data grid:

Private Sub Command1_Click()DataEnvironment1.rsCommand1.MoveNextEnd Sub

Private Sub Command2_Click()

DataEnvironment1.rsCommand1.MovePrevious

End Sub

Private Sub Command3_Click()DataEnvironment1.rsCommand1.MoveFirst

End Sub


DataEnvironment1.rsCommand1.MoveLastEnd Sub


DataEnvironment1.rsCommand1.AddNewEnd Sub

Private Sub Command6_Click()DataEnvironment1.rsCommand1.UpdateEnd Sub


16/17

Visual Data Tools


G 16

Private Sub Command7_Click()End

End Sub

Private Sub DataGrid1_GotFocus()

DataEnvironment1.rsCommand1.UpdateDataEnvironment1.rsCommand1.MovePrevious

DataEnvironment1.rsCommand1.MoveNextEnd Sub

? MSHFlexGrid Control

It is similar to the Microsoft Data Bound grid (DataGrid) control, but with the distinct

difference that the Hierarchical FlexGrid control does not allow the user to edit databound to, or contained within, it. This control, therefore, allows you to display data to the

user while ensuring that the original data remains secure and unchanged. It is alsopossible, however, to add cell-editing features to your Hierarchical FlexGrid control bycombining it with a text box.

The Hierarchical FlexGrid Control is a flexible read-only display mechanism for OLEDB data. Using the Data Environment as created already, Hierarchical FlexGrid Control

is used to display the related sets of data in a single grid.

Add a child command to command1created above by :

?Clicking right mouse button on Command1.?Select Add child command option.

?Right click child command Command2 , following screen will appear :


17/17

Visual Data Tools


G 17

? In General tab, select the object type table and object name as training, which is thesecond table.

?Getting Started with the MSHFlexGrid Control

? In Visual Basic, open a Project.? ClickProject>>Components.? Select Microsoft Hierarchial FlexGrid Control 6.0 (OLE DB).? ClickOK.? Following data object picture will be displayed on the tool box

? In the form that appear, put MSHFlexGrid Control and set theproperties as given inthe following table

Property Value

Data Source DataEnvironment2 (name of the Data Environment)

Data Member Command1 ( name of the top-level command object to be displayedin the Grid)

? To make the control occupy the whole of the form area, write the following code inthe Resize event of the form

Private Sub Form_Resize()MSHFlexGrid1.Move 0, 0, Me.ScaleWidth, Me.ScaleHeight

End Sub

?

Run the project . Results will be displayed as shown below :

visual data tools

Documents