试图通过最近的ID与scope_identity，它的错误我:指定的转换是无效的

Question

我试图通过最近的ID，我刚刚插入Scope_Identity，它给了我一些奇怪的东西。上面写着:

:指定的类型转换无效

和指向行:

int IdOfRecentHistoryGame = (Int32)commandquery.ExecuteScalar();

这是剩下的代码:

string username = Session["Session"].ToString();
            con = new SqlConnection("Data Source=MICROSOF-58B8A5\SQL_SERVER_R2;Initial Catalog=Daniel;Integrated Security=True");
            con.Open();
            string knowWhichOne = "SELECT ID FROM Users WHERE Username='" + UserOrGuest.Text + "'";
            SqlCommand comm = new SqlCommand(knowWhichOne, con);
            int userID = (Int32)comm.ExecuteScalar();
            knowWhichOne = "SELECT ClassID FROM Users WHERE Username='" + UserOrGuest.Text + "'";
            comm = new SqlCommand(knowWhichOne, con);
            int classID = (Int32)comm.ExecuteScalar();
            knowWhichOne = "SELECT SchoolID FROM Users WHERE Username='"+UserOrGuest.Text + "'";
            comm = new SqlCommand(knowWhichOne, con);
            int schoolID = (Int32)comm.ExecuteScalar();
            if (RadioWords.Checked == true)
            {
                 game = 1;
            }
            else
            {
                 game = 2;
            }
            string arr = "";
            for (int i = 0; i < ListBox.Items.Count; i++)
            {
                arr += ListBox.Items[i] +",";
            }
            string sqlqueryString = "INSERT INTO HistoryOfGames (GameID, UserID, LengthOfArray, NumberOfErrors, ClassID, SchoolID,Arrayarray) VALUES (@GameID, @UserID, @LengthOfArray, @NumberOfErrors, @ClassID, @SchoolID, @Arrayarray);" + "SELECT SCOPE_IDENTITY()";
            SqlCommand commandquery = new SqlCommand(sqlqueryString, con);
            commandquery.Parameters.AddWithValue("GameID", game);
            commandquery.Parameters.AddWithValue("UserID", userID);
            commandquery.Parameters.AddWithValue("LengthOfArray", HowMany.Text);
            commandquery.Parameters.AddWithValue("NumberOfErrors", 0);
            commandquery.Parameters.AddWithValue("ClassID", classID);
            commandquery.Parameters.AddWithValue("SchoolID", schoolID);
            commandquery.Parameters.AddWithValue("Arrayarray", arr);
            commandquery.ExecuteNonQuery();


            int IdOfRecentHistoryGame = (Int32)commandquery.ExecuteScalar();
            con.Close();
            Response.Redirect("NowPlay.aspx?ID="+ IdOfRecentHistoryGame);

有人知道吗?

谢谢!

Answer 1

SCOPE_IDENTITY()返回numeric(38,0)，因为IDENTITY可以定义在int以外的类型的列上(参见SQL Server - IDENTITY列支持哪些数据类型)。

ExecuteScalar返回decimal作为object，不能直接转换为int。相反，先转换为decimal，然后转换为int:

int IdOfRecentHistoryGame = (Int32)(Decimal)commandquery.ExecuteScalar();

Answer 2

SCOPE_IDENTITY()显然在SQL server中实际上没有返回Int。我不知道它不能像你想的那样铸造，但显然它不能。

下面的链接说，如果源列是一个整数，你不应该强制转换它，但无论如何这是值得一试的-

如何将Scope_Identity()转换为Int?显示了如何在SQL查询中强制转换它以获得Integer:

(由Jose Basilio提供)

DECLARE @NewIdent Int
SET @NewIdent = SCOPE_IDENTITY()

试着将变量赋值给它的输出。