ExecuteScalar(); With scope_identity() Generating “System.InvalidCastException: Specified cast is not valid”

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!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

SELECT CONVERT(int, SCOPE_IDENTITY())

Method 2

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.

Method 3

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

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

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

Method 5

try adding SET NOCOUNT ON; before the insert.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0