In the previous chapter, we have learned to use the basic SQL keywords SELECT and FROM to manipulate database in Visual Basic 6 environment. In this lesson, you will learn to use more SQL keywords. One of the more important SQL keywords is WHERE. This keyword allow the user to search for data that fulfill certain criteria. The Syntax is as follows:
SELECT fieldname1,fieldname2,.....,fieldnameN FROM TableName WHERE Criteria
The criteria can be specified using operators such as =, >,<, <=, >=, <> and Like.
Using the database books.mdb created in the previous chapter, we will show you a few examples. First of all, start a new project and insert a DataGrid control and an ADO control into the form. . 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. Next, insert one textbox and put it on top of the DataGrid control, this will be the place where the user can enter SQL query text. Insert one command button and change the caption to Query. The design interface is shown below:
Example 21d1: Query based on Author
Run the program and key in the following SQL query statement
SELECT Title, Author FROM book WHERE Author='Liew Voon Kiong'
Where you click on the query button, the DataGrid will display the author name Liew Voon Kiong. as shown below:
Example 21d2:Query based on year
Run the program and key in the following SQL query statement:
SELECT * FROM book WHERE Year>2005
Where you click on the query button, the DataGrid will display all the books that were published after the year 2005.
You can also try following queries:
You may also search for data that contain certain characters by pattern matching. It involves using the Like operator and the % symbol. For example, if you want to search for a author name that begins with alphabet J, you can use the following query statement
SELECT * FROM book WHERE Author Like 'J%'
Where you click on the query command button, the records where authors' name start with the alphabet J will be displayed, as shown below:
Next, if you wish to rank order the data, either in ascending or descending order, you can use the ORDER By , ASC (for ascending) and DESC(Descending) SQL keywords.
The general formats are
SELECT fieldname1, fieldname2.....FROM table ORDER BY fieldname ASC
SELECT fieldname1, fieldname2.....FROM table ORDER BY fieldname DESC
The following query statement will rank the records according to Author in ascending order.
SELECT Title, Author FROM book ORDER BY Author ASC
The following query statement will rank the records according to price in descending order.