Communicating with the Database (Using ADO)

Communicating with the Database (Using ADO)

ADO provides developers with a powerful, logical object model for programmatically accessing, editing, and updating data from a wide variety of data sources. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint.

How to start the Communication with the Database?

To use the objects you must make a reference to them in your project. Go to Project/References and check the highest version of Microsoft ActiveX Data Objects. This could be 2.0 or 2.6.To start the communication with the database, there must be some procedure, which we need to follow. To start with, we need to declare a Connection Object.

What is a Connection?

A Connection object represents a physical connection to a database. When you open the Connection object, you attempt to connect to the database. The State property of the Connection object tells you whether you succeeded or failed. You can send SQL statements or run stored procedures by using the Execute method of the Connection object. If the command you send to the data store returns records, a Recordset object will be created automatically.

Referencing the Connection

Before you proceed, you need to refer the ADO (Microsoft Activex Data Object ver X.XX) in your project. To add the reference you need to go to the Project Menu and then select the Microsoft Activex Data Object X.XX.

Select All Code Declaring a Connection Before establishing a session with the database, you need to declare the Connection. Connection can be declared like any of the method below: Dim objConn As ADODB.Connection Set objConn = New ADODB.Connection Or Dim objConn As New ADODB.Connection Opening a Connection To open a Connection, we must make a connection string (Specifying Provider, Path of the database, User ID and Password etc) with the Database. At a minimum, you'll require to specify a Provider/connection string which indicates the type of the database, and a Data Source String, which is simply the path and file name of your database. To separate an Individual setting=value combinations of the connection string are separated with semi-colons. Dim sConnect As String    ‘ Declarign the Connection sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:/database/myaccessfile.mdb" objConn.Open sConnect    ‘ Opening the Connection Provider for MS-Access 2000 Database file is 'Microsoft.Jet.OLEDB.4.0 Provider for MS-Access 97 Database its 'Microsoft.Jet.OLEDB.3.51. Once our connection is established, we can read the records (Rows) of the table by using the ADODB Recordset object.

What is a Recordset?

As name suggests, ‘Recordset’ is nothing but a set of records from a base table or the results of an executed command. At any time, the Recordset object refers to only a single record (row) within the set as the current record. The data which the recordset will provide us, is dependent on the Query/Table/Stored Procedure we provided to open the recordet. When you use ADO, you manipulate data almost entirely using Recordset objects. All Recordset objects consist of records (rows) and fields (columns). Depending on the functionality supported by the provider, some Recordset methods or properties may not be available

Select All Code Declaring and Opening a Recordset Dim objRs As ADODB.Recordset Set objRs = New ADODB.Recordset Or Dim Objrs As New Adodb.Recordset objRs.Open "users", objConn, adOpenKeyset, adLockOptimistic, adCmdTable

What Are the Cursor?

A database element that controls record navigation, updateability of data, and the visibility of changes made to the database by other users. There four different types of cursor available in ADO.

· Dynamic cursor — allows you to view additions, changes, and deletions by other users; allows all types of movement through the Recordset

· Keyset cursor — behaves like a dynamic cursor, except that it prevents you from seeing records that other users add, and prevents access to records that other users delete. Data changes by other users will still be visible. Allows all types of navigation through the Recordset.

· Static cursor — provides a static copy of a set of records for you to use to find data or generate report. Additions, changes, or deletions by other users will not be visible.

· Forward-only cursor — allows you to only scroll forward through the Recordset. Additions, changes, or deletions by other users will not be visible.

What are the Lock types ?

If multiple users try to update the same record at the same time, an error will occur. When locking your records, you will either be optimistic (adLockOptimistic) that this error won't occur, or you will be pessimistic (adLockPessimistic) and figure this error is likely to happen. So we require implementing some locking strategy. So Lock Type is required because, it determines how to implement concurrent sessions, specially when the solution is supposed to run in a network environment. How to lock the records when the application will run on a network. In optimistic locking, other users are only locked out of the record(s) when the Update method is called - probably just a split second. In pessimistic locking, other users are locked out for the entire period that the records are being accessed and modified.

Navigating Through a Recordset ?

Recordset is a collection of one or more rows from the database and table specified by you. So you must be able to navigate (Move) through this collection of rows. The Move method moves to a record in a database specified by a row number.

· Move : The Move method moves to a record in a database specified by a row number e.g adoRS.Move 4 will move the recordset to fourth record

· MoveFirst: Moves to the first record in the Recordset

· MoveLast: Moves to the last record in the Recordset

· MovePrevious: The MovePrevious method moves to the previous record in the Recordset. The BOF property should be checked to prevent an error occurring. BOF is set to True if the current record is before the first record in the Recordset

· MoveNext: The MoveNext method moves to the next record in the Recordset. The EOF property should be checked to prevent an error occurring. EOF is set to True if the current record is after the last record in the Recordset

Bringing all together to start working

Now we will be creating a simple Database Enabled form, which will allow you ADD, Display, Edit and Delete the records. To start with, we will create a simple form, which will store the Name, Address and Telephone Number of an Employee.

Select All Code Step-I (Design) 1.    Open Visual Basic and Create a New .EXE project and save it as AddBook.Vbp 2.    When u will create a project, a form named “Form1.frm” will be added automatically in the project, Change the name of he Form as “frmMyAddrBook.frm” 3.    Place Three Label and change the caption to “Name”, “Address” and Teleephone Number” respectively 4.    Place Three TextBox on the form and Name it as “txtName”, “txtAddr” and “txtTel” 5.    Place a ListBox Control on the form and Name it as lstContact 6.    Add some Buttons for Adding, Saving, Deleting andClearing the form and name it “cmdAdd”, “cmdSave”, “cmdDelete” and “cmdClear” Step-II (Referencing the ADO) 1.    Goto Project à Reference Menu 2.    Add reference of the “Microsoft Actiovex Data Object [XX.XX] class Step-III (Implementing the Code) 1.    Double Click the form Window, it will open the Editor Window of “frmMyAddrBook” 2.    Declare some Variables in the General Declaration Section of the Code. Dim objConn As ADODB.Connection    ‘ Connection Object Dim objRs As ADODB.Recordset Dim strConnString As String    ‘ Variable to Hold the Connection String Dim strQstr As String    ‘ Variable to store Query Dim sSelID As Double Dim sOperateMode As Strign 3.    Write a procedure to clear the controls on the form Private Sub ClearControls() ‘ This procedure will clear the controls on the form.     TxtName=””     TxtAddr=””     TxtTel=”” End Sub 4.    Call this procedure from ther Clear Button Click Event Private Sub cmdClear_Click() ClearControls     ‘ it can be called like Call ClearControls     End Sub 5.    Establish Connection with The database Private Function bgetConnected()As Boolean On Error goto ConnErr GetConnectedb=False         Set objConn = New ADODB.Connection         StrConnStr= "Provider=Microsoft.Jet.OLEDB.4.0;" & _          "Data Source=” App.path & “/mydeb.mdb"         Objconn.Open StrConnStr         bGetConnected=true Exit Function ConnErr: Msgbox “Unable to Connect to the Database becaue” & Err.Description, VbInformation End Function 6.    Loading the Already Existing data to the List 7.    Now Call This procedure in the Form_Load Event so that data will remain blank Private Sub Form_Load() Call ClearContriols    ‘ Calling procedure to clear the input controls SOperateMode=”” BgetConnected    ‘ Establishing Connection with the Database End Sub 8.    Add the Code below to Add Button Click Event Private cmdAdd_Click()     Call Clearcontrols    ‘ Clear Data     Operatemode=”Add”    ‘ Seting Operation Mode End Sub 9.    Add a procedure to validate the Data, like Name and Address Must be entered before we save the Data. Private Function bValidateData() As Boolean     BValidateData=False    ‘ Default we r assuming that Data is Invalid     If (Trim(txtName)=”” Then         Msgbox”Please Enter Name”,Vbinformation,App.title         Trim(txtName.Setfocous    ‘Seting focus to Name filed Exit Function     Endif ‘’’ Place similar Validation for Address here ‘ …. …. … BValidateData=True End Function 10.    Now We need to Save the data, so we will be writing a single procedure which will save the Data (Add/Edit/Delete) Private bSaveData() As Boolean On Error goto ErrHandlr        ‘ incorporating Error Handler     BSaveData=false     StrQstr=”” If sOperateMode=”Add” Then     StrQstr=”Insert Into tblAddrBook (Name, Addr,Tel) Values(“     StrQstr=strQstr & “’” & txtName & “’,” & txtAddr & “’,’” & txtTel &”’’)” Else if sOperateMode=”Edit” Then     StrQstr=”Update tblAddrBook Set”     StrQstr=strQstr & “ Name=’” & txtName & “’,”     StrQstr=strQstr & “ Addr=’” & txtAddr & “’,”     StrQstr=strQstr & “ Tel=’” & txtTel & “’”     StrQstr=strQstr & “ Where conID=” & val(sSelID) Else If sOperateMode=”Del” Then     StrQstr=”Delete From tblAddrBook Where conID=” & Val(sSelID) Endif ‘ Now We will Use the Connection Object’s Execute Method to Run Our Query objConn.Execute strSql bSaveData=true Exit Function ErrHandlr:     Msgbox “Unable to Save The Data Because “ & Vbcrlf & Err.Description , vbinformation, app.title End Function 11.    Now We Need to Call the Function to Save The data Private cmdSave_Click() If bSaveData Then    ‘ If data has been Saved Successfully Then Give a message     Msgbox “Contact has been Added Succefully”     Call PopulateData    ‘ Refreshing the List of already Added data End Sub 12.    Now we will be deleting the data Selected by the User Private cmdDelete_Click()     If Val(sSelId)=0 Then    ‘Use has not selected any Data to delete         Msgbox “Please select a Contact to Delete” LstContact.setfocous Exit Sub     End if         If bSaveData Then             Msgbox “the Contact has been deleted” Call ClearControls    ‘ Clear the Controls Call PopulateData    ‘ repopulate The data         End if End Sub 13.    Now we need to write a procedure to populate the data into the list Box. Private Function PopulateData() Dim dctr As double Objrs.activeConnection=ObjConn Objrs.LockType=adLockOptimistic Objrs.CursorType=adOpenStatic Objrs.CursorLocation=adUseClient StrQstr=”Select * from tblAddrBook Order By Name” Objrs.open StrQstr,,,,adcmdtext If objrs.RecordCount>0 Then     For dCtr=1 to Objrs.Recordcount         LstContact.addItem Objrs!Name    ‘ Displaying Name Only ‘Populating ID in the Item Data         LstContact.ItemData(lstContact.NewIndex)=Objrs!Id    ‘         Objrs.movenext     Nect dCtr Endif If objrs.state=1 Then    ‘ If Connection is Open Objrs.close Endif End function 14.    Again we need a procedure to Display the Contact Information as the user selects a contact from the List Box Private Function DispData() Objrs.activeConnection=ObjConn Objrs.LockType=adLockOptimistic Objrs.CursorType=adOpenStatic Objrs.CursorLocation=adUseClient StrQstr=”Select * from tblAddrBook Where ID=”& Val(selID) Objrs.open StrQstr,,,,adcmdtext If objrs.RecordCount>0 Then         TxtName= Objrs!Name    ‘ Displaying Name         TxtAddr= Objrs!Name         TxtTel= Objrs!Tel Endif If objrs.state=1 Then    ‘ If Connection is Open Objrs.close Endif End Function 15.    Now We have to write some code to display the detail information in the input controls, when user double clicks a contact in the List box_ Add The Code below into the Double_Click Event of the lstContact Private Sub lstContact_DoubleClick(Byval Index as integer) SelID=lstContact.itemdata(lstContact.ListIndex) Call DispData    ‘ Calling Function to display the contact information End Sub