Retrieve newly inserted record ID?

Discussion in 'ASP .Net Web Controls' started by 11D.Universe, Jun 11, 2007.

  1. 11D.Universe

    11D.Universe Guest

    I'm using the Insert button in a DetailsView to insert a record into a
    SQL table. I've got part of what I need to recover the record ID...

    The INSERT COMMAND is:
    InsertCommand="INSERT INTO [inventory] ([Active], ...) VALUES
    (@Active...); select SCOPE_IDENTITY();"

    The Select Command is:
    SelectCommand="SELECT * FROM [inventory] where [inventoryID] =
    @identity ORDER BY [InventoryID] DESC"

    The Insert Parameters are:
    <InsertParameters>
    <asp:parameter Name="Active" Type="Boolean" />
    ...
    <asp:QueryStringParameter Name="@identity"
    Direction="Output" Type="Int32" />
    </InsertParameters>

    The Select Parameters are:
    <asp:parameter Name="identity" Type="Int32" />

    I know I'm supposed to put some code in the SqlDataSource1_Inserted
    sub, but not sure what code is needed to get the InventoryID from the
    inserted record.

    Am I on the right track??

    Any help would be appreciated...
    11D.Universe, Jun 11, 2007
    #1
    1. Advertising

  2. 11D.Universe

    Riki Guest

    "11D.Universe" <> wrote in message
    news:...
    > I'm using the Insert button in a DetailsView to insert a record into a
    > SQL table. I've got part of what I need to recover the record ID...
    >
    > The INSERT COMMAND is:
    > InsertCommand="INSERT INTO [inventory] ([Active], ...) VALUES
    > (@Active...); select SCOPE_IDENTITY();"
    >
    > The Select Command is:
    > SelectCommand="SELECT * FROM [inventory] where [inventoryID] =
    > @identity ORDER BY [InventoryID] DESC"
    >
    > The Insert Parameters are:
    > <InsertParameters>
    > <asp:parameter Name="Active" Type="Boolean" />
    > ...
    > <asp:QueryStringParameter Name="@identity"
    > Direction="Output" Type="Int32" />
    > </InsertParameters>
    >
    > The Select Parameters are:
    > <asp:parameter Name="identity" Type="Int32" />
    >
    > I know I'm supposed to put some code in the SqlDataSource1_Inserted
    > sub, but not sure what code is needed to get the InventoryID from the
    > inserted record.
    >
    > Am I on the right track??


    Yes, you are.

    Put this code in SqlDataSource1_Inserted (VB.NET):

    If (e.Exception IsNot Nothing) Then
    ' log error or show error
    e.ExceptionHandled = True
    Return
    End If
    Dim InventoryID As Integer =
    CInt(e.Command.Parameters("@identity").Value)

    You may have to modify your INSERT command to match the parameter:
    select SCOPE_IDENTITY AS @identity;

    Riki
    Riki, Jun 12, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi

    Get newly created record's ID??

    David Lozzi, Dec 7, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    400
    Jacco Schalkwijk
    Dec 7, 2004
  2. Craig
    Replies:
    1
    Views:
    1,888
    =?Utf-8?B?SmFtZXM=?=
    Jan 25, 2006
  3. Patrick.O.Ige
    Replies:
    0
    Views:
    527
    Patrick.O.Ige
    Oct 31, 2007
  4. fig000
    Replies:
    0
    Views:
    622
    fig000
    Sep 6, 2008
  5. Craig
    Replies:
    0
    Views:
    135
    Craig
    Jan 2, 2006
Loading...

Share This Page