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 .................. ..
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 Decilaning a connection
Before establishing a session with the database, you need to declare the connection:
DIM Objconn as adodb.connection
Set objconn = new adoDb.connection
Oral
DIM OBJCONN AS New Adodb.connectionOpening 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.
Of The Table by Using The AdoDset 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.recordsetor Dim objrs as new adod.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 will not 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 RecordsetBringing 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 Automatic 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 "cmdad", "cmdsave", "cmddelete" and "cmdclear"
Step-Ii (Referencing the ADO)
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 objection 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 Strign3. Write a Procedure to Clear The Controls on The Form
Private sub cleancontrols ()
'This Procedure Will Clear The Controls on The Form.
TXTNAME = ""
TXTADDR = ""
TXTTEL = ""
End Sub
4. Call this procedure from the 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 flow_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 clicet
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) = "" "
MsgBox "please enter name", vbinformation, app.title
TRIM (txtname.setfocous' seting focus to name file)
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
Private bosavedata () as boolean
ON Error Goto Errhandlr 'Incorporation Error Handler
Bsavedata = false
STRQSTR = "" "
If SOPERATEMODE = "add" THEN
STRQSTR = "Insert Into TBLADDRBOOK (Name, Addr, Tel) Values ("
Strqstr = strqstr & "'" & txtname & "," & txtaddr & ",'" & txttel & "')"
Else if sorateemode = "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 Well 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 Succeful"
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 lstContactPrivate Sub lstContact_DoubleClick (Byval Index as integer)
Selid = Lstcontact.ItemData (LSTContact.ListIndex)
Call Dispdata 'Calling Function To Display To Contact Information
End Sub
