DeriveParameters and Jet OLEDB Provider

[Guest]

Hi

I've just discovered that the Jet OLEDB Provider does not support
OleDbCommandBuilder.DeriveParameters, to discover the number and type of
parameters expected by a Microsoft Access query.

I'm a little surprised, as it was always possible to use the ADO Refresh
method with this provider to populate a Paramters collection.

Is there a reason for this? Is there a workaround?

I know that it's an expensive operation which should be avoided anyway, but
it's sometimes useful, and I'd like to use the same code against both Access
and SQL Server.

Thanks in advance.

 

[Stephany Young]

From ADO.Command object:
<quote>
Using the Refresh method on a Command object's Parameters collection
retrieves provider-side parameter information for the stored procedure or
parameterized query specified in the Command object. The collection will be
empty for providers that do not support stored procedure calls or
parameterized queries.
<unquote>

From OleDBCommandBuilder.DeriveParameters method:
<quote>
You can only use DeriveParameters with stored procedures. You cannot use
DeriveParameters to populate the OleDbParameterCollection with arbitrary
Transact-SQL statements, such as a parameterized SELECT statement.
<unquote>

In Jet a 'saved' query is not a stored procedure.

 

[Kevin Yu [MSFT]]

Thanks for Stephany's response.

Hi Doug

It is true that we can only derive parameters from a stored procedure. This
behavior is by design. In this case, I'm afraid we have to add parameter to
the collection manually.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Thanks to both for your replies.

At least I know that I'm going to have to find a workaround, and I'm not
being dim!

I still find it surprising though, that something ADO + JET OELDB was
obviously able to do has not been carried forward to ADO.NET.

Doug

 

[Kevin Yu [MSFT]]

You're welcome, Doug. Thanks for sharing your experience with all the
people here. If you have any questions, please feel free to post them in
the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."