ExecuteScalar(); With scope_identity() Generating

This question already has an answer here:

Specific cast is not valid, while retrieving scope_identity 4 answers

I've have a form which accept various data (through textboxes and a checkboxlist) and on the click event they insert all the data into a table and selects the scope_identity then store it in a variable to use it in the insertion of the checkboxlist items using a loop into another table

according to many answers and examples this should work perfectly!..but it gives me this error :

Exception Details: System.InvalidCastException: Specified cast is not valid.

Line 66:             int NewBrandId = (int)comm.ExecuteScalar();

Here's my linkbutton method code :

   protected void lnkbtnUploadAndSubmit_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MOODbCenterConnection"].ConnectionString);

        SqlCommand comm = new SqlCommand("INSERT INTO Brands (BrandName, BrandLogo, BrandWebsite, IsBrandVisible) VALUES (@Name, @Logo, @Website, @IsVisible); SELECT scope_identity();", conn);

        comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50);
        comm.Parameters["@Name"].Value = txtbxBrandName.Text;

        comm.Parameters.Add("@Logo", System.Data.SqlDbType.Text);
        comm.Parameters["@Logo"].Value = fileuploadLogo.PostedFile.FileName;

        comm.Parameters.Add("@Website", System.Data.SqlDbType.Text);
        comm.Parameters["@Website"].Value = txtbxWebsite.Text;

        comm.Parameters.Add("@IsVisible", System.Data.SqlDbType.Bit);
        comm.Parameters["@IsVisible"].Value = chkbxIsPublished.Checked;
        conn.Open();


        int NewBrandId = (int)comm.ExecuteScalar(); 

        conn.Close();

        foreach (ListItem li in chkbxlstCuisines.Items)
        {
            if (li.Selected)
            {
                conn.Open();

                SqlCommand comm2 = new SqlCommand("INSERT INTO BrandCuisines (CuisineId, BrandId) VALUES (@CuisineId, @NewBrandId)", conn);

                comm2.Parameters.Add("@CuisineId", System.Data.SqlDbType.Int);
                comm2.Parameters["@CuisineId"].Value = li.Value;

                comm2.Parameters.Add("@NewBrandId", System.Data.SqlDbType.Int);
                comm2.Parameters["@NewBrandId"].Value = NewBrandId;
                comm2.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

Edit Strangely the query works fine when I run it directly in the sql server express of the visual studio!

THE Weird Explanation For This Weird Problem

First Solution by alexb :

I did it :) My sql server 2008 returns System.Decimal packed into 'object'. Try to use System.Convert.ToInt32 instead of cast.

Example : int NewBrandId = System.Convert.ToInt32(comm.ExecuteScalar());

Second Solution by me :

instead of using "SELECT scope_identity();" I used "SELECT BrandId FROM Brands WHERE BrandId = @@Identity;" it gives you even more controlm but I personally prefare the first solution

N.B : "SELECT @@Identity;" would also work just fine

Thanks guys and I hope it help others!

回答1:

What exactly comm.ExecuteScalar() returns? Maybe it returns long or double packed into object.
can't remember exactly, but seems like I had faced something 1-2 years ago.

回答2:

If you can change the SQL, an even easier way would be to

SELECT CONVERT(int, SCOPE_IDENTITY())

回答3:

As Dimi said, Identity is a SQL Numeric (.NET Decimal). This should work:

decimal decimalBrandId = (decimal)comm.ExecuteScalar(); 
int NewBrandId = (int)decimalBrandId;

回答4:

From the documentation for SCOPE_IDENTITY()

Return Types

numeric(38,0)

So an easy solution would be the following

DECLARE @ReturnValue INT;

/*
   Do your insert stuff here.
*/

SELECT @ReturnValue = SCOPE_IDENTITY();
SELECT @ReturnValue;

回答5:

try adding SET NOCOUNT ON; before the insert.