PRB: Non-Parameterized SELECT Query Appears in ADO Procedures Collection (252888)

MORE INFORMATION

The Microsoft Jet database engine does not divide queries into Procedures and Views; this is done by the Microsoft Jet OLE DB 4.0 provider. In general, a non-parameterized SELECT query is mapped to the ADOX Views collection and all other query types are mapped to the ADOX Procedures collection.

In order to determine whether a SELECT query is parameterized, all identifiers must be able to be resolved by the query engine. A query that references non-existing table or field names is mapped to the Procedures collection because the identifier names cannot be resolved.

Steps to Reproduce Behavior

1. Start a new Standard EXE project in Microsoft Visual Basic 5.0 or 6.0. Form1 is created by default.
2. Reference the following two object libraries:

   Microsoft ActiveX Data Objects 2.1 Library
   Microsoft ADO Ext. 2.1 for DDL and Security

3. Add a Command button (Command1) and the following code to the form:

   Private Sub Command1_Click()
   Dim cnn As New ADODB.Connection
   Dim cmd As New ADODB.Command
   Dim prm As ADODB.Parameter
   Dim cat As New ADOX.Catalog
   Dim strDB As String
   Dim i As Integer
   
   '
   ' Append parameterized query
   '
     strDB = "MyTest.mdb"
     If Dir(strDB) <> "" Then Kill (strDB)
     cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB
     cmd.CommandText = "Select * From Table1"
   
   '
   ' Append the new Procedure
   '
     cat.Views.Append "SimpleSELECT", cmd
     cat.Views.Refresh
     cat.Procedures.Refresh
   
   '
   ' Enumerate Procedures and Views
   '
     If cat.Procedures.Count = 0 Then
       Debug.Print "No procedure in Procedures collection"
     Else
       For i = 0 To cat.Procedures.Count - 1
         Debug.Print "Procedures Collection: " & cat.Procedures(i).Name
       Next
     End If
     If cat.Views.Count = 0 Then
       Debug.Print "No View in Views collection"
     Else
       For i = 0 To cat.Views.Count - 1
         Debug.Print "Views Collection: " & cat.Views(i).Name
       Next
     End If
   End Sub
   					

4. Run the project and click the Command button. The Debug/Immediate window shows that the SimpleSELECT query is a member of the Procedures collection.