Programming language Visual Basic 6 (Chapter 16 - Programming with ADO (Part I))

ADO Data Control

Visual Basic 6 gives us the choice of technique when programming with database, or use  DAO  as in the previous two articles, or are using  ADO (ActiveX Data Objects) .

The main difference between ADO and the ADO DAO allows us to work with any type of data sources (data sources), not necessarily the Access database or ODBC. Data sources can be a list of email addresses, or a text string, where each row is a record consisting of fields separated by commas ( comma separated values ).

If the DAO is used directly in the name of the Database MSAccess ADO us  connected (connect)  a database through a Connection by specifying a  Connection String . In the Connection String Database Provider  (such as Jet, ISAM, Oracle, SQLServer, etc. ..), Database name,  username / password logon to a database. etc. Then we can  retrieve (extract)  the recordsets and update the records using the  SQL command  used on the tables or  stored procedures  inside the database.



Normally, when I booted a new VB6 project, Control  Data ADO  is not available in the IDE. Wanted it, use the Command Menu  Project | Components ... , then select  Microsoft ADO Data Control 6.0 (OLEDB) from Interface Components, as follows:

Begin a new VB6 project, giving it the name  ADODataControl  by clicking project name in Project Explorer right then edit the Name property in the Properties Window. This form of the name of frmADO , and review questions  DataControl Demo ADO  in its Caption.

DoubleClick read Icon cua trong ADO Data Control Toolbox. ADO Data Control Mot has  Adodc1  to train hyena ra Form. Muon nam and node to dưới Form, Nhu mot giong StatusBar there in September  Align property  node trong cua Thanh Properties Window  2 - vbAlignBottom .

Click on the right of  property (Custom) , and then click on the browse button with three dots to communicate voice  Property Pages  appear. In the dialog, on the  General tab  select the Radio (Option) Button  Use Connection String  and click the button  ... Build .


This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (842x574)


In the Data Link Properties dialog, Provider tab, select Microsoft Jet OLE DB Provider3:51, and then click the Next button>> or the Connection tab.

In the Select or enter a place name database we choose E: \ Program Files \ MicrosoftVisual Studio \ VB98 \ BIBLIO.MDB, in your computer that you can file on disk C or D.Then, you can click the Test Connection button below to see connection is not well established.


This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (907x468)


Up connection is finished, we just want to get anything done Recordset by clicking on  Recordsource property  of Adodc1. In the Property Pages of it select  2-adCmdTable  as  Command Type , and then open the combo box for the  Table or Stored Procedure Name  to select the  Titles table .


This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (763x319)


Depending on how you use the ADO Recordset, it has three types and is called the  Cursor Type . Cursor is simply another name for the recordset:

  • Static Cursor:  Static Cursor give you a static copy (copies rigid) of the records. Static Cursor while you are using, if anyone else modify or add or subtract anything to the recordset you will not see.
  • Keyset Cursor:  Keysey Static Cursor Cursor over the place while you use it, if anyone has changed any record you'll know. If you delete one record, you will not see it anymore. But you will not know if anyone does add a record to the recordset.
  • Dynamic Cursor:  As the word  alive (dynamic)  implies that, while you are using a Dynamic Cursor, if anyone else modify or add or subtract anything to the recordset you will find out.
Choose your 2-adOpenDynamic value for the property Adodc1 Cursor Type:

Now you put labels on Form 4 with Captions: Title, Year Published, ISBN, and PublisherID. Then add 4 textboxes and named them respectively as txtTitle, txtYearPublished,txtISBN vatxtPublisherID.

Data Binding to perform, select the textbox  txtYearPublished  (year of publication), then set the DataSource property  in the Properties Window to its  Adodc1 . When you click on the  DataField property  of the ComboBox txtYearPublished open and you will see listed the names of the Fields in the Titles table. That's because Adodc1 considered taking intermediate Titles table from the database. Here we will choose Year Published column.

Repeat this for the other three textboxes, and select the column title (title), ISBN (the number of international tourism in the library), and PubID (publisher of history) as the DataField for them.

This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (704x392)
Here, although not written a line of code, you can run the program and it will show as below:

You can download the program uses ADO Data Control this from here  ADODatacontrol.zip .

Data Form Wizard

To help programmers design of data forms faster, for our VB6  Data Form Wizard  to generate (produce) a form that supports Edit, Add and Delete records.

Now let's start a new standard VB6 project, named  ADOClass  MS Access file and copy BIBLIO.MDB, ie database, into the same folder of this new project.

Want to use the Data Form Wizard, we must add it to your development environment (IDE) for VB6. You use the Command Menu IDE  Add-Ins | Add-In Manager ... . Choose the  VB6 Data Form Wizard  in the delivery device, and then click Checkbox  Loaded / unloaded  to loaded words right out of "VB6 Data Form Wizard" as shown in the image below:

If you want every time you start VB6 IDE is available in the Data Form Wizard Add-Ins menu option, in addition Loaded, additional check box you click  the Load on Startup .

An  Add-In  is a new menu item, which can be added to an application program is available. Usually, people use the Add-Ins to add functionality to a program, do as the program functions that are available from the beginning. Try to start the Data Form Wizard from the Command Menu new IDE  Add-Ins | Data Form Wizard ...

When the Data Form Wizard page - Introduction appears, click Next

In the next page to select the Access Database Type.

In the Database page, click Browse to select an MS Access database file. Here weselect the file from the folder BIBLIO.MDB of this program. Paragraph click Next.

In the form, we choose the Single Record Form Layout and Binding Class for Type.ClickNext segment. If we choose the ADO Data Control, the result will look like when weuse the DAO Data Control as in a previous post.

In the Record Source I select the Titles table. Fields Available listbox displays the fieldsof the Titles table. After selecting a field by clicking on the Listbox field names that, if youclick the triangle to the right, the only field names that will be moved through the bottomof the right Listbox Selected Fields.

If you double click the triangle just to the right, then all fields will also be moved back to the left to the right.You can also arrange the location of the selected fields by clicking on the name field and click the arrow was pointing up or down to move it up field or down the list of fields.

Also, select Title to  Column to Sort By  a Combobox's in it for the records in the recordset are arranged in alphabetical order (Alphabetical order) of the title field (Title).

Control Selection page, we will for medical reasons to have all the buttons. Please click  Next .

Upon termination of the Data Form Wizard, it will generate frmADODataForm form.Please remove Form1 Command Menu and use Project | Properties ...ADODataControl Change the Startup Object to frmADODataForm. The finish istemporary program to edit the records of the Titles table.

We look at the Form and the Data Form Wizard code is generated. InfrmADODataForm, an array of textboxes do txtFields name. All textboxes are setDataField property of the table field names Titles. For example txtFields (2) there areDataField ISBN. The main form is not used, but using an ADO Data Control Object ofclass clsTitles.

Initialisation of the class is part clsTitles Open a Connection and take on a datasetcalled DataMember laPrimary as follows:


Private Sub Class_Initialize() 
   Dim db As Connection 
   Set db = New Connection 
   db.CursorLocation = adUseClient 
   ' Open connection
   db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=E:\Websites\Vovisoft\VisualBasic\ADOForm\BIBLIO.MDB;" 
   ' Instantiate ADO recordset
   Set adoPrimaryRS = New Recordset 
   ' Retrieve data for Recordset
   adoPrimaryRS.Open "select Title,[Year Published],ISBN,Description,Notes,PubID from Titles Order by Title", _ 
                                                   db,  adOpenStatic, adLockOptimistic 
   ' Define the only data member, named Primary
   DataMembers.Add "Primary" 
End Sub 
The locations of the database, if you do not want it stuck in a folder, use  App.Path  to determine the relationship between the location of the database and the folder of the program is running, for example:


db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" & App.Path & "\BIBLIO.MDB;" 
In  Sub Form_Load , we can use  For Each  to go through all the textboxes in array txtFields. Because the DataSource property of a textbox so we use the keyword Object  Set  it to point to Object PrimaryCLS. Simultaneously, we also must specify the name of each textbox is DataMember Primary:
Private Sub Form_Load() 
   ' Instantiate an Object of class clsTitles
   Set PrimaryCLS = New clsTitles 
   Dim oText As TextBox 
   ' Iterate through each textbox in the array txtFields
   'Bind the text boxes to the data source, i.e. PrimaryCLS
   For Each oText In Me.txtFields 
      oText.DataMember = "Primary" 
      ' Use Set because property Datasource is an Object
      Set oText.DataSource = PrimaryCLS 
   Next 
End Sub 
When moving from record to record this other end, the Recordset can raise  MoveComplete Event . Event was being handled (resolved) in the class by re-raise clsTitles  Event MoveComplete  so it is handled in the form.

I want to handle the Event of clsTitles adoPrimaryRS declare recordset with WithEvents:


Dim WithEvents adoPrimaryRS As Recordset 
Private Sub adoPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, _
   ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) 
   ' Raise event to be handled by main form
   RaiseEvent MoveComplete 
End Sub 
And we also have to declare in Form (object clsTitles) PrimaryCLS with WithEvents:

Private WithEvents PrimaryCLS As clsTitles 
In Form, Event MoveComplete would show absolute position (Absolute Position) ofrecord with the code below:
Private Sub PrimaryCLS_MoveComplete() 
   'This will display the current record position for this recordset
   lblStatus.Caption = "Record: " & CStr(PrimaryCLS.AbsolutePosition) 
End Sub 
When the user clicks  Refresh , the textboxes will be displayed again with the latest details of the record from the recordset, when someone else missed the modified record.  Requery Method  of clsTitles calling Requery method of the recordset as follows:
Private Sub cmdRefresh_Click() 
   'This is only needed for multi user applications
   On Error GoTo RefreshErr 
   ' fetch the latest copy of Recordset
   PrimaryCLS.Requery 
   Exit Sub 
RefreshErr: 
   MsgBox Err.Description 
End Sub 

'In Class clsTitles
Public Sub Requery() 
   ' Fetch latest copy of record
   adoPrimaryRS.Requery 
   DataMemberChanged "Primary" 
End Sub 
You can download this program from here  ADOClass.zip .
(Collected)

1 comment: