Provide Dynamic Data Extraction with SQL Statements
by Mike D. Jones
Application: Visual Basic 5.0/6.0
Visual Basic provides many ways to extract data from a database. For the most part, when retrieving filtered information, stored procedures (saved queries in Access) and VB's internal query builder in conjunction with ADO go a long way toward getting the job done. On occasion, though, you may have need to build a valid Structured Query Language (SQL) statement via code--for dynamic data extraction based on user input, for instance. If you're new to Visual Basic database programming, however, you may not be familiar with this robust query language. In this article, we'll show you some of the basics, so that when you do have to build a statement via code, you'll know exactly what to do.
In essence, SQL provides a generic, standard way to retrieve data from most database software. While it's true that individual databases may have proprietary extensions to SQL, the same useful basic commands apply to any database that implements the specification.
These SQL commands consist of all kinds of ways to manipulate records, data and tables in a database. With these commands, you select, update, add and delete records. For tables, SQL lets you create and delete them. For our purposes, we'll concentrate on selecting records that match criteria chosen at runtime by a user.
Using SQL with ADO recordsets
To use a SQL statement with an ADO recordset, you provide the entire SQL string in the recordset's Open() method, like so:
rst.Open "SELECT * FROM tblBattles", conn, _ , adOpenForwardOnly, , adCmdText
The adCmdText constant lets VB know that it should interpret the value in the first argument as a SQL statement, instead of say a table or stored procedure name. Of course, you can also store a complex SQL string in a variable, then use that variable in the Open() method instead, as in:
strSQL = "SELECT * FROM tblBattles" rst.Open "SELECT * FROM tblBattles", conn, _ , adOpenForwardOnly, , adCmdText
Selecting the right fields
When you create a SQL statement, you in essence form a mini, compact sentence consisting of keywords that the database can interpret. The SELECT command, as you can probably guess, informs the database which fields and data you wish to include in your final result set. A very basic statement consists of the following syntax:
SELECT * FROM tblBattles;
In essence, this statement tells the database to select all (*) fields from the tblBattles table. Assuming for the moment that we've included all the proper code to extract the data and loop through the recordset, the resulting data would appear as shown in Figure A.Figure A: A basic SELECT statement chose every field in the table.
Notice, though, that there's a lot of information a user may not want included in the extraction. For instance, chances are, users couldn't care less about ID values. With larger tables or with limited field requirements, you'd want to restrict which fields to extract even further. For our example, however, we'll just remove the ID. This leaves just the battle name, colony name and date.
Picking and grinning
Fortunately, SQL lets you choose exactly which fields to pull into a recordset. To do so, in place of the previous asterisk you simply provide a comma-delimited list of field names to include. So, to choose only the product name and price fields, you'd send a SQL statement along the lines of:
SELECT battle_name, colony, date FROM tblBattles
This statement generates the data shown in Figure B.Figure B: SQL lets you specify which fields to include in the result set.
Refining a recordset's data
Up to this point, we've talked about limiting which fields VB retrieves from a database. However, SQL also lets you determine which data you wish to retrieve. To do so, you add what's called a WHERE clause. In addition to the WHERE keyword, you provide specific criteria by which the database should select records. If data values in the record match your criteria, then the database includes the record in the result. To build a WHERE clause, you append the keyword WHERE followed by the criteria to the end of a basic SELECT statement, like so:
SELECT * FROM tblBattles WHERE (some_field_name = SomeFieldValue);
For instance, suppose we want to pull only those Revolutionary War battles fought in Virginia. To do so, we'd use the following SQL statement:
SELECT battle_name, colony, date FROM tblBattles WHERE (colony='VA')
With this criterion in place, the database returns the records shown in Figure C.Figure C: A WHERE clause helps limit the data returned in a result set.
Of course, SQL lets you craft criteria that are as specific or as general as you want. You could select a single piece of the data, such as:
SELECT battle_name, colony, date FROM tblBattles WHERE (date=#6/27/1778#)
Or you could select a specific battle by name, like so:
SELECT battle_name, colony, date FROM tblBattles WHERE (battle_name='Saratoga')
Notice that for fields that contain string values, we enclosed the criteria value in quotes. For Access database dates, we enclosed them in pound signs (#). When entering a criteria value for standard numbers, no enclosing characters are necessary. For instance, suppose our table also held a num_days field that contained the number of days each battle lasted. Under these circumstances to extract those battles that lasted longer than 10 days, we'd use the following:
SELECT battle_name, colony, date FROM tblBattles WHERE (num_days > 10)
Advanced criteria clauses
In addition to the standard WHERE clauses we've provide so far, SQL provides more advanced criteria opportunities. In addition to setting criteria on data in fields you've chosen, you can also provide criteria for fields not represented in the final result set. For instance, suppose we knew exactly which ID number we wanted to extract. In that case, we'd fashion a WHERE clause, like this:
SELECT battle_name, colony, date FROM tblBattles WHERE (site_id=5)
This criteria would return the record shown in Figure D. Even though our result didn't include the site_id field, SQL allowed us to limit the returned data based on its value.Figure D: Using a WHERE clause, we excluded certain records based on data in a non-displayed field.
Of course, no criteria feature would be complete without the option to provide Boolean evaluations. The SQL WHERE clause is no exception. With keywords such as AND and OR, you can combine criteria to create even more complex selections. To use these keywords simply combine them together in the WHERE clause, as in:
SELECT battle_name, colony, date FROM tblBattles WHERE (colony<>'MA' AND date >= #1/1/1778#)
This criteria selects the battles shown in Figure E.Figure E: We used the AND Boolean keyword to select battles not fought in Massachusetts and fought later than January 1, 1778.
Sorting the results
As you can see in the figures so far, the results returned by the database appear in the same order they do in the table. Most often, this isn't always the preferred way to display data. You may want to present the information alphabetically by battle name, or in descending order by date. Fortunately, rather than creating complex code to handle each of these potential sorts, you can let SQL do it for you.
To sort the data returned by a database, the SQL language provides the ORDER BY clause. Used in conjunction with a field name, this phrase tells the database to sort the records by the specified table field. With this in mind, we could alphabetize the battles with the following statement:
SELECT battle_name, colony, date FROM tblBattles WHERE (colony<>'MA') ORDER BY battle_name
Figure F shows the results of this SQL statement. By default, the ORDER BY phrase causes the database to sort in an ascending fashion: A to Z for string values, and 0 to infinity for numeric values. To provide a descending sort, append the keyword DESC after the specified field name, like so:
Figure F: We used the ORDER BY clause to present a list of descending battles by date.
SELECT battle_name, colony, date FROM tblBattles WHERE (colony<>'MA') ORDER BY date DESC
Building user-selected criteria for SQL statements
Of course, given the power of Visual Basic, you can also build SQL statements that contain criteria values based on user selections. To do so, you parse the user-selected values into the SQL string. To illustrate, let's build a simple example program.
To begin, launch Visual Basic and open a Standard project. Right-click on the default form. Drop a combobox on the form, name it cboColony, and in its List property enter: MA, NJ, NY and VA. Now, add a regular textbox next to the combobox. Name it txtDate. In the textbox's Text property enter 4/18/1775. Finally, add a command button to the form. Use Figure G as a guide to place the controls.Figure G: Use our sample form as a guide to create your own.
At this point, right-click on the form and select View Code from the shortcut menu. When Visual Basic opens the Code window, enter the Click() event code shown in Listing A. As you can see, after parsing in the user-selected values, this code simply extracts the recordset and then loops through the fields and values to build the output in the Debug window. To see the code in action, press [F5] to run the program. Select MA from the combobox, and leave the textbox as is. Click the Extract Data button to run the code. Figure H shows the results.Listing A: The command button's Click() event
Figure H: We used values entered into the form to create the criteria for the SQL statement that extracted this data.
Private Sub Command1_Click() Dim strSQL As String Dim strConn As String Dim rst As Recordset Dim strTemp As String Dim strPad As String * 20 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "User ID=Admin;Data Source=" & App.Path _ & "\BattleSites.mdb" Set rst = New ADODB.Recordset strSQL = "SELECT battle_name, colony, date FROM " _ & "tblBattles WHERE (colony<>'" _ & cboColony & "' AND date >= #" _ & txtDate & "#) ORDER BY date" With rst .Open strSQL, strConn, adOpenForwardOnly, , _ adCmdText For Each fld In .Fields strPad = fld.Name strTemp = strTemp & strPad Next fld Debug.Print strTemp Do Until .EOF strTemp = "" For Each fld In .Fields strPad = fld strTemp = strTemp & strPad Next fld Debug.Print strTemp .MoveNext Loop .Close End With Set rst = Nothing End Sub
Get the results you want
In any project based on flexible data extraction, getting the data you need is just as important as how you use it, if not more so. Fortunately, you don't need to create complex code routines to filter out unwanted data. Instead, all it takes is a judicial use of SQL. In this article, we've shown just a portion of this useful database querying specification. Hopefully, however, it will provide a strong foundation upon which you can build more advanced knowledge.
Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.