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.CreatePa rameter("aantal ", adInteger, adParamOutput, 4)
With ADOCMD
.ActiveConnecti on = connSQL_dw
.CommandType = adCmdStoredProc
.CommandText = "chck_gesla cht"
.CommandTimeout = 0
.Parameters.App end param1
.Execute , param1
.Parameters.Ref resh
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_Geslac ht.Geslachtscod e
FROM Codering_Geslac ht))
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