• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 899
  • 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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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