Lesson 27 : SQL Queries

In the previous lesson, we have learned to use the DataGrid Control to display data from a database in Visual Basic 6 environment. However, it does not allow users to search for and select the information they want to see. In order to search for a certain information, we need to use SQL query. SQL stands for Structures Query Language. Using SQL keywords, we are able to select specific information to be displayed based on certain criteria.

The most basic SQL keyword is SELECT, it is used together with the keyword FROM to select information from one or more tables from a database. The syntax is:

SELECT fieldname1,fieldname2,.....,fieldnameN  FROM  TableName

fieldname1, fieldname2,......fieldnameN are headings of the columns from a table of a database. You can select any number of fieldname in the query. If you wish to select all the information, you can use the following syntax:

SELECT  * FROM  TableNam

In order to illustrate the usage of SQL queries, lets create a new database in Microsoft Access with the following fieldsID, Title, Author, Year, ISBN, Publisher, Price and save the table as book and the database as books.mdb in a designated folder.

Next, we will start Visual Basic and insert an ADO control, a DataGrid and three command buttons. Name the three command buttons as cmdAuthor, cmdTitle and cmdAll. Change their captions to Display Author ,Display Book Title and Display All respectively. You can also change the caption of the form to My Books.

The design interface is shown in Figure 27.1:

Now you need to connect the database to the ADO data control. Please refer to lesson 25 for the details. However, you need to make one change. At the ADODC property pages dialog box, click on the Recordsource tab and select 1-adCmdText  under command type and under Command Text(SQL) key in SELECT * FROM book.

The Code

Now, click on the command button cmdAuthor and key in the following statements:

Private Sub cmdAuthor_Click()
 Adodc1.RecordSource = "SELECT Author FROM  book"
 Adodc1.Refresh
 Adodc1.Caption = Adodc1.RecordSource
End Sub

For the command button cmdTitle, key in

Private Sub cmdTitle_Click()
 Adodc1.RecordSource = "SELECT Title FROM book"
 Adodc1.Refresh
 Adodc1.Caption = Adodc1.RecordSource
End Sub

Finally for the command button cmdAll, key in

Private Sub cmdAll_Click()
 Adodc1.RecordSource = "SELECT * FROM book"
 Adodc1.Refresh
 Adodc1.Caption = Adodc1.RecordSource
End Sub

Now, run the program and when you click on the Display Author button, only the names of authors will be displayed, as shown in Figure 27.3 below:

and when you click on the Display Book Title button, only the book titles will be displayed, as shown in Figure 27.4 below:

Lastly, click on the Display All button and all the information will be displayed, as shown in Figure 27.5 below: