Invalid Cast when dealing with @@Identity

Question

I have a winforms app that includes the following class method:

public aSqlQuery(SqlCommand pSqlCom, string pMode = "object", bool pGetID = false)
    {
        try
        {
            string strConnection = aSystem.ConnectionString;
            SqlConnection linkToDB = new SqlConnection(strConnection);
            pSqlCom.Connection = linkToDB;

            switch (pMode)
            {
                case "non query":
                    {
                        linkToDB.Open();
                        pSqlCom.ExecuteNonQuery();
                        if (pGetID == true)
                        {
                            SqlCommand sqlCom = new SqlCommand("SELECT @@IDENTITY;", linkToDB);
                            this.LastID = (int)sqlCom.ExecuteScalar();
                        }
                        linkToDB.Close();
                    }
                    break;

plus other switches

The pSqlCom (SqlCommand) executes fine becuase I can see the data written into the database. However the subsequent "SELECT @@IDENTITY" statement gives an invalid cast error What am I doing wrong and how can I retrieve the new ID created by SQL within my class method?

What does "sqlCom.ExecuteScalar();" actually returns to you? — Luis Filipe, Jul 27 '12 at 14:20
This is great resource, read the full thread please, same to @Martin Smith http://stackoverflow.com/questions/1174930/int32-tryparse-or-intcommand-executescalar/1175048#1175048 — Yaroslav, Jul 27 '12 at 14:34
@Yaroslav - Not sure why you are telling me to read it. The answer there in no way negates the comment I made on your answer. — Martin Smith, Jul 27 '12 at 14:37
I am probably doing a stupid question, but are you sure that the table on which you perform the INSERT contains an IDENTITY column? I have code like yours and never seen this problem. — Steve, Jul 27 '12 at 14:51
@Steve that would explain why `@@IDENTITY` is returning null and failing the cast. — Jodrell, Jul 27 '12 at 14:53

Jodrell · Answer 1 · 2012-07-27T15:15:19.707

1

Insert the row and get the Id it is was given with SCOPE_IDENTIY(), don't use @@IDENTITY.

You need to use SCOPE_IDENTITY() on the same connection and scope, just after the INSERT.

~~In your example no INSERT is performed on your connection so you can't expect to get the last generated Id.~~

In your example its not clear that pSqlCom performs an INSERT, if it does not any indentity function will return NULL which cannot be converted to int

EDIT

You want to use SCOPE_IDENTITY() and you want to do it in the same Command as the INSERT.

So, your statement should be somthing like

var sql =
@"INSERT <Your Data> <Your Table>;
SELECT SCOPE_IDENTIY();"

using (SqlConnection connection = new SqlConnection(strConnection))
{
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        connection.Open();
        object result = command.ExecuteScalar();
    }
}

int? id = (int?)(!Convert.IsDBNull(result) ? result : null);

edited Jul 27 '12 at 15:15

answered Jul 27 '12 at 14:27

Jodrell

34,946
5
87
124

Scope_Identity() gives the same error message. Please clarify scope. I thought that if the pSqlCom AND "SELECT SCOPE_IDENTIY();" where executed back to back on the same connection, that that would qulify as the 'same scope'. Am I wrong in assuming this? – PJW Jul 27 '12 at 14:30
Its a Sql Insert Command that puts a new entry into a database table - which works - because each ettempt succesfully inserts the data into the database, but I am unable to retrieve the ID created by SQL server. – PJW Jul 27 '12 at 14:45
@PJW You wan't to use `SCOPE_IDENTITY()` as a second statement in the same `Command`. – Jodrell Jul 27 '12 at 14:48
1

@PJW concerning the specific cast problem, I suspect that `@@IDENTITY` is returning null, which cannot be cast to `int` – Jodrell Jul 27 '12 at 14:51
1

@@Idnetity shoudl never be used to return the identity just inserted, it will give the worng answer if someone ever puts a trigger onteh table that inserts to another table with an indetity. Worse, you will not find this out until your data is setroyed beyond your abilty to fix. SO even if you have no triggers now, it s an exptremely dangerous practice to use @@identity. – HLGEM Jul 27 '12 at 14:58

score -1 · Accepted Answer · answered Jul 27 '12 at 20:33

The correct answer it turns out was that the SELECT SCOPE_IDENTITY() statement had to form part of the same SqlCommand as the INSERT statement which preceeded it, but which I had contained in the previous SqlCommand 'pSqlCom'. Once the SELECT SCOPE_IDENTITY() was incuded as part of pSqlCom the code correctly returned the Identity.

score -2 · Answer 3 · answered Jul 27 '12 at 14:22

-2

Your call to the command should be returning something not of scalar type. Indeed you need to use Int32 instead. Chek ExecuteScalar on MSDN.

Also, I recommend you to use SCOPE_IDENTITY() instead of @@Identity. Check the following link for a detailed explanation.

answered Jul 27 '12 at 14:22

Yaroslav

6,476
10
48
89

If the ID is an integer how can it not be a scalar? Also I started by using SCOPE_INDENTITY() and got the same error message so I reverted to @@IDENTITY as a second option - but I got no further. – PJW Jul 27 '12 at 14:24
Think before giving a minus one. I could do the same with your question as it denotes lack of research. But I prefer instead to answer and help. Check my answer again – Yaroslav Jul 27 '12 at 14:26
`(Int32) cmd.ExecuteScalar();` and `(int) cmd.ExecuteScalar();` are synonymous. – Martin Smith Jul 27 '12 at 14:29
The link I posted clearly states that on ExecuteScalar INT cannot be used. I'm not stating that are synonimous or not, just answering the question, using INT will fail, as can be read on both the link on this site, on MSDN and many posts on other forums by doing a quick search. @AVD has already clearly pointed out where the problem resides – Yaroslav Jul 27 '12 at 14:43
1

@Yaroslav - Your answer suggests that the OP needs to "use Int32 instead" I just pointed out that this will make zero difference. `int` is just a `C#` keyword for the same thing. – Martin Smith Jul 27 '12 at 14:44
@MartinSmith more on the subject http://stackoverflow.com/questions/7803375/casting-exception-when-trying-to-get-value-from-executescalar http://stackoverflow.com/questions/8736337/trying-to-pass-recent-id-with-scope-identity-and-its-error-me-specified-cast And there is even more – Yaroslav Jul 27 '12 at 14:47
1

@Yaroslav the links don't really support your case as far as I can tell. – Brian Rasmussen Jul 27 '12 at 14:58
Ok...kill me...but the answer given by @adrift on the 2nd link is not far from what I'm answering. I did not said on any moment that INT or INT32 are different or alike, just recommended to use INT32 cast. Is not exactly the answer, but is not definitely totally wrong to the point of receiving two minus one....whatever... – Yaroslav Jul 27 '12 at 15:10
The good thing of this answer is that even the OP accepted his own answer and acknowledged that using `SCOPE_IDENTITY` solves his problem. – Yaroslav Nov 16 '12 at 23:56

Invalid Cast when dealing with @@Identity

3 Answers3