Trying to pass recent ID with scope_identity, and

Question

Im trying to pass the recent ID that I just insert with Scope_Identity, and its giving me some weird stuff. it says :

: Specified cast is not valid

and refering to the line :

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

this is the rest of the code :

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);

somebody has a clue ?

Thanks!

Answer 1

SCOPE_IDENTITY() apparently doesn't actually return an Int in SQL server. I didn't know it couldn't be cast like you tried to, but apparently it can't.

The below link says that if the source column is an integer you shouldn't have to cast it at all, but this is worth a shot at any rate -

How do I cast Scope_Identity() to Int? shows how to cast this within the SQL query in order to get an Integer out:

(Courtesy of Jose Basilio)

DECLARE @NewIdent Int
SET @NewIdent = SCOPE_IDENTITY()

Try assigning your variable to the output of that.

Answer 2

SCOPE_IDENTITY() returns numeric(38,0) since an IDENTITY can be defined on columns of types besides int (see SQL Server - What are the data types supported in IDENTITY columns?).

ExecuteScalar is returning a decimal as an object, which you cannot cast directly to int. Instead, cast to decimal first, then to int:

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