ASP Shopping CartForum & BBS
  - all for $20 from CodeToad Plus!
  Home || ASP | ASP.Net | C++/C# | DHTML | HTML | Java | Javascript | Perl | VB | XML || CodeToad Plus! || Forums || RAM 
Search Site:

Home » Visual Basic » Article

Communicating with the Database (Using ADO)

Article by: Chaudhary Pradeep K. Roy (9/29/2004)
Bookmark us now! Add to Favourites
Email a friend!Tell a friend
Summary: Using ADO to get connected with the database.
Viewed: 212320 times Rating (27 votes): 
 4.2 out of 5
 Rate this Article  Read Comments  Post Comments

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

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

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

CodeToad Experts

Can't find the answer?
Our Site experts are answering questions for free in the CodeToad forums
Rate this article:     Poor Excellent
View highlighted Comments
User Comments on 'Communicating with the Database (Using ADO)'
Generate License Keys (such as CD keys on Microsoft software)
by Brian Gillham
This class allows you to generate license keys, such as the CD keys on the case of most Microsoft software
Communicating with the Database (Using ADO)
by Chaudhary Pradeep K. Roy
Using ADO to get connected with the database.
Visual Basic Read and Modify the Registry
by Kenneth Ives
Perform the four basic functions to the Windows registry. Add, change, delete, and query. Allows you to to read registry values, and modify both keys and values.
Generate an Array of Unique Random Numbers
by Brian Gillham
This function will generate an ARRAY of TRULY random numbers.
Beginning Resource Files in Visual Basic
by Kenneth Ives
Have you ever wanted to use graphics, such as icons, bitmaps, cursors, and AVI files? How about sound or even message box text? This can be an enormous amount of overhead. These are all examples of more files to keep track of when you distribute an application, DLL, or OCX. Lets find out how resource files can help....
Visual Basic Compact/Repair Access Database Utility
by Kenneth Ives
This utility will compact and repair the access database you select. It is actually more convenient to use this utility than do the same task with Access, because you don't have to save the database with a different name and then delete the old database when you are done: here, a temporary copy of the database is automatically created and deleted.
Creating a watermark in Excel with VBA
by Kenneth Ives
Adding a watermark to an Excel spreadsheet using VBA.
Change Cursor to Hour Glass
by Brian Gillham
A simple script to change the cursor to an hour glass.
Generate your own Random Numbers
by Thaha Hussain
This simple program generates random numbers using the basic mathematical methods.
Analog Clock
by Thaha Hussain
This program demonstrates Thaha Hussain's Clock Work Formula to paint an Analogue Clock.

Recent Forum Threads
• Game:Colonization based with HTML5 Canvas and JavaScript
• Pointwise.GridGen.v15.18
• Global.Mapper.v15.2.3.b060614
• Geometric_Glovius_Pro_v3.6.1
• VERO.SurfCAM.v2014
• Schlumberger.Petrel.V2013.2
• Petrel.V2013.2
• Altair.HyperWorks.v12
• VoluMill.v6.1

Recent Articles
ASP GetTempName
Decode and Encode UTF-8
ASP GetFile
ASP FolderExists
ASP FileExists
ASP OpenTextFile
ASP FilesystemObject
ASP CreateFolder
ASP CreateTextFile
Javascript Get Selected Text

© Copyright 2001-2014