Programming language Visual Basic 6 (Chapter 14 - Using Data Control)

Control Data

From VB5, Visual Basic for a programmer to control access to databases, it's just a simple name is  Data. As we know, there is a database when you buy bundled Microsoft VB6 - that is  Jet Database Engine .Jet Database Engine is the  "engine room"  of the MS Access Database Management System.

Until VB5, Microsoft gives us three main techniques:

• DAO (Data Access Objects) : DAO is esoteric techniques of Microsoft, just to use the Jet Database Engine. It is easy to use, performance and convenience, but are limited within MS Access. Nevertheless, it is so prevalent because there are practical benefits.
• ODBC (Open Database Connectivity) : ODBC is designed to let users connect with all sorts of databases that use only a single method. This put the burden for the programmer, to just learn a single programming techniques that can work with any database of any kind. Especially if you later need to change the type of database, such as upgrading from Access to SQLServer instance, the alteration of very little coding. When using ODBC with DAO, you can connect to Access Database with other databases. There is a disadvantage of ODBC is that it is confusing.
• RDO (Remote Data Object) : One of the main reasons for RDO is designed to solve the difficult problem of ODBC. How to program with simple DAO RDO, but actually it should allow users to use ODBC connections to multiple databases. However, the RDO is not very prevalent.

VB6 continue to support the above techniques, and to add a new database access technology, is important, such as  ADO (ActiveX Data Objects) . In a next article we will learn about ADO with the advantages of it. However, for very simple DAO and performance so we can continue to use it very effective in most application. Therefore this article and all plans will focus on programming techniques that are popular with DAO.

Using the Data control is simplified by placing it on a Form then work with the Properties of it. Begin a new VB6 project, giving it the name  DataControl  by clicking project name in Project Explorer right then edit the Name property in the Properties Window.

DoubleClick on the icon of the Control Data in the Toolbox. A Data Control named  Data1  will appear on the form. Want to its underlying form, like a statusbar, please set  the Align property  of it in the Properties Window into  2 - Align Bottom .

Click on the right of  property databasename , and then click on the browse button with three dots to select a file from the communication device dabase Access to Data1. Here we choose  E: \ Program Files \ Microsoft Visual Studio \ VB98 \ BIBLIO.MDB  , in your computer maybe it is on disk  C  or  D .

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

In this program we want to work with the  Titles table  of BIBLIO.MDB database, to view and edit the records. Notice  DefaultType property  of Data1 is the number of  2 - UseJet , ie, the DAO technique, instead of using ODBC technology.

When you click on  Recordsource property  of Data1, then click on the small triangle to the right, a ComboBox opens shows a list of tables in the database. Choose your  Titles . Notice  RecordsetType property  of Data1 have a value  0 - Table : 

The new word that we will use frequently to access the data in VB6  Recordset  (the records). Recordset is a  Set of records , it may contain some records or no record at all. A record in the Recordset can be a record from a table. In the event that we can retrieve all records or only those records in the table agreed on one condition, for example, we just want to get the records of the books published before 1990 (Published Year <1990).

Record in a Recordset can also be a set of columns (columns) from two (or three) tables through relationships one-to-one and one-to-many. For example when taking the records from the Titles table, we want more detail the company name (Company Name) and Phone (Telephone), publisher (Publishers table) by using the  Foreign Key PubID  in the Titles table as  Primary Key  Publishers in the table to retrieve the details it. If you have not mastered the concept Foreign Key, please read all  Database .

In the event that we can view as a  virtual (virtual) table  is a set of two tables Titles, and Publishers.

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.

Select txtTitle textbox, then set its DataSource property to Data1 in the PropertiesWindow. When you click on the property of txtTitle Datafield and open the ComboBox, you will see listed the names of the Fields in the Titles table. That's because Data1 isconsidered intermediate Titles taken from the database table. Here we will select theTitle column.

Repeat this for the other three textboxes, and select the column Published Year (year of publication), ISBN (the number of international tourism in the library), and PubID (publisher of history) as Datafield for them.

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

Here, although not written a line of code, we can run the program right. It will show details of first record in the Titles table as below:

You can click the button to move  Navigator Buttons  to go to the record  head (first), before (previous), next (next)  and  late (last) . Every time you move to a new record is more of that record is displayed. If not using the Navigator Buttons, we can also code for equivalent work by calling the recordset  methods MoveFirst, MovePrevious, MoveNext  and  MoveLast .

When the last record of the recordset is displayed, if we call the property methodMoveLast EOF (End-Of-File) of the recordset becomes True. Similarly, when the firstrecord of the recordset is displayed, if we call the property method MovePrevious BOF(Begin-Of-File) of the recordset becomes True. If a recordset does not contain a recordat all properties, both BOF and EOF are both True.

Feature to display data in accordance textboxex current record ( current record ) is known as data binding  or  bound data  (the data required) and TextBox control supports this functionality is said toData Aware  (known relative data data).

When the first record is displayed, if you edit the  Year Published  to change from 1985 to  1983 Navigator, then click the Next button to display the second record, and then click Previous Navigator button to display the first record you will see the field Published Year of first record has really been changed (updated) in 1983.

This means that when this Data1 navigates from record to record another record, if this has changed because the user edited, it stores the change before the move. Not sure you want this, so if you do not want users accidentally edit a record, you can set the  Locked property  to True for the textboxes so that user can not edit the textboxes as in the image below: 

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

You can download the program from here this amateur  Datacontrol.zip . 

Specify the database location at run

Specifying the name databasename in the design phase (at design time) we had used previously but convenient but somewhat dangerous, because when you install this program on the client computer, not sure that the database files in a folder same name. For example, the database on my computer in the folder E: \ Program Files \ Microsoft Visual Studio \ VB98, but on the client computer, the database is located in the folder C: \ VB6 \ DataControl such. Thus, when the boot program should determine the location of the database. Suppose we want to the database in the same folder as the program is running, you can use the  Path property  of the Application Object  App  as follows:

Dim AppFolder As String Private Sub Form_Load() ' Fetch Folder where this program EXE resides AppFolder = App.Path ' make sure it ends with a back slash If Right(AppFolder, 1) <> "\" Then AppFolder = AppFolder & "\" ' Assign Full path database filename to Data1 Data1.DatabaseName = AppFolder & "BIBLIO.MDB" End Sub

With the above code will ensure it finds the database file in place, people do not know where you are installing the program in the client computer's hard disk.

If you're school VB6 remote database when submitting articles to the VB6 that you hardcode supervisor (stuck writing) the location of database files during the design supervisor (tutor) is also having difficulty with this because not sure assessment marketing database will contain a folder with the same name as in your harddisk.

Additions of Records

The program also uses temporary, but it does not give us the means to add (add), remove (delete) the records. Now you go to 5 buttons on the Form name:  cmdEdit, cmdNew, cmdDelete, cmdUpdate  and cmdCancel .

Although you do not see, but actually Control Data  Data1  a  Recordset property  when we use the Navigator buttons and a scroll from record to record in this recordset it. We can talk about it with Notation (writing)  Data1.Recordset , and every time to get new Recordset from the database we use  the Refresh method  as  Data1.Recordset.Refresh .

At launch the new program, the user is viewing (browsing) the records, the two buttons  Update  and Cancel does not need to work. So we'll add the Lock (key) and disable the textboxes (made powerless) because these two buttons do not need to use them.

In Sub SetControls below, we use a parameter called Editing with True or False valuedepending on the user are the Browse or Edit, called Browse mode and Edit mode. InEdit mode, the Textboxes are unlocked (unlocked) and cmdNew button, and cmdEditcmdDelete become impotent:

Sub SetControls(ByVal Editing As Boolean) ' Lock/Unlock textboxes txtTitle.Locked = Not Editing txtYearPublished.Locked = Not Editing txtISBN.Locked = Not Editing txtPublisherID.Locked = Not Editing ' Enable/Disable buttons CmdUpdate.Enabled = Editing CmdCancel.Enabled = Editing CmdDelete.Enabled = Not Editing cmdNew.Enabled = Not Editing CmdEdit.Enabled = Not Editing End Sub

In Browse mode, like the following form:

Sub SetControls called the  Sub Form_Load  when the program started and in the  Sub CmdEdit  when users click  Edit  as follows:

Private Sub Form_Load() ' Fetch Folder where this program EXE resides AppFolder = App.Path ' make sure it ends with a back slash If Right(AppFolder, 1) <> "\" Then AppFolder = AppFolder & "\" ' Assign Full path database filename to Data1 Data1.DatabaseName = AppFolder & "BIBLIO.MDB" ' Place controls in Browse Mode SetControls (False) End Sub Private Sub CmdEdit_Click() ' Place controls in Edit Mode SetControls (True) End Sub

When you delete a record in the recordset, the current position of record (current record)has not changed. Therefore, after delete a record we must MoveNext. Unfortunately, ifwe just delete the last record of the recordset, after the MoveNext, EOF property to truewill of the recordset. Turns out we have to check that, if true, it would be MoveLast to display the last record of the recordset as in the Sub cmdDelete_Click code below:

Private Sub CmdDelete_Click() On Error GoTo DeleteErr With Data1.Recordset ' Delete new record .Delete ' Move to next record .MoveNext If .EOF Then .MoveLast Exit Sub End With DeleteErr: MsgBox Err.Description Exit Sub End Sub

While the code, we update (updates) a record in the recordset using the methodUpdate. But we can only call the Update method of a Recordset the Recordset is in theEdit or AddNew mode. I put a recordset in Edit mode by invoking the Edit method of therecordset, such nhuData1.Recordset.Edit. Similarly, we set a recordset in AddNewmode of the Recordset AddNew goimethod by, for example Data1.Recordset.AddNew.

Private Sub cmdNew_Click() ' Place Recordset into Recordset AddNew mode Data1.Recordset.AddNew ' Place controls in Edit Mode SetControls (True) End Sub

Recordset Update method after calling him out of the recordset AddNew or Edit modes.We can also pull themselves out AddNew or Edit modes, or rather told to cancel allpending (waiting) by calling the Update method CancelUpdate, such as Data1.Recordset.CancelUpdate.

You can download this program from here  DataEdit.zip .

Dùng DataBound Combo

In our current program history only shows the publisher (PubID) of Title, and not have further details. Would that even though stored program  PubID , but displays the  Company Name  of publisher for us to work out the numbers to remember or too. We can do this by using Control  DBCombo (Data Bound Combo) .You use the Command Menu IDE  Project | Components ... to select  Microsoft Data Bound List Controls 6.0  and click  Apply . 

Next, add a name DBCombo  DBCombo1  the Form. Because we need to provide a table recordset Publisher DBCombo1, so please add a second Data control named  Data2  on Form. For Data2, please set the databasename property of  E: \ Program Files \ Microsoft Visual Studio \ VB98 \ BIBLIO.MDB and property RecordSource the  Publishers . To not let people see the Data2 at run-time, you set the  Visible property  to False it.

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

The purpose of our show is taking DBCombo1 Company Name of publisher, but behind his back, then nothing changes, that is, we still work with the record PubID Title of Data1. When the user clicks on DBCombo1 to choose a publisher, then we follow PubID Company Name that contains the corresponding record of Data1 Title. Thus there are many things we have arranged for DBCombo1 as follows:

Property	Value	Caption
RowSource	Data2	This is the main datasource DBCombo1. It provides the Publishers table.
Listfield	Company Name	When RowSource above was chosen then, Combo Listfield of this property will be displayed fields of the Publishers table. Company Name is the field of RowSource that we want to display on DBCombo1.
DataSource	Data1	This is the datasource of the record that we want. edit, ie the record of the table Titles
Datafield	PubID	Field (Title of record) will be changed.
BoundColumn	PubID	Field trong RowSource (table Publishers) tương ứng với item user chọn trong DBCombo1 (Company Name).

When in Edit mode users select a Company Name in DBCombo1 then click the Update button you will see changes as well txtPublisherID textbox and display the new number PubID history. Update If you want to see before the new PubID txtPublisherID displayed in the textbox you can use the Click Event of DBCombo1 as follows:

Private Sub DBCombo1_Click(Area As Integer) ' Display new PuBID txtPublisherID.Text = DBCombo1.BoundText End Sub

BoundText property  of the value of the BoundColumn DBCombo1 that we can access (write or read) it.For example when you want to add a new record, the default Title PubID is 324, ie Company Name = GLOBAL ENGINEERING. " You can assign the numbers 324 on property in the Sub cmdNew_Click DBCombo1 BoundText of the following:

Private Sub cmdNew_Click() ' Place Recordset into Recordset AddNew mode Data1.Recordset.AddNew ' Default Publisher is "GLOBAL ENGINEERING", i.e. PubID=324 DBCombo1.BoundText = 324 ' Place controls in Edit Mode SetControls (True) End Sub

You can download this program from here  DataBound.zip .

In the next article we will learn more about how to use coding techniques DAO Recordset in.

(Collected)