Return to Inserted Record

rit01 · February 13th, 2006, 05:44 PM

Hi

First before the Q I would like to show my appreciation to this forum. I have chosen to learn ASP.NET2 by applying it to a project and I don't think I would be making as quicker progress without your kind generosity.. Many thanks!

Heres the Q...

I have created a formview control that inserts a new record. What I would like to be able to do is once the record has been inserted is to redirect the user to the same formview but in Edit mode on the record they have just inserted.

..Is this possible?.. if so what would be the best method to explore?

Rit

rit01 · February 14th, 2006, 09:16 AM

I have achieved this now by on the Inserted event of the formview in which I used the DataReader object to provide me with the ID of the last record added.

Imar · February 14th, 2006, 04:22 PM

Quick question: are you using stored procedures for the Insert?

If so, you may be better off using an output parameter that returns the new ID of the record you just inserted.

By requerying the database for the latest record ID, you may run into issues on busy sites, where two inserts happen at almost the same time, and you both end up with the "latest ID" from the database....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Only Us by Peter Gabriel (Track 5 from the album: US) What's This?

rit01 · February 15th, 2006, 06:53 AM

HI Imar

Regarding requerying, that did cross my mind.

(*Embarrassed)What is an Output Parameter?(*Embarrassed) Is it an SQL thing or ASP.NET or both?

I have done a search for this but get an array of things.

Many thanks

Rit

rit01 · February 15th, 2006, 07:06 AM

Ah, I think I got it.. I can build it into the stored procedure and then my ASP.Net page can aquire it... just need to work on the syntax for picking up the ID of the new record created for that paticular INSERT.

Many many thanks again!

Rit

Imar · February 15th, 2006, 07:52 AM

If you're using a DataSOurce, you can do something like this:

Protected Sub myDataSource_Inserted(ByVal sender As Object, _
      ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEvent Args) _
      Handles myDataSource.Inserted

    ' Get the return value from the stored procedure.
    Dim id As Integer = Convert.ToInt32(e.Command.Parameters("@returnValue ").Value)
End Sub

in combination with the following DataSource markup:

<asp:SqlDataSource ID="myDataSource" runat="server"
       InsertCommand="SomeSproc InsertCommandType="StoredProcedure"
  <InsertParameters>
    <asp:Parameter Name="returnValue" Type="Int32" Direction="ReturnValue" />
    <asp:Parameter Name="YourField1" Type="Int32" />
    <asp:Parameter Name="YourField2" Type="String" />
  </InsertParameters>
</asp:SqlDataSource>

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

rit01 · February 15th, 2006, 08:32 AM

Brilliant, thanks so much.

Rich

rit01 · February 15th, 2006, 01:32 PM

Hi Imar

I have tried your code but it returns the error:

Code:

Object cannot be cast from DBNull to other types

.. now I have a hunch that its not your code but the way I cam obtaining the output parameter in my stored proc... I have the following, can anyone see anything wrong with it:

Code:

Stored proc stuff...
@fname nvarchar(30),
@lname nvarchar(30),
@returnValue int OUTPUT

AS

INSERT INTO Users (FirstName,LastName) 
    VALUES(@fname,@lname)
    Select @returnValue = @@identity
    GO

Ta
Rit

jbenson001 · February 15th, 2006, 02:03 PM

You are not returning the value:

use:

declare @hold_var int
execute <your proc> @hold_var output

Imar · February 15th, 2006, 02:34 PM

Hi Rit,

You're confusing an output parameter with a return value. In a sproc, there can be only one return value, but multiple output params. In the .NET world, a return value would be the return value of function, while output parameters would be ByRef (out in C#) params. For example:

Public Function GetSomeThing(ByRef userName As String) As Integer

The ByRef userName is the output param, and the (nameless) Integer is the return value.

To return a value (ints only) from a sproc, use RETURN:

INSERT INTO Table (Bla Bla Bla)
RETURN SCOPE_IDENTITY()

Now the sproc will return the Id of the table that you just inserted a record in.

There is no need to declare a return value for the stored procedure; it's just there.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Dead by Pixies (Track 6 from the album: Doolittle) What's This?