Return Value (Urgent)

Hello....

I wrote a stored procedure and at the bottom of SP I wrote

IF @@rowcount=1
return 0
else
begin
return 50002
end

I wanna have the value whether is 0 or 50002 value in Visual Basic.

How can I have this values in VB, I appreciate if you guys help me out

Thankx

[Johnny101]

If this is all that is returned from your sp, you might be able to use the recordset object

dim rs as adodb.recordset

set rs = cn.execute(YourStoreProc)

msgbox rs(0) 'might be the return code.



on the other hand if your proc returns a resultset and you still want this return value, but dont' want to use the Command object, then use the NextRecordset method of the recordset. - it should be there.

dim rs as adodb.recorset

set rs = cn.execute(YourStoredProc) 'the main resultset

'once your done with the original, call this method to get the other recordset
set rs = rs.nextrecordset

msgbox rs(0)




or you can use the command object (recommended)


dim cmd as adodb.command

with cmd
.commandtype = adStoredProcedure
.commandtext = "yourstoredprocedure"
.commandtimeout = 600 '10 minutes
.parameters.append .createparameter("RetVal",adLongInteger,adParamReturnValue)
.execute
end with

msgbox cmd.parameters("RetVal")




one of these should work for yuo.

hope this helps,

John



John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

Johnny thanks for reply but i have one problem here

My SP is :

CREATE PROCEDURE byroyalty
@percentage int,
@ioparm int OUTPUT
AS


select * from titleauthor
where titleauthor.royaltyper = @percentage

if @@rowcount = 1
-- SELECT @ioparm = 00
RETURN 00
else
begin
SELECT @ioparm = 88
RETURN 88
end

My Vb program is:

Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim param As Parameter
Dim PATH_Pma01

Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "byroyalty 100" 'stored procedure name and the value passing

Cmd.CommandType = adCmdStoredProc

Set param = Cmd.CreateParameter("myinput", adinteger, adParamInput, 12 ")

Cmd.Parameters.Append param

Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0)
Cmd.Parameters.Append param

Set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)
Cmd.Parameters.Append param

Set rs = Cmd.Execute
If Not rs.EOF And Not rs.BOF Then
Debug.Print rs(0)
Debug.Print rs(1)
Debug.Print rs(2)
rs.Close
End If
Debug.Print Cmd(0) ' The return code
Debug.Print Cmd(1) ' The Output parameter

Set Cmd = Nothing

run time error '-2147217900 (80040e14)
Microsoft ODBC SQL Server Driver Syntax error or access violation

I forgot to write, the above error message in my previous post

[Johnny101]

I forgot to mention - sorry - the Return Value parameter MUST be the first one in the collection. You have to create it and append it first, and then do the rest of them. I'm not sure if that error is caused by this, but i just remembered. If this is the cause, then this should fix it, and all should be well.

Good luck,

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

completly lost....
i did not understand why that error comes.....

let me know, if you have input parameters, how do u pass ?

[Johnny101]

I'm not sure why you are getting that error either, but your VB code should look something like this:

Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "byroyalty 100" 'stored procedure name and the value passing

Cmd.CommandType = adCmdStoredProc

'the ReturnValue must be the first parameter
set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0)
Cmd.Parameters.Append param

set param = Cmd.CreateParameter("myinput", adinteger, adParamInput, 12 ")
Cmd.Parameters.Append param

set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)
Cmd.Parameters.Append param

Cmd.Execute

MsgBox Cmd.Parameters("Return")




if that doesn't work, then I'm out of ideas, but it should. If it doesn't, then there is some other problem at work here, one that i cant see.


John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

Thankx a lot johnny.

I appreciate ur help

Have a nice day