Return a parameter count from a SQL Stored Procedure

3 Apr 2006 8:49 PM

goldbond_8

I would like to use ADO.Net to connect to a Stored Procedure in SQL
Server and return a count of parameters from it. I was able to do this
using ADO and VB 6 using the following code:

        ' m_objConn is a database connection

        Set m_objCmd2.ActiveConnection = m_objConn
        m_objCmd2.CommandText = "TestSP"
        m_objCmd2.CommandType = adCmdStoredProc
        'Assign to var
        m_iSPCount = m_objCmd2.Parameters.Count

When I try it with VB.Net, my count is always 0

        m_objConn.Open()
        Dim objCMD As SqlCommand = New SqlCommand()
        objCMD.Connection = m_objConn
        objCMD.CommandText = "TestSP"
        objCMD.CommandType = CommandType.StoredProcedure
        'Assign to var
        Dim icount As Integer = objCMD.Parameters.Count

Does anyone know how to retrieve a count of parameters from a Stored
Procedure?

3 Apr 2006 10:30 PM

Jim Hughes

You can reference ADOX in your VB.Net code and use it to query parameters.
ADO.Net does not have that functionality.

My personal preference is to use a code generation tool like
www.CodeSmith.com to create my stored proc wrappers instead of reinventing
the wheel.

<goldbon***@hotmail.com> wrote in message
Show quote

news:1144097386.388068.200610@j33g2000cwa.googlegroups.com...
>I would like to use ADO.Net to connect to a Stored Procedure in SQL
> Server and return a count of parameters from it. I was able to do this
> using ADO and VB 6 using the following code:
>
>        ' m_objConn is a database connection
>
>        Set m_objCmd2.ActiveConnection = m_objConn
>        m_objCmd2.CommandText = "TestSP"
>        m_objCmd2.CommandType = adCmdStoredProc
>        'Assign to var
>        m_iSPCount = m_objCmd2.Parameters.Count
>
> When I try it with VB.Net, my count is always 0
>
>        m_objConn.Open()
>        Dim objCMD As SqlCommand = New SqlCommand()
>        objCMD.Connection = m_objConn
>        objCMD.CommandText = "TestSP"
>        objCMD.CommandType = CommandType.StoredProcedure
>        'Assign to var
>        Dim icount As Integer = objCMD.Parameters.Count
>
> Does anyone know how to retrieve a count of parameters from a Stored
> Procedure?
>

4 Apr 2006 1:23 AM

Robbe Morris [C# MVP]

..DeriveParameters

Check out the source code for this ADO.NET Code Generator.

http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp

<goldbon***@hotmail.com> wrote in message

Show quote

news:1144097386.388068.200610@j33g2000cwa.googlegroups.com...
>I would like to use ADO.Net to connect to a Stored Procedure in SQL
> Server and return a count of parameters from it. I was able to do this
> using ADO and VB 6 using the following code:
>
>        ' m_objConn is a database connection
>
>        Set m_objCmd2.ActiveConnection = m_objConn
>        m_objCmd2.CommandText = "TestSP"
>        m_objCmd2.CommandType = adCmdStoredProc
>        'Assign to var
>        m_iSPCount = m_objCmd2.Parameters.Count
>
> When I try it with VB.Net, my count is always 0
>
>        m_objConn.Open()
>        Dim objCMD As SqlCommand = New SqlCommand()
>        objCMD.Connection = m_objConn
>        objCMD.CommandText = "TestSP"
>        objCMD.CommandType = CommandType.StoredProcedure
>        'Assign to var
>        Dim icount As Integer = objCMD.Parameters.Count
>
> Does anyone know how to retrieve a count of parameters from a Stored
> Procedure?
>

4 Apr 2006 3:25 PM

goldbond_8

Thanks Robbe

The .DeriveParameters worked perfectly.