“从SQL Server CE获取@@ Identity时”解析查询时出错“

I'm writing a simple desktop application in which I'm using a local SQL database (SQL Server CE). Here is the problematic section:

我正在编写一个简单的桌面应用程序，我正在使用本地SQL数据库（SQL Server CE）。这是有问题的部分：

SqlCeConnection conn = new SqlCeConnection("Data Source=|DataDirectory|\\App_Data\\Rosters.sdf");
System.Data.SqlServerCe.SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;

cmd.CommandText = String.Format("Insert into Teams (LeagueID, TeamName, Color) values ({0},'{1}','{2}');SELECT @@IDENTITY;", leagueID, txtTeamName.Text.Replace("'", "''"), txtColor.Text.Replace("'", "''"));
conn.Open();
int teamID = (int)cmd.ExecuteScalar();
conn.Close();

The problem is that I'm getting an exception when I call cmd.ExecuteScalar.

问题是我在调用cmd.ExecuteScalar时遇到异常。

The exception message reads,

异常消息显示，

{"There was an error parsing the query. [ Token line number = 1,Token line offset = 97,Token in error = SELECT ]"}

{“解析查询时出错。[令牌行号= 1，令牌行偏移= 97，令牌错误=选择]”}

I have run the exact same command in the exact same database through a direct query, and it runs fine - which makes me think the problem is not with SQL Server CE.

我通过直接查询在完全相同的数据库中运行完全相同的命令，它运行正常 - 这让我觉得问题不在于SQL Server CE。

Any help would be greatly appreciated.

任何帮助将不胜感激。

1 个解决方案

#1

SQL Server Compact only supports a single statement per command, so first run the insert statement with executenonquery, then get the identity with executescalar, and remember not to close the connection in between

SQL Server Compact仅支持每个命令一个语句，因此首先使用executenonquery运行insert语句，然后使用executioncalar获取标识，并记住不要关闭之间的连接