FAQ: Most Frequently Asked Questions About VB 3.0 SQL Queries

The information in this article applies to:

• Standard and Professional Editions of Microsoft Visual Basic for Windows, versions 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:

   ARTICLE-ID: Q128388
   TITLE     : INF: Rushmore Query Optimization Paper Available on MSL

MORE INFORMATION

1. 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.

2. 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:

      ARTICLE-ID: Q113901
      TITLE     : How to Query for Literal Special Characters in a Where
                  Clause

3. 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:

      ARTICLE-ID: Q105171
      TITLE     : PRB: Error 13 (Type Mismatch) & Error 3061 w/ SQL Queries

4. 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:

      ARTICLE-ID: Q105539
      TITLE     : How to Use VB Control Property or Variable in SQL
                  Statement

5. 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:

      ARTICLE-ID: Q107748
      TITLE     : How to Create a Parameter Query in Visual Basic for
                  Windows

6. 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:

      ARTICLE-ID: Q103808
      TITLE     : Limitations of the Data Control in Visual Basic
                  Version 3.0

7. 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:

      ARTICLE-ID: Q106492
      TITLE     : How to Call SQL Stored Procedures from Visual Basic

8. 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:

      ARTICLE-ID: Q102479
      TITLE     : BUG: Domain Functions Available Only Within SQL Statement

9. 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)

   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:

      ARTICLE-ID: Q102681
      TITLE     : PRB: Error When Updating Fields in Dynaset That Has
                  2+ Tables

      ARTICLE-ID: Q104983
      TITLE     : Referential Integrity Enforced for DBs Created in Access

      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:

      ARTICLE-ID: Q113701
      TITLE     : How to Access Multiple Databases in an SQL Query in
                  VB 3.0

      ARTICLE-ID: Q108423
      TITLE     : How to Attach an External Database Table to a
                  VB 3.0 Database

   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

   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)

      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))

      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.

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

      ARTICLE-ID: Q112112
      TITLE     : How to Optimize Microsoft Access Version 2.0 Queries

      ARTICLE-ID: Q129882
      TITLE     : How to Optimize SQL Queries in Visual Basic 3.0 and 4.0

      ARTICLE-ID: Q99321
      TITLE     : Tips for Optimizing Queries on Attached SQL Tables

Keywords          : APrgDataOther kbfaq kbref
Version           : 3.00
Platform          : WINDOWS
Issue type        : kbinfo
Solution Type     : Info_Provided