Lesson 27: Using SQL queries in Visual Basic 6
|
In the previous chapter, 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 TableName In order to illustrate the usage of SQL queries, lets create a new database in Microsoft Access with the following filenames ID, 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 below:
|
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.
|
Next, click on the command buttton 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
and 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 below:
and when you click on the Display Book Title button, ony the book titles will be displayed, as show below:
Lastly, click on the Display All button and all the information will be displayed.
