DATA CONNECTIVITY IN VB

VISUAL BASIC : VISUAL BASIC INTRODUCTION TO DATA CONNECTIVITY

Presented by R.SHANTHIVICTORIA : Presented by R.SHANTHIVICTORIA

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

Introduction to Data Connectivity : 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 : Simple database

FRONT END AND BACK END APPROACH : 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…) : FRONT END AND BACK END APPROACH(Continued…) Connectivity Bridge Report Database Records

Data Connection Approaches : Data Connection Approaches Data Control based non-programming associated with form based controls (DataGrid, DataList etc) Limited control over data Programming based uses Connection object (ADO) allows closer data control

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

DAO : 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 : 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.

Slide 12 : 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

Slide 13 : 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 : 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) : 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) : Database Engine (continued) modify any and all information within that database Add and delete all information within that database

How Data Control works? : 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.

Slide 18 : 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 database based 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 : Setting The Properties For Data Control

Setting The Properties For Data Control : Setting The Properties For Data Control

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

DAO Data Control Recordset Methods (continued) : DAO Data Control Recordset Methods (continued) Edit Places the current record in the Recordset into edit mode. MoveFirst Moves the current record pointer to the first record in the Recordset. MoveLast Moves the current record pointer to the last record in the Recordset. MoveNext Moves the current record pointer to the next record in the Recordset.

DAO Data Control Recordset Methods (continued) : DAO Data Control Recordset Methods (continued) MovePrevious 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 : ADO ADO Means active X Data Control. ADO control works along with OLEDB. OLEDB provides the rights such as accessing, manipulating datas in a database.

Microsoft Components : 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 & nonSQL databases

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

Main Objects : 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 : Key parts of ADO programming Connection Command Parameter Recordset Field Error Property Collection Event

ADO Objects summary : ADO Objects summary

ADO COLLECTIONS SUMMARY : ADO COLLECTIONS SUMMARY

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

Creating DSN : 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 : 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) : 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.

Slide 35 :

Select Microsoft Access Driver (*.mdb) from the list and click the Finish button : 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. : 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 VB6 or Visual Studio 6, the BIBLIO.MDB sample database should reside in the folder C:\Program Files\Microsoft Visual Studio\VB98. Navigate to that folder, select BIBLIO.MDB from the file list, and click OK. : Click the Select button. The Select Database dialog box appears. On a default installation of VB6 or Visual Studio 6, the BIBLIO.MDB sample database should reside in the folder C:\Program Files\Microsoft Visual Studio\VB98. 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 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. : 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) : ODBC(Continued) Close the ODBC data source Administrator & the control panel. Now you get into the Visual Basic.

Example : 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) : 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) : 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) : Example-ADO(continued) Then give Apply and close to disappear the components wizard.

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

Example-ADO(continued) : 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) : 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) : Example-ADO(continued) A dialog box will be opened with the name property pages.

Example-ADO(continued) : 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) : 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) : Example-ADO(continued) Now the application is connected with MS-Access database. This an be checked by using Text boxes.

Example-ADO(continued) : Example-ADO(continued)

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

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

EXAMPLE using DAO : EXAMPLE using DAO

Importance of Text box : 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) : 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) : 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 : 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) : Using the DAO(continued) Select the field name from “Data Field” property. Run the program.