How to get return value back from Sql server

[manjulam]

I am using a SP to update rows in a table. Inside the SP i am checking for @@ERROR and returning the error number if any error occured or else returning 0. But when i execute the SP from .NET with erroneous data,it causes exception.
Is there a way to get the return code from the SP instead?

Regards,
Manjula

 

[NoCoolHandle]

Yes not too difficult if you are using a command object..

You just need to declare a paramater with a type=returnparam ..

For some templates goto

http://www.lynchtek.com/sql.aspx

I wrote a little page that takes a sp deff and then spins off the VB.Net code to use it.

Make sure that the first words are "Create Proc "
and ensure there are no "go"'s at the end as this will blow up the page

(you really don't need any content after the "as" but I think I do check if there are at least 4 words afet the as)

Hope it helps

Rob

PS just to test it place the following code inside the window and you should see it work.

   create proc abc
    @x int,
    @y int output
as 
    set nocount on
    select 1-3

 

[AtomicChip]

lol... sorry... had to...

If you go to

http://www.lynchtek.com/sql.aspx

and just click the button, you get an object reference not set to an instance of an object error

-----------------------------------------------

http://www.liquidfuzion.com

"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[manjulam]

Thanks everyone. I got it working.
I had to define the command type as stored procedure and then add all the parameters including one for the return value(direction is return value). Then it worked. If you give a sql like "updatesql 1,2,3" to the command object,it does not return the value. Also, you have to close the connection and then try to get the return code.

 

[NoCoolHandle]

AtomicChip.. Thanks! Hadn't tried that

I will try to fix it tomorrow


Rob