Link to home
Start Free TrialLog in
Avatar of Duc_de_Belfort
Duc_de_Belfort

asked on

Jet 4 does not support OleDb.OleDbSchemaGuid.Procedure_Parameters, alternatives to determine parameters of a stored proc in MS-Access?

I am trying to access Database Schema-Information via VB.NET 2003, ADO.NET.
Apparently Jet 4 (as I found described for Jet 3.51 in MS-KB: http://support.microsoft.com/kb/q191356/ ) does not support "procedure_parameters"-option in OleDbSchemaGuid:

 _conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Procedure_Parameters, New Object() {})

I can not find another possibility to know, which parameters should be supplied to a procedure in MS-Access.
Any help deeply appreciated!

Regards, Duc
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Access does not support STORED PROCEDURES in the same sense that SQL Server or Oracle use the term 'Stored Procedure'

What are you attempting to do?

and the Jet Engine does not directly support getting the Schema from an MDB in any case.

AW
Avatar of Duc_de_Belfort
Duc_de_Belfort

ASKER

My goal is to create a flexible Query-Tool in a .NET application. So I built a form with a textbox, a datagrid and a button, when the button is clicked I try to execute the SQLString in the textbox and fill the datagrid with the result dataset.

Example, I create this view in Northwind, named myParamView:
PARAMETERS sCountry TEXT;
SELECT * FROM Orders
WHERE (((Orders.ShipCountry)=sCountry));

When the user enters the name of this view and leaves out the parameter, I want to give him a feedback to enter the appropriate parameter.

I hope I was clear enough...

Duc
I think you can instantiate a command object based on the query and then iterate the parameters collection of it.

Steve
Steve,
I'm afraid your solution doesn't work. I interpreted your solution similar to:

Dim testcmd As New OleDb.OleDbCommand(myParamView, _conn)
testcmd.Connection.Open()
Debug.WriteLine(testcmd.Parameters.Count)
testcmd.Connection.Close()

The parameters collection does not contain any item...
:-(

Duc
Just increasing points.... :-)
Sorry, ADODB automatically refreshes the parameters collection for you and it sounds like OLEDB does not so you will have to issue the .Refresh directive before getting the count.

cmd.Parameters.Refresh
cmd.Parameters.Count

or to loop through them ...

cmd.Parameters.Refresh
For Each prm In cmd.Parameters
 ...
End If

Steve
I don't have the possibility to refresh the parameters collection:
refresh is not a member of oledb.oledbParameterCollection....

still :-(

Andri
Still no solution for this problem, so I will wait ....

:-(
ASKER CERTIFIED SOLUTION
Avatar of modulo
modulo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial