Microsoft KB Archive/107748: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 108: | Line 108: | ||
Once the parameters are set, you are ready to execute the query. There are three methods (Execute, CreateDynaset, and CreateSnapshot) supported by a QueryDef that will cause the query to be executed.<br /> | Once the parameters are set, you are ready to execute the query. There are three methods (Execute, CreateDynaset, and CreateSnapshot) supported by a QueryDef that will cause the query to be executed.<br /> | ||
<br /> | <br /> | ||
More information on parameter queries is available in the Visual Basic, version 3.0, | More information on parameter queries is available in the Visual Basic, version 3.0, "Professional Features Book 2." | ||
=== Example Parameter Queries === | === Example Parameter Queries === | ||
The following example illustrates the use of a short parameter in a query. The example has two parts. The first part creates a new QueryDef for BIBLIO.MDB (the sample Microsoft Access database that ships with Visual Basic) and should be executed only once. The second part uses the QueryDef to create a snapshot, which is then displayed. To test the example, place each of the following code segments in a command button Click event procedure: | The following example illustrates the use of a short parameter in a query. The example has two parts. The first part creates a new QueryDef for BIBLIO.MDB (the sample Microsoft Access database that ships with Visual Basic) and should be executed only once. The second part uses the QueryDef to create a snapshot, which is then displayed. To test the example, place each of the following code segments in a command button Click event procedure: | ||
<pre class="codesample"> 'Create QueryDef | <pre class="codesample"> 'Create QueryDef "by date" | ||
Dim Db As Database | Dim Db As Database | ||
Dim Qd As QueryDef | Dim Qd As QueryDef | ||
Set Db = OpenDatabase( | Set Db = OpenDatabase("C:\VB\BIBLIO.MDB") | ||
Set Qd = Db.CreateQueryDef( | Set Qd = Db.CreateQueryDef("By date") 'Create the query "By date" | ||
QdText = | QdText = "PARAMETERS dp Short; " | ||
QdText = QdText & | QdText = QdText & "SELECT * from Titles WHERE [Year Published] = dp" | ||
Qd.SQL = QdText | Qd.SQL = QdText | ||
Print Qd.SQL | Print Qd.SQL | ||
Line 128: | Line 128: | ||
Dim Qd As QueryDef | Dim Qd As QueryDef | ||
Dim Sn As Snapshot | Dim Sn As Snapshot | ||
Set Db = OpenDatabase( | Set Db = OpenDatabase("C:\VB\BIBLIO.MDB") | ||
Set Qd = Db.OpenQueryDef( | Set Qd = Db.OpenQueryDef("By Date") 'Open the "By date" query | ||
Qd!dp = 1991 'Set the value of the dp parameter | Qd!dp = 1991 'Set the value of the dp parameter | ||
Set Sn = Qd.CreateSnapshot() 'Create a snapshot from the query | Set Sn = Qd.CreateSnapshot() 'Create a snapshot from the query | ||
Line 148: | Line 148: | ||
Dim Db As Database | Dim Db As Database | ||
Dim Qd As QueryDef | Dim Qd As QueryDef | ||
Set Db = OpenDatabase( | Set Db = OpenDatabase("C:\VB\BIBLIO.MDB") | ||
Set Qd = Db.CreateQueryDef( | Set Qd = Db.CreateQueryDef("Delete by name") 'Create the query | ||
QdText = | QdText = "PARAMETERS p1 Text; " | ||
QdText = QdText & | QdText = QdText & "DELETE * FROM Authors WHERE Author = p1;" | ||
Qd.SQL = QdText | Qd.SQL = QdText | ||
Print Qd.SQL | Print Qd.SQL | ||
Line 160: | Line 160: | ||
Dim Db As Database | Dim Db As Database | ||
Dim Qd As QueryDef | Dim Qd As QueryDef | ||
Set Db = OpenDatabase( | Set Db = OpenDatabase("C:\VB\BIBLIO.MDB") | ||
Set Qd = Db.OpenQueryDef( | Set Qd = Db.OpenQueryDef("Delete by name") | ||
Qd!p1 = | Qd!p1 = "Bob" | ||
Qd.Execute 'Perform the action query | Qd.Execute 'Perform the action query | ||
Qd.Close | Qd.Close |
Revision as of 10:20, 20 July 2020
Article ID: 107748
Article Last Modified on 1/8/2003
APPLIES TO
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q107748
SUMMARY
This article explains how to create and use a parameter query. A parameter query is a type of QueryDef specific to Visual Basic and Microsoft Access. Parameter queries enable you to automate the process of changing query criteria. With a parameter query, you can set new values for the parameters each time you run the query.
MORE INFORMATION
A parameter query is created in a program by using the CreateQueryDef() function. Here is the syntax for the CreateQueryDef() function:
Set querydef = database.CreateQueryDef(name, sqltext) querydef - a QueryDef object database - a Database object name - string containing query name sqltext - string containing the SQL query text
The sqltext string is optional or it can be defined by using the .SQL property of the QueryDef. To create a parameter query, place the PARAMETERS statement in the sqltext string. Here is the syntax for the PARAMETERS statement:
PARAMETERS parametertext datatype parametertext - name of the parameter datatype - type of the parameter
The following table lists the appropriate Microsoft Access SQL datatype that should be used with the PARAMETERS statement as well as the corresponding Microsoft Access field type, Visual Basic variable type, and constant value from the DATACONS.TXT file.
Microsoft Microsoft Visual Access SQL Access Field Basic Type DATACONS.TXT Constant ----------------------------------------------------------------- Bit Yes/No Integer DB_BOOLEAN = 1 Byte Byte Integer DB_BYTE = 2 Short Integer Integer DB_INTEGER = 3 Long Long Integer Long DB_LONG = 4 Currency Currency Double DB_CURRENCY = 5 IEEESingle Single Single DB_SINGLE = 6 IEEEDouble Double Double DB_DOUBLE = 7 DateTime Date/Time Variant DB_DATE = 8 Binary Binary String Text Text String DB_TEXT = 10 LongBinary OLE Object String DB_LONGBINARY = 11 LongText Memo String DB_MEMO = 12
Following the PARAMETERS statement in the sqltext string, place the query. The query can refer to the parameter (parametertext) named in the PARAMETERS statement. Wherever the query refers to a parameter the current value will be substituted when the query is executed.
For example, if the query text is:
PARAMETERS i SHORT; SELECT fld FROM tbl WHERE fld=i
and the parameter i was set to 42 in the program. The parameter i would be substituted and the resulting query would be equivalent to:
SELECT fld FROM tbl WHERE fld=42
Multiple Parameters in a PARAMETERS statement
It is also possible to have multiple parameters in a PARAMETERS statement. To do this, use commas to separate the parameters as follows:
PARAMETERS parametertext datatype, parametertext datatype, ...
Prior to executing the query, set the parameters using this syntax:
querydef!parametertext = value querydef - a QueryDef object parametertext - the name of the parameter in the PARAMETERS statement value - the value the parameter will have
In the previous example, you would use QD!i=42 before executing the query.
Once the parameters are set, you are ready to execute the query. There are three methods (Execute, CreateDynaset, and CreateSnapshot) supported by a QueryDef that will cause the query to be executed.
More information on parameter queries is available in the Visual Basic, version 3.0, "Professional Features Book 2."
Example Parameter Queries
The following example illustrates the use of a short parameter in a query. The example has two parts. The first part creates a new QueryDef for BIBLIO.MDB (the sample Microsoft Access database that ships with Visual Basic) and should be executed only once. The second part uses the QueryDef to create a snapshot, which is then displayed. To test the example, place each of the following code segments in a command button Click event procedure:
'Create QueryDef "by date" Dim Db As Database Dim Qd As QueryDef Set Db = OpenDatabase("C:\VB\BIBLIO.MDB") Set Qd = Db.CreateQueryDef("By date") 'Create the query "By date" QdText = "PARAMETERS dp Short; " QdText = QdText & "SELECT * from Titles WHERE [Year Published] = dp" Qd.SQL = QdText Print Qd.SQL Qd.Close Db.Close ' Create Snapshot from QueryDef Dim Db As Database Dim Qd As QueryDef Dim Sn As Snapshot Set Db = OpenDatabase("C:\VB\BIBLIO.MDB") Set Qd = Db.OpenQueryDef("By Date") 'Open the "By date" query Qd!dp = 1991 'Set the value of the dp parameter Set Sn = Qd.CreateSnapshot() 'Create a snapshot from the query Sn.MoveFirst Do Until Sn.EOF For i = 1 To Sn.Fields.Count - 1 Print Sn(i); 'Display results of query Next Print Sn.MoveNext Loop Sn.Close Qd.Close Db.Close
The second example shows how to use an action parameter query. Note that action queries are invoked with the Excute method, not CreateDynaset or CreateSnapshot, because they do not return records:
'Create QueryDef Dim Db As Database Dim Qd As QueryDef Set Db = OpenDatabase("C:\VB\BIBLIO.MDB") Set Qd = Db.CreateQueryDef("Delete by name") 'Create the query QdText = "PARAMETERS p1 Text; " QdText = QdText & "DELETE * FROM Authors WHERE Author = p1;" Qd.SQL = QdText Print Qd.SQL Qd.Close Db.Close ' Execute the QueryDef Dim Db As Database Dim Qd As QueryDef Set Db = OpenDatabase("C:\VB\BIBLIO.MDB") Set Qd = Db.OpenQueryDef("Delete by name") Qd!p1 = "Bob" Qd.Execute 'Perform the action query Qd.Close Db.Close
Additional query words: 3.00 parameterized querydefs
Keywords: KB107748