FAQ: Most Frequently Asked Questions About VB 3.0 SQL Queries

ID: Q137025

The information in this article applies to:

Microsoft Visual Basic Standard and Professional Editions for Windows, version 3.0

SUMMARY

This article covers some of the most frequently asked questions (FAQ) about the Structured Query Language (SQL) for Microsoft Visual Basic for Windows. You can find this and other FAQ articles by querying in the Microsoft Knowledge Base using the keyword FAQ.

NOTE: Many of the SQL statements in this article use the Biblio.mdb Access database that ships with Visual Basic.

For more information on SQL queries, please see the following articles in the Microsoft Knowledge Base:

Q128388 : INF: Rushmore Query Optimization Paper Available on MSL

MORE INFORMATION

Q. How can I test my SQL query quickly and easily.

A. Use VisData. VisData is a sample application that ships with the Professional Edition of Visual Basic version 3.0. You'll find it in C:\VB\Samples\VisData\VisData.mak. It has a window to test SQL queries, can open all of the database types that Visual Basic supports, and has proven to be very stable.

Q. How do I write an SQL query that has values that contains single or double quotation?

A. Single quotation marks ('), double quotation marks ("), and the pipe symbol (|) are all special characters in Microsoft Jet SQL. Double up the quotation marks or, if you have only one type of quotation mark (single or double) in your data, use the other one as the delimiter. All other characters are handled as is. For more information, please see the following article in the Microsoft Knowledge Base:

Q113901 : How to Query for Literal Special Characters in a Where Clause

Q. I received the error, "1 parameter expected but only 0 were supplied." What does this mean?

A. Microsoft Jet is telling you that it didn't recognize one or more of the terms you used in the SQL statement. Look for typographical errors, Visual Basic variables embedded incorrectly in SQL, or names of fields that don't exist in the selected tables. For more information, please see the following article in the Microsoft Knowledge Base:

Q105171 : PRB: Error 13 (Type Mismatch) & Error 3061 w/ SQL Queries

Q. How do I use a Visual Basic variable in an SQL query?

A. Visual Basic variables and properties must be concatenated into the SQL string. If you embed the name of the variable, your code will search using the variable name rather than the variable value. For more information, please see the following article in the Microsoft Knowledge Base:

Q105539 : How to Use VB Control Property or Variable in SQL Statement

Q. How do I call a parameter query in Visual Basic?

A. Create a QueryDef object using the CreateQueryDef method. For more information, please see the following article in the Microsoft Knowledge Base:

Q107748 : How to Create a Parameter Query in Visual Basic for Windows

Q. Why can't I get a parameter query to work on the Data Control?

A. This is a limitation of the Data Control. It does not support parameter queries. For more information, please see the following article in the Microsoft Knowledge Base:

Q103808 : Limitations of the Data Control in Visual Basic Version 3.0

Q. How can I call stored procedures (queries) for SQL Server databases?

A. You can pass the stored procedure name to an ExecuteSQL, a CreateDynaset, or a DataControl's RecordSource property. For more information, please see the following article in the Microsoft Knowledge Base:

Q106492 : How to Call SQL Stored Procedures from Visual Basic

Q. Why don't the Domain Aggregate functions work as documented?

A. The Domain Aggregate functions were incorrectly documented as though they were Visual Basic functions. They are actually Microsoft Jet SQL functions, so they can only be used inside an SQL statement. For more information, please see the following article in the Microsoft Knowledge Base:

Q102479 : BUG: Domain Functions Available Only Within SQL Statement

Q. How can I select a specific list of values or exclude certain records in a Select statement?

A. Use the In keyword to identify the values you want to include or exclude, adding the Not keyword to exclude, as in this example:
```
      Select * From Authors Where Au_ID In (1, 3, 5, 7, 9)
      Select * From Authors Where Au_ID Not In (1, 3, 5, 7, 9)
  
```

Q. How can I build a Select statement that works with multiple tables?

A. A simple Select statement across unrelated tables returns a useless cross product of all the records in the tables listed in the From clause:
```
      Select * From Publishers, Titles
   
```
To get a useful list of data, your data must have a common key field (or set of fields) that relate the tables. You can retrieve data from your tables by simply ensuring that these key fields match for each row you retrieve:
```
      Select * From Publishers, Titles
         Where Publishers.PubID=Titles.PubID
   
```
However, this returns rows without recognizing the relationship between the records in the separate tables, so the resulting RecordSet will not be updatable. To return an updatable recordset, you need to join the tables as in this example:
```
      Select * From Publishers
         Inner Join Titles On Publishers.PubID=Titles.PubID
   
```
For more information, please see the "Joins" topic in the Visual Basic Help file and the following articles in the Microsoft Knowledge Base:

Q102681 : PRB: Error When Updating Fields in Dynaset That Has 2+ Tables

Q104983 : Referential Integrity Enforced for DBs Created in Access

Q. How can I build a Select statement that includes multiple tables in more than one database?

A. You can specify an external database with the <table> IN <database> syntax, specify the connect string in your SQL query, or attach the tables to a Microsoft Access database. For more information, please see the following articles in the Microsoft Knowledge Base:

Q113701 : How to Access Multiple Databases in an SQL Query in VB 3.0

Q108423 : How to Attach an External Database Table to a VB 3.0 Database

Q. How can I do bit operations in SQL?

A. You can retrieve data based on which bit position is set or cleared in a numeric field with Integer Division and the Mod (modulus) operator.

To check if a bit is set, use this syntax:
```
         Select * From MyTable Where (MyNumField \ APowerOf2) Mod 2 = 1
  
```
To check if a bit is clear, use this syntax:
```
         Select * From MyTable Where (MyNumField \ APowerOf2) Mod 2 = 0
  
```
For example:
```
         Select * From Authors Where (Au_ID \ 16) Mod 2 = 1
  
```
This retrieves all the records from the Authors table where the fourth bit is set. (16 decimal = 10000 binary. Bit position counts from 0 beginning with the right-most position and moving to the left.

The syntax for setting or clearing a bit in SQL is similar to the syntax for checking the bit. The following example sets the third bit on Titles.[Year Published]:
```
      Update Publishers Set [Year Published] = [Year Published] + 8
         Where ([Year Published] \ 8) Mod 2 = 0
  
```

Q. How can I merge two tables using SQL?

A. In a normalized database, you should not have duplicate data in different tables. However, it isn't unusual to have duplicate tables (possibly in separate databases) for archived data. If you need to merge this data with SQL you can do it with the following syntax. This query merges the contents of Table2 into Table1 ignoring any duplicate records.
```
   
         Insert into Table1 Select Table2.* From Table2 Where
         Table2.Keyfield Not In (Select Table1.Keyfield From Table1)
 
```

Q. How can I return a default value in my Select statement result when the real value stored in the table is null?

A. You can construct a SQL statement that returns a default value rather than Null for those records where the value is actually null and use the Union operator to add these records to those where the value is not null. For example, the following query returns the word Anonymous for any record in the Authors table where the Author field is null. (To make this example work, you need to add a record with an AU_ID value but no Author value.)
```
      (Select Author, Au_ID From Authors Where Not IsNull(Author))
         Union (Select "Anonymous" As Author, Au_ID From Authors Where
         IsNull(Author))
   
```

Q. When I try to recreate a table that I just deleted, Visual Basic says that the table is in use. What do I do?

A. If you use the Delete method on the TableDefs collection to delete table X, you may not be able to immediately recreate a table by the same name with something like this statement:
```
      DB.Execute("Select * Into X From Y")
  
```
Visual Basic reports that the table already exists. With Microsoft Jet 2.0, you can work around this by using the Drop Table SQL statement, as in this example:
```
      DB.Execute("Drop Table X")
   
```
With Jet 1.1, you must close and reopen the database to refresh the TableDefs collection.

Q. How can I make my SQL statement run faster?

A. For a list of hints, please see the following articles in the Microsoft Knowledge Base:

Q112112 : How to Optimize Microsoft Access Version 2.0 Queries

Q129882 : How to Optimize SQL Queries in Visual Basic 3.0 and 4.0

Q99321 : Tips for Optimizing Queries on Attached SQL Tables


Keywords          : 
Version           : 3.00
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: June 14, 1999