need some help please getting a return value

merk · September 25th, 2007, 07:28 PM

Hello,

This seems like it's something that should be pretty basic and simple. I have an object datasource linked to a detailsview. Everything is working except i cant get the return value after doing an insert.

My sproc has:

declare @id int
do my db insert
set @id = scope_identity()
return @id

I ran this manually in QA and it returned the ID of the records so I'm pretty sure my sql is correct.

But nothing i do will give me that value in my aspx page.

I've tried adding a parameter with a direction of ReturnValue to the objdatasrc but when I did that it complained that there was no method that matched that parameter collection, even though there IS a return value parameter in my insert method.

When i try using the objdatasrc inserted method and using e.ReturnValue, i get all sorts of crap. I thought that's supposed to return a single value, the return value. But when i dub that and step into it, i'm seeing all my columns.

Does anyone have some simple code which shows the sql query, the insert method linked to that sql and the objdatasource code to insert and get the return value?

This one has been driving me nuts for a few days ... i cant figure out what i am doing wrong.

thanks in advance for any advice/help/bullet to the head.

dparsons · September 26th, 2007, 09:35 AM

Can you show us the code that you are using?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

merk · September 26th, 2007, 11:20 AM

it's mainly code that VS generated (VS2008).

Here's the chunk of relevant code for the datasource:

Code:

<asp:ObjectDataSource ID="obdsNonTVDub" runat="server" 
                InsertMethod="InsertNonTVDubReq" OldValuesParameterFormatString="original_{0}" 
                SelectMethod="GetDataByID" 
                TypeName="adTrackingTableAdapters.dtNonTVDubTableAdapter" 
                oninserted="obdsNonTVDub_Inserted1">
                <SelectParameters>
                    <asp:querystringparameter Name="id" QueryStringField="id" Type="Int32" />
                </SelectParameters>
                <InsertParameters>
                    <asp:parameter Name="RETURN_VALUE" Direction=ReturnValue
                    <asp:parameter Name="TelNumber" Type="String" />
                    <asp:parameter Name="VDN" Type="String" />

I've tried it with and without the return parameter defined. The above code fails when i include the return parameter saying there's no method that matches, even though my insert does have a return parameter in it's parameter collection.

And my C# in trying to get the return param is:

Code:

    protected void obdsNonTVDub_Inserted1(object sender, ObjectDataSourceStatusEventArgs e)
    {
        int x = (int)e.ReturnValue;
    }

thanks

mielniczuk · October 3rd, 2007, 06:53 PM

I find it helpful to see what the ObjectDataSource_Inserted event (objCurrListing in code below) is sending by adding a label (lbResult) to the aspx page and the following the code:

protected void objCurrListing_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
        {

         lbResult.Text = String.Format("Affected Rows = {0}. Generated Id = {1}", e.AffectedRows, (int)e.ReturnValue);

            int n = (int)e.ReturnValue;
// do something with the above

The aspx page also has the following as part of the ObjectDataSource
OnInserted="objCurrListing_Inserted".

A. Simon Mielniczuk
ITScooperative
Toronto CANADA

merk · October 3rd, 2007, 07:10 PM

I will give that a try next time. I suspect it will say it cant convert e.returnvalue to an int.

I managed to work around this problem by cheating. There's one piece of data that's unique to each record besides the ID. So i just do a simple select filtering on that one piece of data to get the ID of the record. I really hate doing that since its a 2nd database call that's not needed. But for now it does what i want and performance isn't an issue on this page since its only going to be used internally by a handful of people. It just bugs me not knowing how to do it the right away.

thanks for the suggestion