How to call SQL Server stored procedures in Visual Basic?

[Groningen]

Hi,

I was wondering if someone could help me on this one.
I've made a stored procedure in SQL Server Enterprise Manager. When I call the stored procedure in Visual Basic I didn't succeed to return a SQL value.

I'm calling the procedure using the following VB source code:

Dim param1 As ADODB.Parameter
Dim aantal As Integer
Set ADOCMD = New ADODB.Command
frm_importeren.txt_importeren.Text)
Set param1 = ADOCMD.CreateParameter("aantal", adInteger, adParamOutput, 4)
With ADOCMD
.ActiveConnection = connSQL_dw
.CommandType = adCmdStoredProc
.CommandText = "chck_geslacht"
.CommandTimeout = 0
.Parameters.Append param1
.Execute , param1
.Parameters.Refresh
End With
Set ADOCMD = Nothing
Set param1 = Nothing

My stored procedure in SQL server:
CREATE PROCEDURE chck_geslacht @Aantal INT OUTPUT
AS

SELECT Geslachtscode
FROM imptmp_bevpeil
WHERE (imptmp_bevpeil.Geslachtscode NOT IN
(SELECT DISTINCT Codering_Geslacht.Geslachtscode
FROM Codering_Geslacht))
set @Aantal=833
return @aantal
GO

SQL server doesn't return the value I'm expecting (833).

[willakawill]

You must assign the return value of the execute command to a recordset.

Here it is from MSDN:

This example shows the execution of the sp_who SQL Server system stored procedure:
 
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
 
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
 
Cmd.ActiveConnection = cn
Cmd.CommandText = "sp_who"
Cmd.CommandType = adCmdStoredProc
 
Set rs = Cmd.Execute
Debug.Print rs(0)
rs.Close