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范围内。
需要使用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()
这就是我所拥有的,并且工作完美。