Why would @@IDENTITY or SCOPE_IDENTITY() be DBNull?

Question

(resolved: see bottom)

I have the following code snippet:

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, 
ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
Handles SqlDataSource1.Inserted
    affected = CInt(DirectCast(e.Command.Parameters("@affected"), IDbDataParameter).Value)
    newID = CInt(DirectCast(e.Command.Parameters("@newID"), IDbDataParameter).Value)
End Sub

Where @newID is defined like this in the SQL string:

"INSERT INTO x(a,b,c) VALUES (@a,@b,@c); SELECT @affected = @@rowcount, @newID = SCOPE_IDENTITY();

The parameters are defined using ASP.NET as follows:

The strange thing about it is that this works 90% of the time, but every once and a while it throws an InvalidCastException saying that "Conversion from type 'DBNull' to type 'Integer' is not valid." Any ideas on what could be causing this value to be null? I don't have any triggers set on the table, and the only thing my query is doing is running a plain insert into 1 table.

Edit: Based on the suggestions here, I added an affected parameter. I set a breakpoint, and affected = 1 but I still got the exception. However, I then figured out that I had SELECT @newID before SELECT @affected. I switched the order, and now @affected = 0. So it appears to be a problem with my insert statement after all. Thanks for your help!

Answer 1

What is the query text... part? An INSERT, I guess. Could it be an insert ... select which didn't find any rows?

Answer 2

Various possibilities:

• INSERT does not insert anything (as erikkallen said)

• your statement (not shown) inserts to various tables, the last one of which does not have an identity column

• the INSERT fires a trigger which inserts to a table without an identity column - try SCOPE_IDENTITY() instead

Answer 3

From MSDN

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.

Do you have a check to see if the insert has succeeded? It may be possible that the record you are trying to insert fails to insert anything for some reason, therefore the id is null.

Are there any triggers set up on the table? You may be retrieving the id from a trigger procedure using @@IDENTITY

I would suggest either using SCOPE_IDENTITY() or an output parameter to get the id

Answer 4

You might want to perform a check to see if @@ROWCOUNT > 0.

Also there were known problems in past versions of SQL Server with triggers impacting the survivability of @@IDENTITY. Do you have any triggers?

Answer 5

You should use SCOPE_IDENTITY() as oppose to @@IDENTITY in 99.9% of cases. It is very rare you will have a situation that requires something other than SCOPE_IDENTITY()

@@IDENTITY returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well.

Are you sure there are no auditing triggers on that table (perhaps added by your DBA)?

Answer 6

I am having this issue while running a transaction in Sql CE 3.5. The Command.ExecuteQuery() results in a value > 0 so I assume this means rows are updated however I am trying to get the Identity before the Transaction.Commit(). It comes back DBNull.

No, I am not starting a new connection before making the call to SELECT @@IDentity, it is the same open connection throughout. No, you cannot use Scope_Identity() in Sql Server Compact 3.5...so yes it is used much more than 0.01% of the time as someone above suggested.

Any ideas?

Answer 7

Another time when this occurred, changing

<asp:Parameter Name="newID" Direction="Output" Size="4" />

to

<asp:Parameter Name="newID" Direction="Output" Type="Int32" Size="4" />

fixed the problem for me. It appeared that my host turned on some requirements for more explicit declaration.