DeriveParameters and Jet OLEDB Provider

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.

--

Doug

Stephany
Mon Apr 04 03:33:47 CDT 2005

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.


"Doug" <doug@newsgroup.nospam> wrote in message
news:57F1EB53-5F0A-4A19-A52C-688D65AED9AE@microsoft.com...
> 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.
>
> --
>
> Doug

v-kevy
Mon Apr 04 20:43:17 CDT 2005

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."

doug
Tue Apr 05 02:31:09 CDT 2005

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

v-kevy
Tue Apr 05 03:55:44 CDT 2005

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."