Use Stored procedures that uses the "EXECUTE" command in a TableAdapter

Question

I am writing a program that needs to call a MSSQL stored procedure called dbo.getsystemnumber; this procedure generates the next pyd_number which I need to insert a new line in the paydetail table. This is what the procedure looks like:

ALTER PROCEDURE [dbo].[getsystemnumber](@p_controlid varchar(8), @p_alternateid varchar(8)) 
AS
    DECLARE @return_number int
    EXECUTE @return_number = dbo.getsystemnumber_gateway @p_controlid, @p_alternateid, 1
    RETURN @return_number

I'm using the c# tableadapter to call this procedure but when I do call it and say put it on a label or listbox, it just returns a value of 0. The function still updates in SQL server to the next number when I run the program.

TMW_Test2DataSetTableAdapters.paydetailTableAdapter returnPydNumber = new TMW_Test2DataSetTableAdapters.paydetailTableAdapter();
lbPydnumber.Items.Add(Convert.ToInt32(returnPydNumber.getsystemnumber("PYDNUM", " ")));

However, when I use the preview data option in the tableadapter view I get the right number that I'm supposed to get. In SQL Server we call it like this:

declare @pyd_number int
execute @pyd_number = dbo.getsystemnumber N'PYDNUM', NULL
select @pyd_number

The two methods of calling are not the same, because one supplies NULL and the other a space. Investigate the behaviour in both cases. — Andy G, Jan 15 '18 at 15:00
Possible duplicate of [C# - using TableAdapter to return a single value from stored procedure returns null](https://stackoverflow.com/questions/5409904/c-sharp-using-tableadapter-to-return-a-single-value-from-stored-procedure-retu) — Rhumborl, Jan 15 '18 at 15:08
@UweKeim can you please tell me why you sent me this type of message? — Ali Azam, Jan 15 '18 at 15:20
@AliAzam it is discouraged to put tags into titles as it look messy and can make the question unclear. See https://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles and "Should I use tags in titles?" in https://stackoverflow.com/help/tagging — Rhumborl, Jan 15 '18 at 15:24
Why are you using TableAdapter? Thats not the right tool even if you make it work. Use ExecuteScalar and please [read this thread](https://stackoverflow.com/questions/706361/getting-return-value-from-stored-procedure-in-c-sharp). — CodingYoshi, Jan 15 '18 at 15:37
@Rhumborl I didn't post this question or include tags in the question's title. So why the message to me? — Ali Azam, Jan 15 '18 at 15:41
the first command in any stored proc should be `set nocount on` — GuidoG, Jan 15 '18 at 16:28

score 0 · Answer 1 · answered Jan 15 '18 at 15:06

TableAdapters use the result of a SELECT statement for their data, not the RETURN value of a query. Under the hood, a single value TableAdapter will use ExecuteScalar(), which takes the first column in the first row of the resultset.

This blog post explains this and a workaround. Either change the store procedure to SELECT the value instead of RETURNing it, or change the generated code to look at the ReturnValue of the database call.

Use Stored procedures that uses the "EXECUTE" command in a TableAdapter

1 Answers1