VISUAL BASIC INTRODUCTION TO DATA CONNECTIVITY Presented by

VISUAL BASIC INTRODUCTION TO DATA CONNECTIVITY

Presented by

Contents Introduction to Data connectivity Ø Different Database Approach DAO Ø ADO Ø Ø Simple Connectivity program using Data Control

Introduction to Data Connectivity What is Database Connection? A database connection is a facility in computer science that allows client software to communicate with database server software, whether on the same machine or not. A connection is required to send commands and receive answers.

Simple database Field ID No Name Date of Birth Height Weight 1 Bob Jones 01/04/58 72 170 2 Mary Rodgers 11/22/61 65 125 3 Sue Williams 06/11/57 68 130 Table Record

FRONT END AND BACK END APPROACH Ø Front End: The place to access the data or the place from which the data can be accessed through connectivity. Ø Back-End: Place where the datas can be maintained (or) stored. Ex : Ms-Access

FRONT END AND BACK END APPROACH(Continued…) Connectivity Bridge Back-End Application Database Records Front-End Application Report

Data Connection Approaches Ø Data Control based non-programming associated with form based controls (Data. Grid, Data. List etc) Limited control over data Ø Programming based uses Connection object (ADO) allows closer data control

Different Database Connectivity Approaches. Two Data Object Library approaches Ø DAO (Data Access Objects) Closely linked to MS Access Original access strategy (up to VB 6) Ø ADO (Active. X Data Objects) Newer approach More generic Same approach for ASP (VB Script active server pages)

DAO Ø Once the data structure is developed, one can develop the user interface. Ø With VB the user can access the data through the data control. Ø It connect you to the database and specific data set. Ø Once the data connection is made you can bind other controls to the data control.

Features Ø Establish the connection with Database. Ø Returns set of records fronm database. Ø Enables the user to move from record to record. Ø Enables the user to display and manipulate data from the records in bound controls.

Ø A Visual Basic application cannot directly interact with a database Ø There are two intermediate components between the application and the database data control database engine

Data control It is a Visual Basic object It connects the application to the database via the database engine. It is the conduit between the application and the engine, passing information back and forth between the two

Database engine I It is the heart of a Visual Basic database management system It is the actual software that does the management. Having this engine saves programmers a lot of work. The database engine native to Visual Basic is known as the Jet engine.

Database Engine (continued) It is the same engine used by Microsoft Access for database management. Hence, it is primarily used to work with Access databases, but it can also work with others. It requires less code to connect to an existing database. View all information within that database

Database Engine (continued) modify and all information within that database Add and delete all information within that database

How Data Control works? Two properties need to set during run time or design time. Database name-This specifies the name of the database that must be opened. Record Source- This specifies the name of the table of the database from which the data has to be extracted.

After setting this properties Vb connects to the database specified and returns set of records from the table in the form of recordset. Record Set: It is an object that points to the data in the databased on record source property of data control. The record source property can be the name of the table and database selects fields from one or more tables.

Setting The Properties For Data Control PROPERTIES DESCRIPTION BOF Returns True when the current record is Positioned before any data. EOF Returns True when the current record is positioned past any data. Caption Phrase displayed on the data control. Connect Type of database. Default is Microsoft Access (or Jet).

Setting The Properties For Data Control PROPERTIES DESCRIPTION Database Name Returns or sets the name of the source database for the data control. Must be a fully qualified path and file name. Record set Type Indicates type of Recordset you want data control to create. 1. Table 2. Dynaset 3. Snapshot. Record Source Determines the table (or virtual table) the data control is attached to. Bound Controls To view the information, we use data bound controls that are special controls with properties established by database fields. They are Label, Text box, Check box, Image, OLE, Listbox, Picture, Combobox.

DAO Data Control Recordset Methods Important Recordset methods are: Add. New -Adds a new record to the Recordset. All fields are set to null and this record becomes the current record. Cancel. Update -Used to cancel any pending updates (either with Edit or Add. New method) Close -Closes a Recordset. Delete -The current record is deleted from the Recordset.

DAO Data Control Recordset Methods (continued) Edit Places the current record in the Recordset into edit mode. Move. First. Moves the current record pointer to the first record in the Recordset. Move. Last Moves the current record pointer to the last record in the Recordset. Move. Next Moves the current record pointer to the next record in the Recordset.

DAO Data Control Recordset Methods (continued) Move. Previous Moves the current record pointer to the previous record in the Recordset. Requery Updates the data in a Recordset object by re-executing the query on which the object is based. Update Saves the current contents of all data bound controls.

ADO Means ACTIVE X DATA CONTROL. ADO control works along with OLEDB provides the rights such as accessing, manipulating datas in a database.

Microsoft Components Jet Database Engine: VB’s database engine for MS Access & ISAM (Indexed Sequential Access Method) DBMSs ODBC (Open Database Connectivity): Interface for SQL relational databases RDO (Remote Data Objects): Original component to access databases across a network OLE DB (Object Linking & Embedding Databases): newer generic interface accessing SQL & non. SQL databases

ADO VISUAL BASIC 6. 0 APPLICATION ADO OLE DB ODBC SQL DATA e. g. MS Access (JET) SQL Server, ORACLE NON SQL DATA Text Files, Excel etc OTHER LEGACY SYSTEMS

Main Objects ØThe ADO object model defines a collection of programmable objects that can be used by any of the Microsoft Visual languages

Key parts of ADO programming Connection Field Command Error Parameter Property Recordset Collection Event

ADO Objects summary ADO OBJECTS DESCRIPTION 1. Connection Provides connection between the application & the Database. 2. Command SQL statements to operate with datas 3. Parameter Arguments of an SQL statement 4. Recordset Provides accessing & manipulating records on the Database. 5. Field Column of a record set in a database table. 6. Error Produces error messages when error in connection. 7. Property Characteristics of an ADO object.

ADO COLLECTIONS SUMMARY ADO COLLECTIONS 1. Errors 2. Parameters 3. Fields 4. Properties DESCRIPTION When connection failure occurs. Argument included with command object. Fields Present in a Record Set. Connections , Command, Record set Field object properties.

HOW TO USE ADO CONTROL? How to give commands to that? Lets see the Answer for these question.

Creating DSN Before using this control you have to create user DSN, for that you can use control panel or VB to create user DSN. Via Windows Control Panel, double-click on Administrative Tools, then Data Sources (ODBC).

ODBC Double click this ODBC. It will display ODBC data source Administrator. The ODBC Data Source Administrator screen is displayed, as shown below. Click on the User DSN tab

ODBC(Continued) Select the user DSN. Now select ADD option. Now Create New Data Source window displayed. It is used to list all the ODBC drivers for the connection. Click the Add button. The Create New Data Source dialog box will appear.

Select Microsoft Access Driver (*. mdb) from the list and click the Finish button

The ODBC Microsoft Access Setup dialog box will appear. For Data Source Name, type Biblio. If desired, you can type an entry for Description, but this is not required.

Click the Select button. The Select Database dialog box appears. On a default installation of VB 6 or Visual Studio 6, the BIBLIO. MDB sample database should reside in the folder C: Program FilesMicrosoft Visual StudioVB 98. Navigate to that folder, select BIBLIO. MDB from the file list, and click OK.

When you are returned to the ODBC Microsoft Access Setup screen, the database you selected should be reflected as shown below. Click OK to dismiss this screen.

When you are returned to the ODBC Data Source Administrator screen, the new DSN should appear as shown below. Click OK to dismiss this screen.

ODBC(Continued) Close the ODBC data source Administrator & the control panel. Now you get into the Visual Basic.

Example Open Microsoft Visual Basic from Microsoft Visual Basic. Open the Standard Exe Template. Now you cant see the ADO control button on the toolbox. So you have to take it from other components supported by Visual Basic. To get components right click on the Toolbox, select components.

Example-ADO(continued) Now you can see a dialog box having three tab, Namely Ø Controls Ø Designers Ø Insertable Objects Now you select controls from components dialog box. You just scroll down the controls listed there.

Example-ADO(continued) After scrolling some objects, you can see “Microsoft ADO data control 6. 0(OLEDB) Now you click on the check box present near by the control name.

Example-ADO(continued) Then give Apply and close to disappear the components wizard.

Example-ADO(continued) Now you can see a newly inserted control, just like a DAO control present in the toolbox.

Example-ADO(continued) Place the ADO control on the form. Now you can see ADO control is present in the form with navigation buttons.

Example-ADO(continued) After placing ADO control , get into the properties window of the ADO. Change name and caption properties of ADO. In properties window , select connection string property. Click on the button present in the connection string.

Example-ADO(continued) A dialog box will be opened with the name property pages.

Example-ADO(continued) In the property pages select “Use ODBC Data Source Name” Now click on the combo box present there. Then select a DSN created by you through control panel then give OK.

Example-ADO(continued) Now select Record source property of the ADO control , then select 2 -adcmd Table option from command type and select a table from Table or Stored Prog option and give OK.

Example-ADO(continued) Now the application is connected with MSAccess database. This an be checked by using Text boxes.

Example-ADO(continued)

Example-ADO(continued) Select a text box and change the properties as given below. Data Source-Adodc 1 Ø Datafield- Fieldname Ø Now run the program. Now the application gets connected to the specified Database.

Difference between DAO & ADO DAO ADO You cant access any You can access ODBC Enterprise controls like connectivity and SQL DBList, DBComb, ODBC structure. connectivity and SQL structures. Applicable for remote Applicable only for MSclient side data. Access.

EXAMPLE using DAO

Importance of Text box It is used to view the records. With the help of text box the user can manipulate the datas of the selected table. Set the common bound properties: Ø Select Text box control from toolbox. Ø Place the textbox on the form Ø Bring up the properties window. Ø Click on “Data Source” property. Ø Select the name of the data control from the dropdown list.

Set the common bound properties: (continued) Now the data field property of the textbox will display the field from the record set return by the datacontrol. Click on the data field and select the required field from the drop down list. This text box is now bound to the field selected. Run the project.

Set the common bound properties: (continued) Now text box will display current record of the record set. Once the navigation button is cllicked the user can mmove from one record to other record. It will display the field name selected column of the current record.

Using the DAO Procedure: Ø Place the data control on your form. Ø Select the database from “Database name” property. Ø Select the Record source from “Record source” property. Ø Place the text box on your form. Ø Select the source from “Data source ” property.

Using the DAO(continued) ØSelect the field name from “Data Field” property. ØRun the program.