Last inserted record ID problems using typed DataSet and DataGridView

Question

Hi. I'm using following for a simple database application:

• SQL Server Compact Edition .sdf file as database, with int primary key IDs.
• Typed DataSet and BindingSource as data access layer
• DataGridView for displaying data.

My problem is, I'm having trouble with the last inserted record/row ID. When I add a row to datagridview, using Append button of a navigator component, the ID of the new record/row is -1. It's still -1 even after save data to database using TableAdapter.Update(). I know I can get last ID using a seperate query with @@identity or scope_identity() but it doesn't sound right that you just have to use another query to update your data, manually at that. Am I missing something here? Is there an automatic way to update your data after saving to database and getting the ID of the record you just inserted?

Also, I saw a "refresh the datatable" option in dataset designer->table adapter configuration->advanced window, but it's disabled for some reason. But I don't know if it's related..

I'd appreciate any help with this..

Answer 1

What I typically do with my DTO is something similar to the vb below

Public Sub InsertSupplier(ByVal SupplierObject As Supplier) Implements ISupplierRepository.InsertSupplier
SupplierObject.SupplierID = MyBase.ExecuteNonQueryWithReturnValue("usp_Northwind_InsertSupplier", SupplierObject.CompanyName)
End Sub

With my stored procedure similar to the below

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[usp_Northwind_InsertSupplier]    Script Date: 1/1/2009 10:59:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Northwind_InsertSupplier]
@CompanyName nvarchar(40)
AS
INSERT INTO dbo.Suppliers
VALUES (@CompanyName)

RETURN @@Identity

Just to clarify what my 1 liner in the base class does (using the enterprise library fyi)

Public Function ExecuteNonQueryWithReturnValue(ByVal storedProcedureName As String, ByVal ParamArray parameterValues() As Object) As Object Implements Interfaces.IRepository(Of T).ExecuteNonQueryWithReturnValue
    Dim Output As Object
    Dim db As Database = DatabaseFactory.CreateDatabase()
    Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues)
    db.ExecuteNonQuery(dbCommand)
    Output = CType(db.GetParameterValue(dbCommand, "@RETURN_VALUE"), Object)
  Return Output
End Function

Answer 2

You can also try declaring your identity column as an OUTPUT parameter in your stored procedure.

CREATE PROCEDURE YourSP
(@ID int OUTPUT, ...

Answer 3

Thanks for the both answers. I've found the source of the problem, unfortunately there's no solution to my liking. The problem is in SQL Server Compact Edition. Since it doesn't support stored procedures or Multiple Active Result Set (MARS) you can't run more than one SQL in one query. Therefore "refresh the datatable" option is disabled, which would update ID value with actual value if it would not disabled. I'm gonna change my primary key ID valumn from int to Guid and handle RowInit event of the DataGridView and give a Guid ID to new rows manually.

Answer 4

I found this in documentation to Microsoft SQL Server Compact Edition:

return last inserted identity:

SELECT @@IDENTITY or SELECT @@IDENTITY AS 'TEMPNAME'

Answer 5

set IdentitySeed and IdentityIncrement to 1 and evereything works.