Microsoft KB Archive/137025: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - "&" to "&") |
||
(2 intermediate revisions by the same user not shown) | |||
Line 42: | Line 42: | ||
<li>Q. How do I write an SQL query that has values that contains single or double quotation?<br /> | <li>Q. How do I write an SQL query that has values that contains single or double quotation?<br /> | ||
<br /> | <br /> | ||
A. Single quotation marks ('), double quotation marks ( | 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:<br /> | ||
<br /> | <br /> | ||
'''[[../113901|Q113901]]''' : How to Query for Literal Special Characters in a Where Clause</li> | '''[[../113901|Q113901]]''' : How to Query for Literal Special Characters in a Where Clause</li> | ||
<li>Q. I received the error, | <li>Q. I received the error, "1 parameter expected but only 0 were supplied." What does this mean?<br /> | ||
<br /> | <br /> | ||
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:<br /> | 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:<br /> | ||
<br /> | <br /> | ||
'''[[../105171|Q105171]]''' : PRB: Error 13 (Type Mismatch) & | '''[[../105171|Q105171]]''' : PRB: Error 13 (Type Mismatch) & Error 3061 w/ SQL Queries</li> | ||
<li>Q. How do I use a Visual Basic variable in an SQL query?<br /> | <li>Q. How do I use a Visual Basic variable in an SQL query?<br /> | ||
<br /> | <br /> | ||
Line 94: | Line 94: | ||
Inner Join Titles On Publishers.PubID=Titles.PubID | Inner Join Titles On Publishers.PubID=Titles.PubID | ||
</pre> | </pre> | ||
<p>For more information, please see the | <p>For more information, please see the "Joins" topic in the Visual Basic Help file and the following articles in the Microsoft Knowledge Base:<br /> | ||
<br /> | <br /> | ||
'''[[../102681|Q102681]]''' : PRB: Error When Updating Fields in Dynaset That Has 2+ Tables<br /> | '''[[../102681|Q102681]]''' : PRB: Error When Updating Fields in Dynaset That Has 2+ Tables<br /> | ||
Line 101: | Line 101: | ||
<li>Q. How can I build a Select statement that includes multiple tables in more than one database?<br /> | <li>Q. How can I build a Select statement that includes multiple tables in more than one database?<br /> | ||
<br /> | <br /> | ||
A. You can specify an external database with the <table | 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:<br /> | ||
<br /> | <br /> | ||
'''[[../113701|Q113701]]''' : How to Access Multiple Databases in an SQL Query in VB 3.0<br /> | '''[[../113701|Q113701]]''' : How to Access Multiple Databases in an SQL Query in VB 3.0<br /> | ||
Line 136: | Line 136: | ||
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.)</p> | 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.)</p> | ||
<pre class="CODESAMP"> (Select Author, Au_ID From Authors Where Not IsNull(Author)) | <pre class="CODESAMP"> (Select Author, Au_ID From Authors Where Not IsNull(Author)) | ||
Union (Select | Union (Select "Anonymous" As Author, Au_ID From Authors Where | ||
IsNull(Author)) | IsNull(Author)) | ||
</pre></li> | </pre></li> | ||
Line 142: | Line 142: | ||
<br /> | <br /> | ||
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:</p> | 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:</p> | ||
<pre class="CODESAMP"> DB.Execute( | <pre class="CODESAMP"> DB.Execute("Select * Into X From Y") | ||
</pre> | </pre> | ||
<p>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:</p> | <p>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:</p> | ||
<pre class="CODESAMP"> DB.Execute( | <pre class="CODESAMP"> DB.Execute("Drop Table X") | ||
</pre> | </pre> | ||
<p>With Jet 1.1, you must close and reopen the database to refresh the TableDefs collection.</p></li> | <p>With Jet 1.1, you must close and reopen the database to refresh the TableDefs collection.</p></li> |
Latest revision as of 12:27, 21 July 2020
The information in this article applies to:
SUMMARYThis 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.
MORE INFORMATION
Keywords :
|