[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQLServer CE identity column

Posted on 2010-11-22
7
Medium Priority
?
874 Views
Last Modified: 2013-12-27
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
Comment
Question by:codefinger
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34186573
you will have to run a second query to fetch the SCOPE_IDENTITY() value ... unfortunately
0
 

Author Comment

by:codefinger
ID: 34188415
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
 

Author Comment

by:codefinger
ID: 34188437
sorry, str_sql is "SELECT SCOPE_IDENTITY() FROM COMMANDCODES"

I did not forget the ()  !
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

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

you might try SELECT @@IDENTITY  instead
0
 

Author Comment

by:codefinger
ID: 34188661
Same result for SCOPE_IDENTITY() without FROM TABLE in SQL.

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


0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34188695
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
 

Author Closing Comment

by:codefinger
ID: 34188758
That works! Thanks!

0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month18 days, 2 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question