为什么执行插入语句后该命令会获得@@Identity？

发布于 2024-11-28 22:36:05 字数 1292 浏览 0 评论 0原文

只是尝试执行插入，然后获取@@Identity，但事实证明有点困难。

所以我有以下 Sql：

    int retInt = 0;

    string Sql = insertSql;

    Connection.Open();

    commandArg.CommandText = insertSql;

    commandArg.Connection = Connection;

    commandArg.ExecuteNonQuery();

    commandArg.CommandText = "SELECT @@Identity;";

    commandArg.CommandText = Sql;

    retInt = (int)commandArg.ExecuteScalar();

    return retInt;

但是当我最后到达 ExecuteScalar 命令时，我得到一个空引用异常。我对 csharp 做错了什么吗？因为当我在 Sql 编辑器上执行此操作时，它可以工作（当我在线执行这两个命令时）。

希望有人可以提供帮助....

（顺便说一句，commandArg 作为参数传入）

更新：

我原来的帖子不正确，但我已经更改，但仍然收到错误。希望有人可以帮忙！

更新2：

读完下面的内容后，我可以看到我的代码很草率，所以我将代码修复为这个并且它有效！...

SqlCeConnection myConnection = new SqlCeConnection(@"Data Source=C:\database.sdf");
            SqlCeCommand myCommand = new SqlCeCommand();
            myCommand.Connection = myConnection;

            myConnection.Open();

            myCommand.CommandText = "Insert into tbl_media (dateadded) values (getdate())";
            SqlCeDataReader insertReader =  myCommand.ExecuteReader();

            myCommand.CommandText = "select @@identity";
            SqlCeDataReader identityReader = myCommand.ExecuteReader();

            myConnection.Close();

原文

Just trying to perform an insert and then get the @@Identity but its proving a little difficult.

So I have the following Sql:

    int retInt = 0;

    string Sql = insertSql;

    Connection.Open();

    commandArg.CommandText = insertSql;

    commandArg.Connection = Connection;

    commandArg.ExecuteNonQuery();

    commandArg.CommandText = "SELECT @@Identity;";

    commandArg.CommandText = Sql;

    retInt = (int)commandArg.ExecuteScalar();

    return retInt;

But when I get to the ExecuteScalar command at the end I get a null reference exception.Am I doing something wrong with the csharp? Because when I do this on a Sql editor it works (when I execute both commands in line).

Hope someone can help....

(btw commandArg is passed in as a parameter)

UPDATE:

my original post was not right but I have changed and am still getting the error. Hope someone can help!

UPDATE 2:

After reading the below I can see my code is just plain sloppy so I fixed the code to be this and it works!...

SqlCeConnection myConnection = new SqlCeConnection(@"Data Source=C:\database.sdf");
            SqlCeCommand myCommand = new SqlCeCommand();
            myCommand.Connection = myConnection;

            myConnection.Open();

            myCommand.CommandText = "Insert into tbl_media (dateadded) values (getdate())";
            SqlCeDataReader insertReader =  myCommand.ExecuteReader();

            myCommand.CommandText = "select @@identity";
            SqlCeDataReader identityReader = myCommand.ExecuteReader();

            myConnection.Close();

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

有木有妳兜一样 2024-12-05 22:36:05

我一直在单独的查询中使用@@identity。重要的是它位于同一个连接上。正如之前指出的，您没有执行“SELECT @@Identity;”。您将值设置为 commandArg.CommandText = Sql;在您执行标量之前。使用 retInt = commandArg.ExecuteNonQuery();并查看是否 retInt = 1。将其放入 try catch finally 块中，以便您可以读取异常消息。这是有道理的，你在最后一行得到一个空值，因为插入不是一个标量命令。

回复收藏 0 原文

过度放纵 2024-12-05 22:36:05

听起来您正在尝试获取最近插入的项目的标识列。

鉴于：

SQL CE 只会让您每个命令执行一个语句
IDENTITY 超出范围，
您不能使用存储过程

您可以执行插入，然后查询适合插入记录的最后一条记录：

 SELECT TOP 1 ID FROM MyTable
  WHERE Name = @CustomerName
 /* AND all your other matching criteria for the entity from your INSERT */
 ORDER BY CreateDate DESC

Sounds like you're trying to get the identity column for the item recently inserted.

Given that :

SQL CE willonly let you execute one statement per command
IDENTITY is out of scope
you can't use stored procs

You could execute your insert, and then query for the last record that fits your inserted record:

 SELECT TOP 1 ID FROM MyTable
  WHERE Name = @CustomerName
 /* AND all your other matching criteria for the entity from your INSERT */
 ORDER BY CreateDate DESC

回复收藏 0 原文