CODEWITHSUNDEEP
c#asp.net
cunjiva
cunjiva

Reputation: 35

SCOPE_IDENTITY returning wrong ID

I'm attempting to return the ID of the newly created row. I've tried doing it with SCOPE_IDENTITY, but it returns a 3. I've searched around and can't determine what I'm doing incorrect.

Here is the code:

    <asp:SqlDataSource ID="sourceRecruit" runat="server" 
    ProviderName="System.Data.SqlClient"
    ConnectionString="<%$ ConnectionStrings:DBCS %>" 
    SelectCommand="SELECT * FROM [Players] WHERE ([ID] = @ID)" 
    UpdateCommand="UPDATE [Players] SET [FirstName] = @FirstName, [LastName]=@LastName, [Height]=@Height, [Weight]=@Weight, [FortyYardDash] = @FortyYardDash, [CombineVerified] = @CombineVerified, [HomeNumber] = @HomeNumber, [CellNumber] = @CellNumber, [Email] = @Email, [Position] = @Position, [Commitment] = @Commitment, [GraduationYear] = @GraduationYear, [LastUpdate] = @LastUpdate, [NetworkUpdate] = @NetworkUpdate, [DecisionDate] = @DecisionDate, [MiscContact] = @MiscContact, [RivalsID] = @RivalsID, [ScoutID] = @ScoutID, [ESPNID] = @ESPNID, [t247SportsID] = @t247SportsID, [PictureID] = @PictureID, [StarRating] = @StarRating, [NationalHot100Rank] = @NationalHot100Rank, [PositionRank] = @PositionRank, [JanuaryEnrollee] = @JanuaryEnrollee, [Recruiter] = @Recruiter, [RecruitedPosition] = @RecruitedPosition, [SummerCamp] = @SummerCamp, [JuniorDay] = @JuniorDay, [SpringGame] = @SpringGame, [UnofficialVisit] = @UnofficialVisit, [FootballGame] = @FootballGame, [OfficialVisitDate] = @OfficialVisitDate, [Status] = @Status, [Updates] = @Updates, [Favorites] = @Favorites, [Visits] = @Visits, [Odds] = @Odds WHERE [ID] = @ID" 
    InsertCommand="INSERT INTO Players ([FirstName], [LastName], [Height], [Weight], [FortyYardDash], [CombineVerified], [HomeNumber], [CellNumber], [Email], [Position], [Commitment], [GraduationYear], [LastUpdate], [NetworkUpdate], [DecisionDate], [MiscContact], [RivalsID], [ScoutID], [ESPNID], [t247SportsID], [PictureID], [StarRating], [NationalHot100Rank], [PositionRank], [JanuaryEnrollee], [Recruiter], [RecruitedPosition], [SummerCamp], [JuniorDay], [SpringGame], [UnofficialVisit], [FootballGame], [OfficialVisitDate], [Status], [Updates], [Favorites], [Visits], [Odds]) VALUES (@FirstName, @LastName, @Height, @Weight, @FortyYardDash, @CombineVerified, @HomeNumber, @CellNumber, @Email, @Position, @Commitment, @GraduationYear, @LastUpdate, @NetworkUpdate, @DecisionDate, @MiscContact, @RivalsID, @ScoutID, @ESPNID, @t247SportsID, @PictureID, @StarRating, @NationalHot100Rank, @PositionRank, @JanuaryEnrollee, @Recruiter, @RecruitedPosition, @SummerCamp, @JuniorDay, @SpringGame, @UnofficialVisit, @FootballGame, @OfficialVisitDate, @Status, @Updates, @Favorites, @Visits, @Odds); SET @ID=SCOPE_IDENTITY()" 
    OnInserted="reloadPage"> 

    <SelectParameters>
        <asp:QueryStringParameter QueryStringField="ID" Name="ID" Type="Int32" />
    </SelectParameters>
    <UpdateParameters>
        <asp:Parameter Name="DecisionDate" ConvertEmptyStringToNull="true" />
        <asp:Parameter Name="NetworkUpdate" ConvertEmptyStringToNull="true" />
        <asp:Parameter Name="OfficialVisitDate" ConvertEmptyStringToNull="true" />
        <asp:Parameter Name="LastUpdate" ConvertEmptyStringToNull="true" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="ID" DefaultValue="Int32" Direction="Output"  />
        <asp:Parameter Name="DecisionDate" ConvertEmptyStringToNull="true" />
        <asp:Parameter Name="NetworkUpdate" ConvertEmptyStringToNull="true" />
        <asp:Parameter Name="OfficialVisitDate" ConvertEmptyStringToNull="true" />
        <asp:Parameter Name="LastUpdate" ConvertEmptyStringToNull="true" />
    </InsertParameters>
    </asp:SqlDataSource>

Here is my code behind:

    protected void reloadPage(object sender, SqlDataSourceStatusEventArgs e)
{

    object newid    = e.Command.Parameters["@ID"].Value;
    lblResults.Text = "Test "+Convert.ToString(newid);
}

Like I've mentioned, every time I've ran the code, lblResults displays a "3," but the ID is in the 3000 range.

Upvotes: 2

Views: 795

Answers (2)

James Johnson
James Johnson

Reputation: 46067

You need to use the OnInserted event, and specify the output direction of the parameter:

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

Code-behind:

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
    int id = Convert.ToInt32(e.Command.Parameter["@ID"].Value);
}

I also noticed that you set DefaultValue="Int32". You should change that to Type="Int32".

If you still have problems after applying the above changes, try changing SET @ID = SCOPE_IDENTITY to SELECT @ID = SCOPE_IDENTITY. Shouldn't make a difference, but if all else fails...

Upvotes: 2

fahad
fahad

Reputation: 9

Did you try

SELECT @ID = SCOPE_IDENTITY()

instead of

SET @ID = SCOPE_IDENTITY()

that is what I have and works perfect.

Upvotes: 0

Related Questions