• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

SQLServer CE identity column

I have just inserted a record into an auto_increment table using the SQLServerCE database.
Keeping in mind that SQLServerCE does not support output parameters, how can
I determine the new value in the autoincrement column for the record I just inserted?

0
codefinger
Asked:
codefinger
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to run a second query to fetch the SCOPE_IDENTITY() value ... unfortunately
0
 
codefingerAuthor Commented:
I may have the syntax wrong, but I am getting an error that this function is not recognized by SQL Server Compact Edition. My code is attached:

The value of str_sql is "SELECT SCOPE_IDENTITY FROM COMMANDCODES"


Please advise.


Public Function QueryAllAsDataSet(ByVal strsql As String, ByRef lex As LastException) As DataSet

        Dim xds As New DataSet
        Try
            Dim SQLCeAdapter As New System.Data.SqlServerCe.SqlCeDataAdapter(strsql, _conobj.ConnectionString)
            SQLCeAdapter.Fill(xds)
        Catch ex As Exception
            lex.ErrorMessage = ex.Message
            If Not ex.InnerException Is Nothing Then
                lex.ErrorMessage = lex.ErrorMessage & vbCrLf & ex.InnerException.Message
            End If
            _LastError = lex.ErrorMessage
        End Try

        Return xds


    End Function

Open in new window

0
 
codefingerAuthor Commented:
sorry, str_sql is "SELECT SCOPE_IDENTITY() FROM COMMANDCODES"

I did not forget the ()  !
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's not FROM TABLE ... just SELECT SCOPE_IDENTITY()

you might try SELECT @@IDENTITY  instead
0
 
codefingerAuthor Commented:
Same result for SCOPE_IDENTITY() without FROM TABLE in SQL.

I at least get a result for @@IDENTITY,  but it is NULL.


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, in CE, which is local, as you then are the only session for the db, you will visibly need to do:

select max(id_col) from COMMANDCODES

Open in new window

0
 
codefingerAuthor Commented:
That works! Thanks!

0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now