This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Article
07/12/2006

July 2000

Getting Started

Use SQL with ADO

Learn how to write SQL queries and execute them from your VB apps using ADO.

by Stan Schultes

Reprinted with permission from Visual Basic Programmer's Journal, July 2000, Volume 10, Issue 7, Copyright 2000, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange.

There are many ways to access data with VB, but the newest and best choice today is ActiveX Data Objects (ADO). In this column, I'll focus on using embedded SQL-or SQL passthrough-statements created within Visual Basic code to show SQL's advantages as a database querying tool with ADO.

The name of the game with ADO is holding references to database objects for as short a time as possible so other users can share them efficiently. The system caches expensive database connections and ADO objects on the user's behalf, which is especially important when writing server-side Web code. This efficiency isn't as critical for a single-user app that accesses a local database, such as the app in this column's sample code (download the code from the VBPJ Web site; see the Go Online box for details).

Visual Basic 6.0 with SP3

ActiveX Data Objects (ADO) 2.1, SP2, or 2.5

Access 97 or 2000

You'll use three ADO objects most often: Connection, Command, and Recordset. You can use most ADO objects without going through an object-creation hierarchy, and you can often get away with only two ADO objects-Connection and Recordset. I'll demonstrate how to execute SQL statements with ADO and display the results in a grid (download the sample app).

Start VB and create a Standard EXE project. Name it ADOTest, and name the default form frmMain. Choose References from the Project menu, and scroll down the list to the Microsoft ActiveX Data Objects 2.1 Library. Check the box in front of this item and click on OK (or you can use the ADO 2.5 library if it's installed). Right-click on the VB toolbox (if necessary, go to Toolbox from the View menu) and select Components from the popup menu. Make sure you clear the Selected Items Only checkbox on the Controls tab. Scroll down, place checkmarks in front of Microsoft Hierarchical FlexGrid Control 6.0 (SP3) and Microsoft Windows Common Controls 6.0 (SP3), and click on OK.

Next, add a textbox (txtSQL) and a command button (cmdSQL) with the caption Execute SQL to frmMain. Double-click on the StatusBar control in the toolbox to add a status bar to the bottom of frmMain, and name it sbrStatus. Right-click on sbrStatus, choose Properties, click on the Panels tab, and set the AutoSize property to 1-sbrSpring. Double-click on the MSHFlexGrid control in the toolbox to add a grid to frmMain. Name it hflxRecords and size it to the form, then set the AllowUserResizing property to 1-flexResizeColumns and the Fixed Cols property to 0 to remove the row's title column.

Jump Quickly and Easily Into the Code

Double-click on the cmdSQL button to open the empty cmdSQL_Click event routine and add this code:

Private Sub cmdSQL_Click()
   Dim MsgText As String
   'display in Grid
   hflxRecords.Clear
   Set hflxRecords.DataSource = _
      ExecuteSQL(txtSQL, MsgText)
   sbrStatus.Panels(1).Text = MsgText
End Sub

You pass the txtSQL textbox's contents to the ExecuteSQL function to allow direct execution of SQL statements. The ExecuteSQL function returns an ADO recordset to the grid's data source in order to display the returned recordset quickly and easily.

Insert a module named modADOTest and add the code for ExecuteSQL (see Listing 1). You use only the ADO Connection and Recordset objects in this function; you use the ADODB object identifier when declaring the Recordset object to avoid confusion with Data Access Object (DAO) recordsets in case the project mixes the use of ADO and DAO.

Place only the code needed to update the form's controls and error handling directly in the cmdSQL_Click event routine. Call the ExecuteSQL function to do the majority of the data access work-a good practice to follow in your own code to help modularize it and ease maintenance.

You open the Connection object in ExecuteSQL with the return value from the ConnectString function (ConnectString is in modADOTest):

Public Function ConnectString() _
   As String
'returns a DB ConnectString
   ConnectString = "Provider=" & _
      "Microsoft.Jet.OLEDB." & _
      "4.0;Data Source="& _
      App.Path & "\NWind.mdb"
End Function

The returned connection string tells ADO to use the Jet OLEDB 4.0 data provider to access a copy of VB's Northwind database.

The If Instr(…) statement in ExecuteSQL checks the passed SQL statement's type by checking the first word in the SQL phrase. ExecuteSQL calls the ADO Connection object's Execute method if the SQL statement is an action query (meaning it doesn't return data). If it's not an action query, open a recordset on the active connection, and use the recordset's MoveLast method to get an accurate record count.

Figure 1. Update Records With User-Entered Data. This completed test form shows the result of updating the ContactTitle field in the ANTON record. The code behind this function shows you how to build the SQL statement from form inputs. The code in the Update function demonstrates how to use the ADO Command object, as well as how to do updates from an ADO Recordset object.

Use the form you've built to test SQL query execution by entering a simple SQL statement, such as SELECT * FROM Customers, in the textbox. Click on the Execute SQL button and you should see the query's results displayed in the grid.

Use SQL's simplest statement, SELECT, to choose records from a database:

SELECT * FROM Orders

This SELECT statement returns every field (*) of every record in the Orders table. You should, in practice, return only data necessary for a given task to save system resources. Saving resources increases your application's scalability-for example, if you're developing a Web application. Specify the needed fields and use a WHERE clause to return a subset of the data matching certain criteria:

SELECT CustomerID,Freight FROM 
   Orders WHERE ShipCountry = 'USA' 
   AND ShippedDate = #10-Nov-94#

You specify a string-type field with a quoted string, and you specify a date by surrounding any valid date value with # symbols. The SQL LIKE operator matches partial fields when you use the standard SQL wildcard characters "%" and "_" with LIKE to create match strings:

SELECT * FROM Orders WHERE ShipCountry 
   LIKE '%land%' ORDER BY ShippedDate

This SELECT statement finds all ShipCountry entries containing the letters "land" anywhere in the ShipCountry field. The ORDER BY clause directs the output to sort by the ShippedDate field.

Don't Confuse Null With Empty

The SQL UPDATE statement modifies records in the database. UPDATE is known as an action query because it doesn't return any data. An UPDATE statement looks like this:

UPDATE Suppliers SET Region = 
   'England' WHERE SupplierID = 1

You clear a string field by setting its value to Null, even if you set the Allow Zero Length property to No:

UPDATE Suppliers SET Region = 
   Null WHERE SupplierID = 1

Null is a special value in SQL, indicating that a field has no value at all. Setting a field to an empty (zero-length) string isn't the same as setting it to Null. A zero-length string is still a string, whereas a Null value is nothing.

Add records to your database with the INSERT statement:

INSERT INTO Employees (LastName, 
   FirstName,Title,BirthDate,ReportsTo) 
   VALUES ('Golding','Juanita',
   'Sales Rep',#29-Feb-76#,2)

INSERT requires a list of field names followed by a matching list of values. Each field name must correspond exactly with a data value of the proper type. The field assumes a Null value if you omit a field name. The EmployeeID field in the Employees table is defined as an AutoNumber datatype. Always leave the AutoNumber field out of the INSERT statement to allow the database to assign the next sequential number when you insert records into a table with this field type.

You remove records from the database with the DELETE statement:

DELETE FROM Employees WHERE 
   LastName = 'Golding'

The INNER JOIN statement returns records from two tables whenever values match in a common field. Modify the SELECT statement to join the tables and see the contact name from the Customers table, the open orders, and the date required to ship from the Orders table:

SELECT Orders.OrderID,RequiredDate,
   ContactName FROM Orders INNER JOIN 
   Customers ON (Orders.CustomerID = 
   Customers.CustomerID) WHERE 
   ShippedDate IS Null

You must prefix fields that appear in both tables; for example, CustomerID appears with the table name and a dot (Orders.CustomerID). If you don't prefix the fields, the field names are ambiguous and SQL can't distinguish which one you're referring to. Specifying ShippedDate IS Null tells SQL to select orders that don't have an entered ShippedDate.

I often use Access as a prototyping tool when I'm working on a new query. The Access Query By Example (QBE) environment is flexible, easy to use, and supports drag-and-drop editing. Switch to the SQL view and copy the SQL query to your VB app once you have it set up and working in the QBE window. The Access QBE can't represent all SQL statements graphically, but it can get you close.

Be aware of the differences between Access SQL and the standard (ANSI) SQL language that ADO requires; see the Microsoft Access Help topic titled "Comparison of Microsoft Jet SQL and ANSI SQL" in the Microsoft Jet SQL Reference in VB's help file for details.

Add Textboxes

Building SQL statements in code is straightforward using the SQL techniques I've shown you. Extend the application by adding two more textboxes (txtCustID and txtTitle) and another command button (cmdUpdate). The first textbox allows entry of a CustomerID; the second lets you enter a new title (see Figure 1).

Add code to the cmdUpdate_Click event routine to execute the UpdateContactTitle function and to redisplay the Customers table (download Listing A from the VBPJ Web site; see the Go Online box for details). The UpdateContactTitle function builds the SQL statement by placing literal single-quote marks around the user-entered character fields for CustomerID and ContactTitle (download Listing B).

Use the VB6 Replace function to substitute two single-quote characters for any lone single-quote character in the Title input string. SQL allows no single-quote characters in string fields during INSERT or UPDATE operations in the Microsoft SQL Server database. Planning ahead can make the transition to a new database type much easier.

Simple SQL statements are easy to write; you use these basic SQL statements as building blocks to form more complex queries. VB programmers need to understand how to delimit field values of different types properly, because they often build SQL statements from input data. This ADO and SQL tutorial gives you a jump on writing code by using simple, yet powerful, database query tools.

Stan Schultes is a project manager and VB and Web enterprise application developer in Sarasota, Fla. Stan is an MCP in VB and spoke on VB development at Microsoft's DevDays conference. He writes regularly for VBPJ. Reach Stan at Stan@VBExpert.com.