SCOPE_IDENTITY()无法工作

Question

当我尝试通过SQL Server获取最后插入的ID时，它返回__Page。以下是我的代码：

query = "INSERT INTO
             seekers(name, sname, lname, status, gender, dob, major, experince,
                     email, password, phone, valid, city)
             values (@name, @sname, @lname, @status ,@gender, @dob, @major,
                     @exp, @email, @password, @phone, 0, @city);
         SELECT SCOPE_IDENTITY();";

// some code here related to parameters

command = new SqlCommand(query, connection);
int id = Convert.ToInt32(command.ExecuteScalar());

- Hatem

---

只需读取返回值，而无需将其转换为“int”，即可查看从数据库中实际获取的内容。 - Nikola Radosavljević

Answer 1

如果你只是想要查询，使用OUTPUT；

INSERT INTO seekers(name,sname,lname,status,gender,dob,major,experince,email,password,phone,valid,city)
OUTPUT INSERTED.IDENTITY_COL_NAME
values(@name,@sname,@lname,@status,@gender,@dob,@major,@exp,@email,@password,@phone,0,@city);

- Alex K.

Answer 2

当使用ExecuteScalar时，如果第一条执行语句是INSERT，它会返回插入记录的数量。

为了避免这种情况，请使用下面所示的SET NOCOUNT ON：

string Query = @"SET NOCOUNT ON;
INSERT INTO table1(name) values('ttt');
SELECT SCOPE_IDENTITY(); SET NOCOUNT OFF;
";

- madhav Bilagi

Answer 3

您可以编写如下的存储过程：

CREATE PROCEDURE [seekers_Insert]
 @ID uniqueidentifier OUTPUT,
 @name nvarchar(50) ,
  @sname nvarchar(50),

(other input parameters)
  AS
  INSERT INTO seekers(name,sname,lname,status,gender,dob,major,experince,email,
                         password,phone,valid,city)
                  values(@name,@sname,@lname,@status,@gender,@dob,@major,@exp,@email,
                         @password,@phone,0,@city)

   SET @ID=SCOPE_IDENTITY()
    GO

然后你可以拿走它

int ID = Convert.ToInt32(Command.ExecuteScalar());

- CocLn

Answer 4

我们这样做很好，但是我们在存储过程中使用返回参数，例如：

@RecordID int OUTPUT

...这里是插入语句

SET @RecordID= SCOPE_IDENTITY();

...

SELECT @RecordID

- Davide Piras