Query a stored procedure to find out its parameters 
Author Message
 Query a stored procedure to find out its parameters

Hi
I'm trying to write a COM DLL in VB to call different stored procedures with
different parameters.
I like to know if it is possible to query a specific stored procedure from
VB to find out how many input parameters it needs before calling it? Is way
I can find out how many variables I need to pass into it.
Thanks


Tue, 08 Oct 2002 03:00:00 GMT  
 Query a stored procedure to find out its parameters

typed and posted to microsoft.public.vb.database:

Quote:
>Hi
>I'm trying to write a COM DLL in VB to call different stored procedures with
>different parameters.
>I like to know if it is possible to query a specific stored procedure from
>VB to find out how many input parameters it needs before calling it? Is way
>I can find out how many variables I need to pass into it.
>Thanks

All Data Access objects have a parameters collection.  Just
interrogate that.  Here is a snippet for ado.

Sub NameParameters()

    Dim cnn As ADODB.Connection
    Dim qry As ADODB.Command
    Dim par As ADODB.Parameter
    Dim ConnectString As String

    Set cnn = New ADODB.Connection
    Set qry = New ADODB.Command

    ' SQL 7 - use provider=sqloledb instead of driver=
    ConnectString = "Driver={sql server};" & _
      "Data Source=JMJFashions; "

    With cnn
        .Open ConnectString
    End With

    With qry
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "Create_Upd_Spec_Measure"
    End With

    For Each par In qry.Parameters
        Debug.Print par.Name, par.Type
    Next

End Sub



Wed, 09 Oct 2002 03:00:00 GMT  
 Query a stored procedure to find out its parameters
Thanks,
I tried it and worked perfectly.


Quote:

> typed and posted to microsoft.public.vb.database:

> >Hi
> >I'm trying to write a COM DLL in VB to call different stored procedures
with
> >different parameters.
> >I like to know if it is possible to query a specific stored procedure
from
> >VB to find out how many input parameters it needs before calling it? Is
way
> >I can find out how many variables I need to pass into it.
> >Thanks

> All Data Access objects have a parameters collection.  Just
> interrogate that.  Here is a snippet for ado.

> Sub NameParameters()

>     Dim cnn As ADODB.Connection
>     Dim qry As ADODB.Command
>     Dim par As ADODB.Parameter
>     Dim ConnectString As String

>     Set cnn = New ADODB.Connection
>     Set qry = New ADODB.Command

>     ' SQL 7 - use provider=sqloledb instead of driver=
>     ConnectString = "Driver={sql server};" & _
>       "Data Source=JMJFashions; "

>     With cnn
>         .Open ConnectString
>     End With

>     With qry
>         .ActiveConnection = cnn
>         .CommandType = adCmdStoredProc
>         .CommandText = "Create_Upd_Spec_Measure"
>     End With

>     For Each par In qry.Parameters
>         Debug.Print par.Name, par.Type
>     Next

> End Sub



Wed, 09 Oct 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Query a stored procedure to find out its parameters

2. ado recordsets from stored procedures, stored procedures have input parameters

3. Passing Parameters to Crystal Report 5.0 (Stored Procedure Query )

4. Passing Parameters to stored parameter queries using VB 5's Data Controls

5. To Stored Procedure or not to stored procedure

6. To stored procedure or not to stored procedure

7. sub report changes its source stored procedure to main report's stored procedure

8. To stored procedure or not to stored procedure

9. Passing Parameters to Store Procedures

10. Passing a parameter to a stored procedure from VBA

11. ADO: Running a stored procedure that takes parameters (?!?!?)

12. Passing parameter to Stored Procedures from VB

 

 
Powered by phpBB® Forum Software