Get identity of last inserted record

[Robert_SF]

Using VS2010 and SqlCE 3.5, I have the following code.

Me.Validate()
Me.CasesBindingSource.EndEdit()
Me.CasesTableAdapter.Update(Me.DbCases.cases)
'Now we need the identity of the record just added

To retrieve the identity (autoincrement number), I created a query in CasesTableAdapter with the code SELECT @@IDENTITY AS TEMPVALUE and set the execution mode to scalar, but

Dim LastRecID As Integer = Me.CasesTableAdapter.GetLastID()

returns zero even though the table adapter's update method did successfully add the record. No error is generated either.

I read that @@IDENTITY is supposed to work even if the connection to the database is closed and reopened. So I tried this.

Dim con As New SqlServerCe.SqlCeConnection(ConnectionString)
Dim cmd As New SqlServerCe.SqlCeCommand
Dim id As New Object
cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
cmd.CommandType = CommandType.Text
cmd.Connection = con
cmd.Connection.Open()
id = cmd.ExecuteScalar

The result I get is an Int32 but still zero. By the way, there's nothing strange about the identity itself. It starts at 1 and increments by 1.

Any ideas what I'm doing wrong? Thanks!

 

[ss7thirty]

You should be able to accomplish what you are looking for, these articles explain everything there is to know about identity values. Possible consider creating a stored proc that does your insert and has the identity as an output:

@@IDENTITY (Transact-SQL)
IDENT_CURRENT (Transact-SQL)

 

[Robert_SF]

Thanks, ss, but SQL Server Compact Edition does not support stored procedures nor IDENT_CURRENT.

The following code worked without a problem:

Dim cmd As New SqlServerCe.SqlCeCommand
cmd.Connection = New SqlServerCe.SqlCeConnection("Data Source=C:\Users\... \pos.sdf")
cmd.CommandType = CommandType.Text
cmd.CommandText = "INSERT INTO test (name) VALUES ('John')"
cmd.ExecuteNonQuery
cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
Dim x As Integer = cmd.ExecuteScalar

Sure enough, after execution, x holds the identity value of the record inserted just above.

I'll have to look more closely at what's different between this code and the other code that I can't get to work.

[a bit later]

Ok, I've integrated the code above with the other code I had, and it's broken again.

Private Sub Ok_Button_Click(...) Handles Ok_Button.Click
    Me.Validate()
    Me.CasesBindingSource.EndEdit()
    Me.CasesTableAdapter.Update(Me.DbCases.cases)
    'Now we need the identity of the record just added
    Dim cmd As New SqlServerCe.SqlCeCommand
    cmd.Connection = CasesTableAdapter.Connection
    cmd.CommandType = CommandType.Text
    cmd.Connection.Open()
    cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
    Dim x As Integer = cmd.ExecuteScalar
    Me.DialogResult = DialogResult.OK
End Sub

I get an error because the return value is a DBNull. I'm expecting the integer value of the last identity, of course.

Any ideas anybody?

 

[ss7thirty]

After insertion you can also SELECT MAX(identField) FROM yourTable and that should return what you are looking for if it is auto increment.

 

[Robert_SF]

Thanks for the suggestion. I think that's what I'll do.

In case you're interested, I think I've found what the problem is. It seems that the scope of @@IDENTITY, at least in SQL Server CE, is restricted to the same connection. If you close and reopen the connection, it will return DBNull. I verified that with this code:

 Dim cmd As New SqlServerCe.SqlCeCommand
cmd.Connection = New SqlServerCe.SqlCeConnection(ConnectionString)
cmd.CommandType = CommandType.Text
 
cmd.Connection.Open()
 
cmd.CommandText = "INSERT INTO testable (info) VALUES ('John Smith')"
cmd.ExecuteNonQuery()
 
cmd.Connection.Close() ' **** rem in and out to see the difference
cmd.Connection.Open() ' **** rem in and out to see the difference
 
cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
Dim LastRecID As Integer = cmd.ExecuteScalar

The table is very simple, just two columns: one, an autoincrement integer PK; and two, an nchar field that allows nulls and duplicates. Without the close/open lines, I get the correct last record id in LastRecID. But when I close and (re)open the connection, I get DBNull in LastRecID.

So that's why it's not possible to get the last record id if you use table adapters, unless you actually extend the data adapter class. That's a bit much for me for something so simple, but if you want to read about it, here's an article by the wonderful Beth Massi (I don't know her, but her articles and videos on VB are fantastic!): Inserting Master-Detail Data into a SQL-Server Compact Edition Database - Beth Massi - Sharing the goodness that is VB - Site Home - MSDN Blogs

So I'm going with your suggestion, and the SQL purists can bite us! LOL