SCOPE_IDENTITY返回错误的ID

本文关键字：ID 错误 返回 IDENTITY SCOPE | 更新日期: 2023-09-27 18:10:59

我试图返回新创建行的ID。我试过用SCOPE_IDENTITY做它，但它返回一个3。我搜索了一下，不能确定我做错了什么。

代码如下:

    <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>
下面是我的代码: 
    protected void reloadPage(object sender, SqlDataSourceStatusEventArgs e)
{
    object newid    = e.Command.Parameters["@ID"].Value;
    lblResults.Text = "Test "+Convert.ToString(newid);
}

就像我提到的，每次我运行代码时，lblResults显示一个"3"，但ID在3000范围内。

SCOPE_IDENTITY返回错误的ID

需要使用OnInserted事件，并指定参数的输出方向:

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

后台代码:

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

我还注意到您设置了DefaultValue="Int32"。你应该把它改成Type="Int32"。

如果应用上述更改后仍有问题，请尝试将SET @ID = SCOPE_IDENTITY更改为SELECT @ID = SCOPE_IDENTITY。应该没什么区别，但如果其他方法都失败了…

你试过了吗

SELECT @ID = SCOPE_IDENTITY()
不是

SET @ID = SCOPE_IDENTITY()

这就是我所拥有的，并且工作完美。

相关文章：