Search results matching tag 'Scope_Identity'

How to Capture Scope_Identity Value with Server Transfer

Redspringer — Tue, 23 Dec 2008 05:00:00 GMT

Hello all

I'm working on a 2 page form. I want to submit the data on the first page, collect the scope identity, and us it as the id when submitting the rest of the data on page 2. I'm using VB, I have all my vb in one class called "contact" but have given each aspx page its on function to call on submit:

The stuff below works independently of each other, so I'm not looking for syntax issues, but wondering what I need to do to pick up the scope_identity and set it as the id for the second sproc. I can get the sproc to fire alone if I enter in an id name, but just can't get it to submit via the webpage; i get :

Procedure or function 'addContact2' expects parameter '@id', which was not supplied.

Any help is appreciated, code is below. One thing I'm thinking is that I need to change the retval on the second page function to execute a dataset instead of scalar?

CONTACT CLASS

-FIRST PAGE FUNCTION-

Function Save() As Boolean
        Dim retCode As Boolean = False
        Try
            Dim spname As String = "addContact"
            Dim parm(62) As SqlParameter
            Dim iSortOrder As Integer = 0

            parm(1) = New SqlParameter("@Type", _contactType)
            parm(2) = New SqlParameter("@LastName", _lastName)
            parm(3) = New SqlParameter("@FirstName", _firstName)

Dim retVal As Integer
            retVal = SqlLayer.ExecuteScalar(Connection.GetConnection, CommandType.StoredProcedure, spname, parm)

            _contactId = retVal
            retCode = True
        Catch ex As Exception
            Throw ex
        End Try
        Return retCode
    End Function

-SECOND PAGE FUNCTION-

Function Update() As Boolean
        Dim retCode As Boolean = False
        Try
            Dim spname As String = "addContact2"
            Dim parm(24) As SqlParameter
            Dim iSortOrder As Integer = 0

            If Not _isEnglish = Nothing Then
                parm(1) = New SqlParameter("@English", _isEnglish)
            End If

            If Not _englishProficiencyReading Is Nothing Then
                parm(2) = New SqlParameter("@EnglishProficiencyReading", _englishProficiencyReading)
            End If

            If Not _englishProficiencySpeaking Is Nothing Then
                parm(3) = New SqlParameter("@EnglishProficiencySpeaking", _englishProficiencySpeaking)
            End If

            If Not _englishProficiencyWriting Is Nothing Then
                parm(4) = New SqlParameter("@EnglishProficiencyWriting", _englishProficiencyWriting)
            End If

The 2 Sprocs being called look like this:

ALTER PROCEDURE [dbo].[addContact]
    (@Type     [varchar](50)= NULL,
    @AppID    [varchar](20)= NULL,
    @CertainID [varchar] (20) = NULL,
    @Salutation [varchar] (5) = NULL,
    @FirstName     [varchar](50),
    @MiddleName     [varchar](50)= NULL,
    @LastName     [varchar](75)

AS

BEGIN
    INSERT INTO [Contact]
        (
        [Type],
        [AppID],
        [CertainID],
        [Salutation],
        [FirstName],
        [MiddleName],
        [LastName]

    VALUES
    (
        @Type,
        @AppID,
        @CertainID,
        @Salutation,
        @FirstName,
        @MiddleName,
        @LastName

);

SELECT SCOPE_IDENTITY()
END

- and -

ALTER PROCEDURE [dbo].[addContact2]
       (
       @id varchar(20),
       @English int = null,
       @EnglishProficiencyReading varchar(50)= null,
       @EnglishProficiencySpeaking varchar(50)= null,
       @EnglishProficiencyWriting varchar(50)= null

)

AS

BEGIN
   /* Update App with Page 2 info */
   Insert into [Contact]
       (
[ContactId],
       [English],
       [EnglishProficiencyReading],
       [EnglishProficiencySpeaking],
       [EnglishProficiencyWriting]

);

set @id = scope_identity()

END

SELECT @RETURN_VALUE = SCOPE_IDENTITY() - Only Works Sometimes?

jfierst — Wed, 09 Jul 2008 04:00:00 GMT

I have a formview where the new record is created, but only sometimes does it pull the id of the recently inserted record. Can you guys look and tell me whats wrong?

QUERY>>>>>>>>>>>>>>>>>>

<asp:SqlDataSource ID="FormviewSqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SiteDataConnString %>"
InsertCommand="INSERT INTO [COUPLE_REGISTRATION] ([DATE], [CLEARANCE], [BW_NAME1], [BW_NAME2], [BW_ADDY1], [BW_ADDY2], [BW_CITY], [BW_STATE], [BW_ZIP], [BW_PHONE], [BW_EMAIL], [BW_RELAT], [BW_RELIG], [BW_RELAT2], [BW_RELIG2], [BW_RELAT3], [BW_RELIG3], [GH_NAME1], [GH_NAME2], [GH_ADDY1], [GH_ADDY2], [GH_CITY], [GH_STATE], [GH_ZIP], [GH_PHONE], [GH_EMAIL], [GH_RELAT], [GH_RELIG], [GH_RELAT2], [GH_RELIG2], [GH_RELAT3], [GH_RELIG3]) VALUES (@DATE, @CLEARANCE, @BW_NAME1, @BW_NAME2, @BW_ADDY1, @BW_ADDY2, @BW_CITY, @BW_STATE, @BW_ZIP, @BW_PHONE, @BW_EMAIL, @BW_RELAT, @BW_RELIG, @BW_RELAT2, @BW_RELIG2, @BW_RELAT3, @BW_RELIG3, @GH_NAME1, @GH_NAME2, @GH_ADDY1, @GH_ADDY2, @GH_CITY, @GH_STATE, @GH_ZIP, @GH_PHONE, @GH_EMAIL, @GH_RELAT, @GH_RELIG, @GH_RELAT2, @GH_RELIG2, @GH_RELAT3, @GH_RELIG3); SELECT @RETURN_VALUE = SCOPE_IDENTITY()">

PARAMS>>>>>>>>>>>>>>>>>>>>>>>>>>>>

CODE BEHIND>>>>>>>>>>>>>>>>>>>>>>>>

Protected Sub FormviewSqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles FormviewSqlDataSource1.Inserted

Session("couple_id") = e.Command.Parameters("RETURN_VALUE").Value
End Sub

Re: Return the ID of a newly inserted row.

Mr.Bill — Mon, 09 Jun 2008 04:00:00 GMT

Thank you for your response, BUT... I've spent 2 and a half days trying to figure this out... and it's just not getting easier. I've diligently tried the methods listed on this post (and many other blogs/posts) with absolutely no success.

Again, I'm using:
Visual Web Developer 2005 (not VisualStudio);
SQL Server 2005 Express Edition
c# codebehinds
TableAdapter ((.xsd) This is my datasource) I'm open to using any datasource scenario I need to, but I prefer to just build my datasources in the designer. Why? should be obvious.
DetailsView control (with the default set to Insert)

this is my SQL Server 2005 Express Edition stored procedure:

CREATE PROCEDURE [dbo].[Insert_ReturnID]
    @company int,
    @newOrderID int output
AS
BEGIN
    insert into tblOrders (tblOrders.company)
    values (@company)

    set @newOrderID = scope_identity()
END

This is my .xsd Insert statement:

INSERT INTO [tblOrders] ([companyID]) VALUES (@companyID);
SELECT @newOrderID = SCOPE_IDENTITY();

I add another insert parameter to the generated insert parameters on my .aspx:

<asp:Parameter Name="newOrderID" Direction="Output" Type="Int32" />

I believe the codebehind (c#) is where I mess it up... I get error after error. ***Also, it is here that EVERY post I've read gets soft and does not follow through...

I've seen probably 10+ variations on how to retrieve the ID from an insert. My problem is that not one! of those that I've seen gives a complete recipe for success, and each time I try to fill in the blanks on my own ...my builds fail!! I have a project due and this little lesson is costing me dearly.

Your help is greatly appreciated. Thank You.

Re: Returns the primary key of the inserted row.

smcirish — Thu, 15 May 2008 04:00:00 GMT

http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

Here is an example of Scope_Identity with Stored Procedures.

-smc

LINQ question

lost_benny — Tue, 02 Oct 2007 04:00:00 GMT

hi guys,

anyone know the LINQ code to perform the SCOPE_IDENTITY functionality on an insert command?

thanks,

Re: Getting the automatically generated id after SqlDataSource.Insert()

cunharubens — Fri, 24 Aug 2007 04:00:00 GMT

Take a look at this FANTASTIC tutorial:

http://www.asp.net/learn/data-access/tutorial-01-cs.aspx

This tutorial has helped me a lot and I am using it now to build an application for our company.

Here you will see detailed instructions on how to create different queries and retrieve the id using SELECT SCOPE_IDENTITY().

Re: Doing a double insert using FormView insert and SqlDataSource

abhm — Fri, 27 Jul 2007 04:00:00 GMT

Execute scalar, would normally execute the defined sql command. Hence if you put e.Command.ExecuteScalar() in the ..._dataInserted Method of your datasource object or the formview's datainserted event it will cause a second insert.

If you want to use the idendity or scope_idendity() have a look at this sp:

ALTER procedure [news].[cnp_InsertCorporate](@Name varchar(1024), @DisplayName varchar(1024), @Description varchar(1024), @ContactEmail varchar(1024), @NoticeBoardTitle varchar(1024), @ExternalAdminPass varchar(1024), @BFASale bit, @HomeAllSynopses bit, @HomeNumberSynopses int, @HasForex bit, @Newsletter_MoreLinkTitle varchar(1024), @Newsletter_TopLinkTitle varchar(1024))
as

-------------------------------------------------------------------------------
-- cnp_InsertCorporate
--
-- Add new corporate to and based on @@identity insert new group and then news user.
-- Also inserts clients weather & forex if it has weather and forex info.
--
-- Written:
-- 29/07/2007 abhm
-------------------------------------------------------------------------------

-- variables
declare @corpid int;
declare @groupid int;

-- Insert corpoarate details
INSERT INTO [tb_cnpCorporates]
([Name], [DisplayName], [Description], [ContactEmail], [NoticeBoardTitle], [ExternalAdminPass], [BFASale], [HomeAllSynopses], [HomeNumberSynopses], [HasForex], [Newsletter_MoreLinkTitle], [Newsletter_TopLinkTitle])
VALUES
(@Name, @DisplayName, @Description, @ContactEmail, @NoticeBoardTitle, @ExternalAdminPass, @BFASale, @HomeAllSynopses, @HomeNumberSynopses, @HasForex, @Newsletter_MoreLinkTitle, @Newsletter_TopLinkTitle)
-- Get last inserted CorporateID and assign to @corpid
SET @corpid = SCOPE_IDENTITY();

-- Insert group details
INSERT INTO corpnews.news.tb_cnpGroups
([CorporateID], [Name], [GroupEmail])
VALUES
(@corpid, @Name, REPLACE('news-&@email.com','&', @Name) )

Using SELECT SCOPE_IDENTITY in an ad-hoc query

Disco Patrick — Tue, 24 Jul 2007 04:00:00 GMT

I've got an insert command in an SqlDataSource control that looks like this:

InsertCommand="INSERT INTO TAG2(TagText) VALUES (@TagText); SELECT SCOPE_IDENTITY()"

Is this correct? How do I get at the value of the identity so I can use it in another insert command?

Re: Gridview - Detailsivew: How do I get the Gridview to select the last inserted record from the detailsview?

wadewalker25 — Fri, 02 Feb 2007 05:00:00 GMT

Hi Rafa, thanks for the quick reply!

I tried what you said, I translated to VB as the following:

Protected Sub DSDemoInfo_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) Handles DSDemoInfo.Inserted
Session("decRetID") = e.ReturnValue
End Sub

Protected Sub Grid1AllDemo_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles Grid1AllDemo.DataBound
   If Session("RowIndex") <> Nothing Then
      Grid1AllDemo.SelectedIndex = Convert.ToInt16(Session("RowIndex"))
   End If
End Sub

Protected Sub Grid1AllDemo_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles Grid1AllDemo.RowDataBound
   Dim ident As String = CStr(DataBinder.Eval(e.Row.DataItem, "ClientID"))
   If ident <> Nothing And Session("RowIndex") <> Nothing Then
      If ident = Session("decRetID") Then
         Session("RowIndex") = Convert.ToInt16(e.Row.RowIndex)
      End If
   End If
End Sub

It ALMOST does what I want. This code searches through each row as it's bound to the gridview and evaluates whether or not the ID matches my return ID, which is good. However...what it doesn't do is search through each page. I have paging enabled on the GridView. How do I also get this to search through all the pages and THEN search through all the rows, stopping once a match is found?

Thanks very much Rafa for your help!! I would hug you if I could!! :)

Gridview - Detailsivew: How do I get the Gridview to select the last inserted record from the detailsview?

wadewalker25 — Thu, 01 Feb 2007 05:00:00 GMT

How do I make the gridview go to a particular record without using the SelectedIndex?

I have a GridView and DetailsView both on one page. I use Selection on the GridView to pull data from a particular table to populate data on the DetailsView. No problem thus far. However, when I insert a record using the DetailsView and click insert the GridView doesn't automatically select the last record inserted. How do I get that to work? How do I make the Gridview select the last inserted record from the DetailsView?

Now, I know about using SELECT SCOPE_IDENTITY(); I've built an insert routine to return the last inserted ID. But how do I use that information (the last inserted ID) to make the Gridview select that record?

This is killing me!! I've been searching for a week for a solution to this problem and nothing so far. This has had to have been explored right!??

Thank you all in advance!