sql-server - 无法在 AspNetUsers 表中插入用户

Question

我正在尝试使用存储过程填充 AspNetUsers 表，但出现以下错误:

Msg 515, Level 16, State 2, Procedure Insert_Users, Line 36 Cannot insert the value NULL into column 'Id', table 'MyDatabase.dbo.AspNetUsers'; column does not allow nulls. INSERT fails.

我在其他一些帖子中读到，列 Id 上应该有以下属性: [DatabaseGenerated(DatabaseGeneratedOption.Identity)] ，但我无法找到实际声明的位置。

我不想手动插入所有用户，因为我没时间了，我需要尽快完成。

BEGIN
SET NOCOUNT ON;
DECLARE @id uniqueidentifier
DECLARE @email nvarchar(256)
DECLARE @emailconfirmed bit
SET @emailconfirmed = 0
DECLARE @twofactorenabled bit
SET @twofactorenabled = 0
DECLARE @lockoutenabled bit
SET @lockoutenabled = 1
DECLARE @accessFailed bit
SET @accessFailed = 1
DECLARE @username nvarchar(256)
DECLARE @fname nvarchar(50)
DECLARE @lname nvarchar(50)

DECLARE @athleteKey int
SET @athleteKey = 41809

DECLARE @atheletsCount int
SET @atheletsCount = 0;
SET @atheletsCount = (SELECT COUNT(*) FROM [BIBD].[dbo].[Athlete])

WHILE @athleteKey < @atheletsCount
    SET @id = NEWID() --Line 36
    print CAST(@id AS nvarchar(128))
    SET @fname = (SELECT FirstName FROM [BIBD].[dbo].[Athlete] where AthleteKey=@athleteKey)
    SET @lname = (SELECT LastName FROM [BIBD].[dbo].[Athlete] where AthleteKey=@athleteKey)
    SET @username = CONCAT(LOWER(@fname),'.',LOWER(@lname))
    SET @email = CONCAT(LOWER(@fname), '.', LOWER(@lname), '@gmail.com')

    INSERT INTO [MyDatabase].[dbo].[AspNetUsers]
       (Id
       ,Email
       ,EmailConfirmed
       ,[TwoFactorEnabled]
       ,LockoutEnabled
       ,AccessFailedCount
       ,UserName
       ,FirstName
       ,LastName)
     VALUES
           (CAST(@id AS nvarchar(128))
           ,@email
           ,@emailconfirmed
           ,@twofactorenabled
           ,@lockoutenabled
           ,@accessFailed
           ,@username
           ,@fname
           ,@lname)

    IF @athleteKey % 5 = 0
        INSERT INTO [MyDatabase].[dbo].[AspNetUserRoles]
                    (UserId,RoleId) VALUES (@id, 3)
    ELSE
        INSERT INTO [MyDatabase].[dbo].[AspNetUserRoles]
                    (UserId,RoleId) VALUES (@id, 4)

    SET @athleteKey = @athleteKey+1
END

Answer 1

您需要在 WHILE 循环中使用 BEGIN-END。
否则它只是不为@id 赋值并且不处理循环