In the previous lesson, we have learned how to use the basic SQL keywords SELECT and FROM to manipulate database in Visual Basic 6 environment. In this lesson, you will learn how to use more SQL keywords. One of the more important SQL keywords is WHERE. This keyword allows 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 more. Using the database books.mdb created in the previous chapter, we shall show you a few examples. First ,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 place it on top of the DataGrid control for the user can enter SQL query text. Insert one command button and change the caption to Query.
The design interface is shown below in Figure 28.1 below:
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 in Figure 28.2 below:
Run the program and key in the following SQL query statement:
SELECT * FROM book WHERE Year>2005
When you click on the query button, the DataGrid will display all the books that were published after the year 2005, as shown in Figure 28.3 below.
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(also known as wildcard character). 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 in Figure 28.4 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 structures 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 runtime interafce is as shown in Figure 28.5 below:
The following query statement will rank the records according to price in descending order.
SELECT Title, Price FROM book ORDER BY Price DESC.
The runtime interafce is as shown in Figure 28.6 below: