Printable View
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
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
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 ?
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