Microsoft SQL Server Compact edition insert query return select @@identity

Question

I am trying to create a function which inserts a record into a Microsoft SQL Server Compact edition 3.5 database and returns the id of the newly inserted row.

Here's my code:

upit = "insert into Crtez (Ncrteza, ProjID, lilu, lide, dubinaRova, d1, d2, ZCdulina, ZCpromjer, dBusenja) 
        values ('primjer 2 rolaza.dwg', 3, 49192.62, 49222.62, 3.11, 74.7693403335958, 15.2495262383346, 14,0,0.00)";

public static int UpisiUBazu(String putanja, String upitUpisa)
{
    int id = default(int);
    SqlCeConnection con = new SqlCeConnection();

    try
    {
        String constring = "Data Source=" + putanja;

        using (con = new SqlCeConnection(constring))
        {
            con.Open();
            SqlCeTransaction tr = con.BeginTransaction();
            SqlCeCommand com = null;
            com = new SqlCeCommand(upitUpisa, con);
            com.Transaction = tr;
            com.ExecuteNonQuery();
            com = new SqlCeCommand(@"SELECT @@IDENTITY AS ID", con);
            object o = com.ExecuteScalar();
            id = Convert.ToInt32(o);
        }
    }
    catch (SqlCeException ex)
    {
        MessageBox.Show("Pojavila se greška: " + ex.Message + "/" + ex.NativeError + "/" + ex.InnerException);
    }
    finally
    {
        con.Close();
    }
    return id;
}

What am I doing wrong??

Can you format this so it's more readable? Also check my answer [here](http://stackoverflow.com/questions/25937973/sql-output-inserted/25938038#25938038) similar question. — Nyra, Oct 10 '14 at 19:17

score 0 · Answer 1 · answered Oct 10 '14 at 19:22

0

SELECT @@IDENTITY AS ID should be in same query with INSERT statement.

INSERT INTO Crtez (...) VALUES (...); SELECT @@IDENTITY AS ID

@@IDENTITY

answered Oct 10 '14 at 19:22

Win

61,100
13
102
181

From what I've read this is not the best way to do it though. Setting to out the last inserted identity I thought was safer. Yes|No? – Nyra Oct 10 '14 at 19:26
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. If you do not execute it together, `SELECT @@IDENTITY AS ID` doesn't make sense. Where does it say it is not a best way? Could you post the link? – Win Oct 10 '14 at 19:30
when i add select@@.. in same query i get "There was an error parsing the query. [ Token line number = 1,Token line offset = 209,Token in error = SELECT ]" – Krkec Oct 10 '14 at 19:45
@user2836295 Can you run the same query in SSMS without error? Or try LittleBobbyTables suggested - `INSERT INTO Crtez (...) OUTPUT Inserted.ID VALUES(...);` – Win Oct 10 '14 at 19:47
@Win the link that LittleBobbyTables linked – Nyra Oct 10 '14 at 19:54
I agree that **OUTPUT** is better than **@@IDENTITY**. However, OP is not about **OUTPUT vs @@IDENTITY vs SCOPE_IDENTITY**. ***The problem is executing in two different queries.*** – Win Oct 10 '14 at 20:12
Yours works as well, just not as well as could... and OP question is actually `I am trying to create a function which inserts a record into a Microsoft SQL Server Compact edition 3.5 database and returns the id of the newly inserted row.` which OUTPUT is better. Why don't you just modify your answer to include his 2 queries and include calling OUTPUT instead of @@ID? – Nyra Oct 10 '14 at 20:20
I don't have SSMS but I run code from Lbbobbys link: ` DECLARE @IDs TABLE (id INT) INSERT INTO Identity_Test(value) OUTPUT inserted.id INTO @IDs VALUES(NULL); INSERT INTO Identity_Test(value) OUTPUT inserted.id INTO @IDs VALUES(NULL); SELECT id FROM @IDs; ` in SQL server Compact toolbox and i get this error: Message : There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = DECLARE ] Err. Par. : DECLARE Does this code works with Microsoft SQL Server Compact edition 3.5 database ? – Krkec Oct 10 '14 at 20:36
compact vs regular is irrelevant for this query- looking at it now. – Nyra Oct 10 '14 at 20:43
Your query is off- this may not be 100% syntax accurate- I don't have SQL on my work machine and can't arbitrarily start executing things on DEV env. `DECLARE @IDs TABLE (id INT, [value] varchar(20) = null) INSERT INTO [Identity_Test] ([value]) OUTPUT inserted.id VALUES(NULL); ` – Nyra Oct 10 '14 at 20:53

Microsoft SQL Server Compact edition insert query return select @@identity

1 Answers1