Problem with return value of stored procedure when using tableadapters

hello

Could you please help me with this problem?

I have a stored procedure like this:

ALTER PROCEDUREdbo.UniqueChannelName

(

@.UserNamenvarchar(50),

@.ChannelNamenvarchar(50)

)

AS

return5;

Then inside of my dataset, I added a new query(dataset1.QueriesTableAdapter) to handle above mentioned stored procedure. Properties window is showing that return type of this adapter is of type int32 as we expected to be.

now I want to use it inside of my code:

DataSet1TableAdapters.QueriesTableAdapter b =new DataSet1TableAdapters.QueriesTableAdapter();

int i;

i=Convert.ToInt32( b.UniqueChannelName("Ahmad","test"));

as you may guess, the return type of b.UniqueChannelName("Ahmad","test") is object and needs to be type-casted before assigning it's value to i; but even after explicit type casting, the value of i is always set to 0, not 5.

could you please show me the way?

many thanks in advance

There is a bug / undocumented feature / downright bad design in ADO.NET whereby a stored procedure with both output parameters and a dataset to return, will not populate the output parameter until all the dataset has been read.

Try reading the dataset to completion before reading the output parameter, otherwise you will have to split your stored procedure into an output parameter part and a dataset part.

|||

Thanks for your fast response.

I'm new to ado.net and couldn't understand what you mean by reading the dataset. Could you please refer me to an example?

thanks

|||

>I'm new to ado.net and couldn't understand what you mean by reading the dataset.

Try assigning the dataset to the control, databind it and then read the output parameter value.

|||

Thanks, Iwill try it tomorrow.

|||

My code is now like this:

DataSet1TableAdapters.QueriesTableAdapter b =new DataSet1TableAdapters.QueriesTableAdapter();

int i;

GridView2.DataSource = b.UniqueChannelName("Ahmad","test");

GridView2.DataBind();

i=Convert.ToInt32( b.UniqueChannelName("Ahmad","test"));

But the problem persists

|||

Please post your stored procedure.

|||ALTER PROCEDURE dbo.UniqueChannelName(@.UserName nvarchar(50),@.ChannelName nvarchar(50))ASdeclare @.Out intSET NOCOUNT ON;return 5;|||

ALTER PROCEDURE dbo.UniqueChannelName

(

@.UserName nvarchar(50),

@.ChannelName nvarchar(50)

)

AS

declare @.Out int

SET NOCOUNT ON;

//return 5;

SET @.OUT=5

// You can get it using AddOutParameter and getOutParameter method

Do not forget to mark as an answer on the post that helped you.

|||

You need to modify your stored procedure to
ALTER PROCEDURE dbo.UniqueChannelName
( @.UserName nvarchar(50),
@.ChannelName nvarchar(50),
@.Out INT OUTPUT -- To get output from a stored procedure parameter you must define it as such
)AS
SET NOCOUNT ON;
SET @.OUT=5

The calling code will need to change

|||

Thanks

Do you mean I must use Database classs and I can't use dataset for my purpose?Infact I prefred using datasets.

Plus

@.out was a local variable inside my code, can it send data out?

Plus

Later I want to change my stored procedure to a select statement like this:

select @.out=count(Autonumber) from ...

what should I do for returning the value?

again thanks for your incorporation

|||

Ok

nowUniqueChannelName method became like this:

UniqueChannelName(string UserName, string ChannelName, ref int? Out)

but unfortunately I don't know how to use the last parameter. could you please help?

thanks

|||

> Plus @.out was a local variable inside my code, can it send data out?

It has to become an output argument of the stored procedure. A local variable is purely local.

For small datasets selected by a SELECT A, B, C FROM FRED, they can be modified to SELECT @.out AS OUT, A, B, C FROM FRED

If the dataset is small the overhead is minimal, otherwise you need to formally define a Command object with all the arguments.

|||

Dear Allstar,

You gave too much of information to me. thanks alot.

I think if you give me a guide on using that reference variable, my questions will be finished.

Any how, thanks alot.

|||I am looking for an example in an old VS2003 project, but the search is taking longer than I anticipated.