How to retrieve stored procedure return values from TableAdapter

re: How to retrieve stored procedure return values from TableAdapter

lusay — Wed, 09 Aug 2006 13:55:54 GMT

Good

re: How to retrieve stored procedure return values from TableAdapter

Phil — Mon, 21 Aug 2006 01:39:21 GMT

I have reviewed your example and ask if you could explain how one does this in a web application. I have been pulling out my hair trying to fix this. I have been stuck on this for an entire day. My sql query works fine when tested even using the table adapter "preview data". However, when I try to pull the result into a variable to be displayed in a textbox or any object for that matter on a web form, I experience the problems you detailed in your example. I would greatly appreciate a "translation" of your solution as it relates to a web form. Thank you for sharing your knowledge.

re: How to retrieve stored procedure return values from TableAdapter

youngjoo — Mon, 21 Aug 2006 20:07:24 GMT

Phil,

Are you having problem using Typed Dataset in your web application or is the problem specific to accessing return values from stored procedures in your web application? If you are having trouble using Typed Dataset in generation, there's a great tutorial where you can learn how to build 3-tier application in ASP.NET. Please check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspnettut/html/aspnet_tutorial01_dataaccesslayer_vb.asp and following series articles.

- YoungJoo

re: How to retrieve stored procedure return values from TableAdapter

Caesar G — Wed, 23 Aug 2006 16:26:32 GMT

Hi! Your post is really interesting. What I have found is that although the stored procedure works great, is doesn't update the dataset. After calling the insertTask function and looking for the newly created row in the dataset you realize that it does not exist. If you have a DataGridView connected to the dataset through a databinding component it will not update the view contents. I have to re-fill the dataset with the table adapter in order to update the DataGridView. With a small group of data that's not a problem, but with large amounts of information that would generate performance issues. Calling the reset bindings and some other methods in the databinding (endEdit) component don't work either. Do you have any idea how to refresh the dataset and the datagridview once the row is inserted via the InsertTask function? I'll really appreciate your comments.

re: How to retrieve stored procedure return values from TableAdapter

Lothan — Mon, 11 Sep 2006 09:34:47 GMT

I have read your explaination to retrieve the procedure return value from TableAdapter...I've done almost the same, but in my DataSet.cs (where I should write that partial class of TableAdapter), It does not find any definition for CommandCollection and I can't find why!!
In DataSet.Designer.cs, InitCommandCollection(), I found the CommandCollection Index I need to use and it's there!
I would be very glad if you can give me some hint or clue, which where I can find the answer of the error, saying there is no definition for CommandCollection! and so in my form, saying there is no definition for the method I wrote in that partial class of DataSet Designer..!
Thank you again.

re: How to retrieve stored procedure return values from TableAdapter

Tpm Corrigan — Sat, 23 Sep 2006 22:27:42 GMT

Why not just go with the flow and change the last line of the stored procedure from return scope_identity() to select scope_identity() ? Then ExecuteScalar returns the desired result.

re: How to retrieve stored procedure return values from TableAdapter

Ben — Mon, 25 Sep 2006 18:44:25 GMT

I too have been searching for this solution. Your article provides a good method to retrieve the return code. The only thing that concerns me is accessing the command collection by an index. It looks like the Typed Dataset adds commands to the collection alphabetically with the exception of the default method. If I refer to the command by it's index, and latter add a method to the adapter, the index of my command object could change, right? Is there a different/better way to refer to it without the subtle danger of methods getting reordered in the command collection? Thanks!

re: How to retrieve stored procedure return values from TableAdapter

hamed — Mon, 25 Sep 2006 19:18:42 GMT

Hi! Do you have any workarounds, when using QueriesTableAdapter and not the TableAdapter? In this case, the generated class file doesnt have the stored procedure's CommandCollection[x].Parameters[y].Value property, so i cannot create a proper GetReturnValue object like the one in your solution.

re: How to retrieve stored procedure return values from TableAdapter

John Miller — Thu, 28 Sep 2006 03:09:24 GMT

TasksDataset.cs

In response to the above error -- you should cast the parameter class as the return value. Here is what works:

[ C# ]

namespace WindowsApplication1.TasksDatasetTableAdapters {

public partial class tbTasksTableAdapter

{

public object GetReturnValue(int commandIndex)

{

return ((System.Data.SqlClient.SqlParameter)this.CommandCollection[commandIndex].Parameters[0]).Value;
}

}

}

re: How to retrieve stored procedure return values from TableAdapter

youngjoo — Mon, 02 Oct 2006 19:43:05 GMT

Lothan,

Could you please copy and paste your partial class code?

- YoungJoo

re: How to retrieve stored procedure return values from TableAdapter

JungleBoy — Sun, 15 Oct 2006 07:22:43 GMT

YoungJoo,

Another solution could be add a new line at end of stored procedure using clause SELECT, like this: SELECT SCOPE_IDENTITY().

Then, the method ExecuteScalar return the correct value.

re: How to retrieve stored procedure return values from TableAdapter

geblack — Thu, 14 Dec 2006 00:44:17 GMT

OK, select scope_identity is all well and fine for sql server, how do you go about getting the new pk value when using sqlce? Since batch statements are not allowed (and there is not scope_identity function in sqlce even if you could batch).

I really don't want to have to ride an event to issue another statement to sqlce to get the new value using @@identity.

re: How to retrieve stored procedure return values from TableAdapter

Ray — Fri, 20 Jul 2007 18:55:34 GMT

I am particularly interested in Ben's question about the Procedure Index. Is there a more symbolic way to accomplish the same thing?

Or alternatively could one use a For Next loop comparing the Command.Text to the known procedure name.

For each obj in theCommands

if obj.CommandText = "dbo.sp_InsertTask" then

blah, blah

return

end if

Return Error

re: How to retrieve stored procedure return values from TableAdapter

You really saved my days with this one. — Fri, 10 Aug 2007 12:00:35 GMT

I was on this problem for nearly 2 days.

consider my situation when you get stuck for inserting a record for that long.

I think asp.net guys should put this in their articles section.

Thanks again ...

tableadapter sql variable

tableadapter sql variable — Mon, 21 Apr 2008 10:21:52 GMT

PingBack from http://julio.dishtvnews.com/tableadaptersqlvariable.html

smart retrieve

smart retrieve — Sat, 10 May 2008 19:30:31 GMT

PingBack from http://lillie.freemedianewsnetwork.info/smartretrieve.html

Smart Client Data How to retrieve stored procedure return values from | Hair Growth Products

Tue, 09 Jun 2009 11:17:27 GMT

PingBack from http://hairgrowthproducts.info/story.php?id=5475

Smart Client Data How to retrieve stored procedure return values from | fix my credit

Wed, 17 Jun 2009 05:11:24 GMT

PingBack from http://fixmycrediteasily.info/story.php?id=17155