Return a parameter count from a SQL Stored Procedure

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?

Jim
Mon Apr 03 17:30:32 CDT 2006

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.

<goldbond_8@hotmail.com> wrote in message
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?
>

Robbe
Mon Apr 03 20:23:40 CDT 2006

.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





<goldbond_8@hotmail.com> wrote in message
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?
>

goldbond_8
Tue Apr 04 10:25:11 CDT 2006

Thanks Robbe

The .DeriveParameters worked perfectly.