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)
SQL CE & @@IDENTITY
Hot Topic
- The path is not valid. Check the directory for the database.
- SMO ScriptingOptions.NoCommandTerminator question
- SSIS Import and Export Wizard Data Sources
- multiple calls to SP
- BOL ABS Overflow error?
- Grant CREATE VIEW, CREATE PROCEDURE ...
- SSIS instead of raw T-SQL
- Watermark Generation is Invalid Error
- The target service name could not be found.
- "Repair statement not processed. Database needs to be in single user mode."
SQL Server
- conversation groups and processing in order on the target end
- Enterprise Publisher to Express Subscribers (2005)
- Putting Multiple Field data in a report table cell
- "Invalid Object Name" error in "Database Setup" step of "Configuration Manager"
- execute DTS from asp.net 2.0 - package does not exist error
- Format Table Row into XML and Insert into Another Table?
- Parameterized Query Using Wildcards in VS2005
- Can't open reports or reportserver from ie
- SQL Mobile query hangs PPC 2003 device
- Connect Oracle10g from SSIS
SQL CE & @@IDENTITY
Ritchie Hughes
Good Luck!
Thanks,
Laxmi NRO, MSFT, SQL Mobile
jay2006
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..
Peter Mortier
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
TimothyMS
Scott Forbes
The device is running CE.NET 4.2 and the version of SQL CE is 2.0
Cheers..
kkkwj
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
Daniel D.C.
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.
Shahed Khandkar
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
Sheila1983
Thanks,
Laxmi NRO [MSFT], SQL Mobile
Marius.Cotor
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
Xiaowei Jiang
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...
ManoranjanPatel
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
mrsamer
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