PRB: Error When Query Builder Runs Parameterized Queries (258888)

SYMPTOMS

When you use Query Builder in the Data Environment of Visual Basic to run parameterized queries, you may receive the following error message:

Provider cannot derive parameter and SetParameterInfo has not been called.

This problem occurs when you use Microsoft Oracle OLE DB Provider.

CAUSE

Query Builder does not process the parameter information from the Command object's property page. To obtain the parameter information, Query Builder calls the DescribeParameters function of OLE DB Provider. This error is thrown because OLE DB Provider does not support this functionality. Query Builder does not run parameterized queries if the provider does not support DescribeParameters.

MORE INFORMATION

Steps to Reproduce Behavior

Create a new Standard EXE project in Visual Basic. Form1 is created by default.
From the Project menu, click Add Data Environment.
Right-click the Connection1 object, and then click Properties.
Double-click Microsoft OLEDB Provider for Oracle.
Type the necessary information to connect to an Oracle server. Make sure that you click Test Connection to verify the information, and then click OK.
Right-click the Connection1 object, and then click Add Command.
Right-click Command1, and then click Properties.
On the General tab of the property page, click SQL Statement, and then click Query Builder.
Create a query with a parameter in the WHERE clause. To do this, type a question mark (?) in the Criteria column, or add the query in the SQL code window. For example:
```
SELECT * FROM Customers WHERE Name = ?
					
```
From the Query menu, click Run. You receive the above-mentioned error message.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

240205 PRB: ADO Parameters.Refresh Fails with MSDAORA Provider and Parameterized Query