Output parameters & Return Values 1

[salvatoredoria]

i have a stored procudere in SQL Server and i have this problem:
The stored procedure has a return value (@codice0) that can be read with sql analyzer, but i don't know how to get the value in visual basic.
for example:
'
in Query Analyzer
'
declare @myid int
EXEC @myid=sp_Class001_Insert 01,'AGHI','AGHI',4,20040525,1227,0
SELECT @myid
'
returns the value
'
' In Visual Basic
'
Myconn.execute "sp_Class001_Insert,'AGHI','AGHI',4,20040525,1227,0"
with this statement i don't know how to get the return value.

Thanks to all

 

[tb]

Use a recordset ...
strSQL="sp_Class001_Insert,'AGHI','AGHI',4,20040525,1227,0"

Rs.open strSql,Myconn,adOpenForwardOnly, adLockReadOnly

Debug.Print Rs.Fields(0).value

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[salvatoredoria]

thank you for the hint.
But after executing the recordset it's been closed.
Any Other idea

 

[salvatoredoria]

thank you for the hint.
But after executing the recordset it's been closed.
Any Other idea ?

 

[tb]

What do you mean closed... does it give you an error or does on the rs.open .... or does it give you an error if you try to get the .fields(0).value ???

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[salvatoredoria]

when i try ro get the .fields(0).value.

 

[tb]

If you only execute your stored proc in Q-Analyser without the declare @myid ... do you get results?

Try this ...

strSql = "declare @myid int EXEC @myid=sp_Class001_Insert 01,'AGHI','AGHI',4,20040525,1227,0 SELECT @myid"

Rs.open strSql,Myconn,adOpenForwardOnly, adLockReadOnly

Debug.Print Rs.Fields(0).value

If this works then you might consider including the @myid as an output paramter in your stored proc. (See BOL under output parameters .. lots of examples)

Cheers







I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[salvatoredoria]

Thank you it worked pretty good you are a genius!!!!