Solved

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

Posted on 2004-10-29
530 Views
Last Modified: 2012-08-13
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
0
Question by:Duc_de_Belfort
    9 Comments
     
    LVL 44

    Expert Comment

    by:Arthur_Wood
    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
    0
     

    Author Comment

    by:Duc_de_Belfort
    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
    0
     
    LVL 39

    Expert Comment

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

    Steve
    0
     

    Author Comment

    by:Duc_de_Belfort
    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
    0
     

    Author Comment

    by:Duc_de_Belfort
    Just increasing points.... :-)
    0
     
    LVL 39

    Expert Comment

    by:stevbe
    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
    0
     

    Author Comment

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

    still :-(

    Andri
    0
     

    Author Comment

    by:Duc_de_Belfort
    Still no solution for this problem, so I will wait ....

    :-(
    0
     

    Accepted Solution

    by:
    PAQed with points refunded (250)

    modulo
    Community Support Moderator
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Shellfire Box VPN + Lifetime Subscription

    The Shellfire Box easily connects all of your devices, even those that don't offer the possibility to establish a safe vpn connection. Access blocked content and surf safely, no matter where in the world you are located.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now