none
SQL CE & @@IDENTITY

    Question

  • If I perform an insert from my Vb.NET and then user select @@IDENTITY to return the identity, the query returns DBNull, even though the row has been inserted and an indentity created.

    If I perform the same insert from CE Query Analyser followed by the @@IDENTITY then it returns the correct identity value.

    If I perform the insert from my Vb.Net app, and then go to query analyser and select @@INDENTITY then it returns DBNull.

    Can anybody explain why I don't get the idendtity when the insert is performed from vb.net?

    The code for insert looks like...

    Dim Command As String = "INSERT INTO " & TableName & Fields & Values
    SqlCeHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, Command)



    Wednesday, September 07, 2005 12:26 PM

Answers

  • We tried to repro this but could not repro till now.  Here is the brief info about @@IDENTITY:

    1) @@IDENTITY is an internal variable which is a session based. 
    2) Inserting from a VB Statement and immediately query should work
    3) Similary, inserting from Query Analyzer and running SELECT @@IDENTITY should work.  But, if you close and open Query analyzer after the insert but before SELECT.  Then, SELECT returns DBNull.
    4) However, Inserting from a VB Statement and then running SELECT @@IDENTITY from Query Analyzer will return DBNull which is a correct behavior
    5) Same is the case with inserting in Query Analyzer and running the SELECT @@IDENTITY query from a VB Program would result in DBNull and this is also a correct behavior

    It would be nice if you can share us a sample repro program including the Visual Studio project files so that we can take this ahead.

    Thanks,
    Laxmi
    Tuesday, September 27, 2005 8:14 AM
  • Thanks for getting the information back to us.

    Good Luck!

    Thanks,
    Laxmi NRO, MSFT, SQL Mobile
    Wednesday, September 28, 2005 12:37 PM

All replies

  • Can you please know the exact INSERT command that is what are the values of Variables TableName, Fields, Values.  And, also the exact SELECT query used to lookup.

    Thanks,
    Laxmi NRO [MSFT], SQL Mobile
    Monday, September 19, 2005 9:37 AM
  • The TableName, Columns and Values are strings that are built at runtime using reflection and custom attributes to identify names and return object values.

    A copy from the watch window of the completed string is shown below for inserting a new Product.

    INSERT INTO Product(Description,Nett,Waste) Values('My Product','100','50')

    The code to return the identity is:

    Dim Id As Object = SqlCeHelper.ExecuteScalar(ConnectionString, CommandType.Text, "SELECT @@IDENTITY")

    Hope this helps...

    Monday, September 19, 2005 10:11 AM
  • We want to dig into this more.  Can you please provide us the following details:
    1) Which type of device (Pocket PC/Smart Phone) is used?
    2) Which version of device (Pocket PC 2003, Pocket PC 2005, Smartphone 2003, ...etc) is used?
    3) Which version of SQL Server CE (2.0/3.0) is used?


    Thanks,
    Laxmi NRO, MSFT, SQL Mobile

    Wednesday, September 21, 2005 12:06 PM
  • Thanks for the reply..

    The device is running CE.NET 4.2 and the version of SQL CE is 2.0

    Cheers..
    Friday, September 23, 2005 2:00 PM
  • We tried to repro this but could not repro till now.  Here is the brief info about @@IDENTITY:

    1) @@IDENTITY is an internal variable which is a session based. 
    2) Inserting from a VB Statement and immediately query should work
    3) Similary, inserting from Query Analyzer and running SELECT @@IDENTITY should work.  But, if you close and open Query analyzer after the insert but before SELECT.  Then, SELECT returns DBNull.
    4) However, Inserting from a VB Statement and then running SELECT @@IDENTITY from Query Analyzer will return DBNull which is a correct behavior
    5) Same is the case with inserting in Query Analyzer and running the SELECT @@IDENTITY query from a VB Program would result in DBNull and this is also a correct behavior

    It would be nice if you can share us a sample repro program including the Visual Studio project files so that we can take this ahead.

    Thanks,
    Laxmi
    Tuesday, September 27, 2005 8:14 AM
  • Thanks for the update...

    I'll attempt to re-create the problem in a small project, I implemented a work round in the meantime.

    My local CE database is a mixture of local only tables and other tables that have been 'pulled' using RDA from a main SQL Server. Is it possible that this behaviour could be experienced if the table is a SQL Server table which has an identity definition but at the server?

    I've not tested this but will try.

    Tuesday, September 27, 2005 12:50 PM
  • Hi Laxmi,

    Forget my last comment, I have been able to repro the issue in a small project with a local table only, how do I get the code to you?

    Cheers

    Glynn
    Tuesday, September 27, 2005 2:03 PM
  • Think I've found the problem...

    The clue was in the description you gave me of @@IDENTITY when you said that it was a 'session' variable.

    I built a SQLCEHelper class for CE similiar to the Microsoft Application block for the full framework. The Application block always disposes of the connection object after each request on the database so taking Microsoft's lead in my CE version I did the same.
     
    I am assuming that when I dispose of my connection I am losing my session so my next request with a new connection object is in fact a new session.

    I've tested this and I do get the identity on my next query, however the datatype is DbDecimal eventthough the identity column is an integer. Not sure if this is right or wrong but I'll work with it for the time being....

    Thanks for your help

    Glynn

    Tuesday, September 27, 2005 5:30 PM
  • Thanks for getting the information back to us.

    Good Luck!

    Thanks,
    Laxmi NRO, MSFT, SQL Mobile
    Wednesday, September 28, 2005 12:37 PM
  • How did you solve it ... having problesm with this now... one year behind
    Tuesday, October 17, 2006 11:55 AM
  • My problem was that I was making a connection to perform the insert but then making a new connection to read the identity. The identity is a 'session' variable and as such is associated with the connection used to perform the insert.

    Hope that helps..

     

    Wednesday, October 25, 2006 9:38 AM
  • There are two things invloved in using the @@IDENTITY internal variable for the SQL Mobile or SQL Server Ev.

    1) The @@IDENTITY is session based hence the database connection should not be closed. Remember this SELECT @@IDENTITY has to be called right after the successfull insertion of an entry in the desired database table.

    2) The @@IDENTITY returns a decimal value so that it needs to be type casted to an integer (For example, System.Convert.ToInt32(return_value_@@IDENTITY), and the return value is neither null nor DBNull)

    Laxmi,

    Will you (MSFT) include the usage of SCOPE_IDENTITY() to sort out the limitations of using @@IDENTITY in near future?

    -Ravi Bala

    Wednesday, October 25, 2006 7:22 PM
  • We have not planned to support SCOPE_IDENTITY() so far till 4.0.  We will consider it for the later versions of 4.0.

    [Note: Things might change and this may very well may make into 4.0 or before depending on the customers demand :) ]

    Thanks,

    Laxmi

    Monday, November 06, 2006 7:50 PM
  • Is there anymore news on the SCOPE_IDENTITY() being implemented? 
    Friday, January 01, 2010 6:42 PM
  • I am looking to be able to do this on SQL Compact: (This code is for SQL Server)

    Dim cmdInsert As New SqlCeCommand( _
                "INSERT INTO tbl " & _
                " (Name) " & _
                "VALUES (@Name) " & _
                "SET    @PKey = SCOPE_IDENTITY() ", _
                cnn)

      With cmdInsert.Parameters
                .Add("@Name", SqlDbType.NVarChar, 50, "Name")
                .Add("@PKey", SqlDbType.Int, 4, "PKey").Direction = ParameterDirection.Output
            End With

    I want to be able to return the PKey in one statement. This is the only thing that is holding me back from switching to SQL Compact.

    Thank you!


    Friday, January 01, 2010 6:57 PM
  • SQL Compact does not support multi command. You can execute only one SQL statement at a time using SqlCeCommand.

    Just a suggestion:You may write your own warpper to simulate this behaviour. 

    Regards,
    Devesh Anand


    Please click Yes, if my post was helpful.
    Saturday, January 02, 2010 7:35 PM
    Answerer
  • Thank you for the information. What would the code look like to simulate this behavior? 
    Sunday, January 03, 2010 4:37 AM
  • Hi

    acutully sqlce cant execute multiple query.so we need to execute identity query differently..

    the code for same is as below

    {

                string spquery = @"Your insert query ";
                cmd = new SqlCeCommand(spquery, con);
                con.Open();
                cmd.ExecuteNonQuery();
             

                SqlCeCommand cmd1 = con.CreateCommand();
                cmd1.CommandText = "SELECT @@IDENTITY"; //using identity
                int id =Convert.ToInt32( cmd1.ExecuteScalar());

               con.Close();

    }

     

    in case if you have any issue please let me know

     

    with thanks,

    amit khese

    • Proposed as answer by Amit Khese Wednesday, May 25, 2011 7:18 AM
    Wednesday, May 25, 2011 7:17 AM