Inserting into SQL Server CE database file and return inserted id

Question

Problem is :

My query

INSERT INTO TableName(val1,val2)values(1,2);
SELECT @@IDENTITY;

When I run it in run query from server explorer I get the correct result.

But when I use ExecuteScalar or ExecuteDataTable I get an error ,... query return null

public object ExecuteScalre(string Query, CommandType type) 
{ 
    OpenConnection(); 
    cmd.CommandText = Query; 
    cmd.CommandType = type; 

    object obj = null; 

    try 
    { 
        obj = cmd.ExecuteScalar(); 
    } 
    catch 
    { 
    } 
    finally 
    { 
        ReleaseResource(); 
    } 

    return obj; 
} 

public DataTable ExecuteDataTable(string Query, CommandType type)
{
    OpenConnection();
    cmd.CommandText = Query;
    cmd.CommandType = type;
    DataTable dt = new DataTable();
    dataAdaptor = new SqlCeDataAdapter(cmd);

    try
    {
        dataAdaptor.Fill(dt);
    }
    catch
    {
    }
    finally
    {
        ReleaseResource();
    }
    return dt;
}

Notes: it's an .sdf file (SQL Server CE), NOT .mdf, so we can not use stored procedures

public object ExecuteScalre(string Query, CommandType type) { OpenConnection(); cmd.CommandText = Query; cmd.CommandType = type; object obj = null; try { obj = cmd.ExecuteScalar(); } catch { } finally { ReleaseResource(); } return obj; } or — Osama Elfar, Sep 21 '14 at 20:09
@OsamaElfar Please note that you can edit your question. The code above should be included into the question via editing. — Dmitry, Sep 21 '14 at 20:10
Remove the try/catch. And check the exception. Creating empty try/catch is an effective way to hide bugs in your code. — Steve, Sep 21 '14 at 20:15
this is the error from tracing : There was an error parsing the query. [ Token line number = 1,Token line offset = 103,Token in error = SELECT ] — Osama Elfar, Sep 21 '14 at 20:20

Steve · Accepted Answer · 2014-09-21T20:43:45.893

8

Sql Server Compact Edition doesn't support multiple statements in one query.
This database (usually) is employeed in a single user scenario, so you could split your command and send two queries to the database, the first inserts the record, the second one returns the @@IDENTITY value.

    cmd = new SqlCeCommand("INSERT INTO TableName(val1,val2)values(1,2)", cn);
    cmd.ExecuteNonQuery();
    cmd.CommandText = "SELECT @@IDENTITY";
    int result = Convert.ToInt32(cmd.ExecuteScalar());

edited Sep 21 '14 at 20:43

answered Sep 21 '14 at 20:34

Steve

213,761
22
232
286

5

The important part is that the connection should remain open betweeen the two commands – ErikEJ Sep 22 '14 at 06:25

score -1 · Answer 2 · answered Sep 21 '14 at 20:34

The reason for this is the fact, that you submit two sql commands in one Command-object. The INSERT statement did return nothing, thats correct behavior.
Use the OUTPUT-Clause of TSQL. This will give you values from inserted or deleted rows as a recordset. So you can use ExecuteScalar to get this value.

Assume you have a table with the following structure

CREATE TABLE [dbo].[Table_1]  
([ID] [int] IDENTITY(1,1) NOT NULL,  
[Value1] [int] NOT NULL,  
[Value2] [int] NULL ) ON [PRIMARY]

Using the following SQL gives you the ID of the row inserted as a resultset

insert Table_1 OUTPUT Inserted.ID values (1,2)

This would be true if we were talking about Sql Server, (and in that case, also the double statement would work ) but the OP is using Sql Server Compact Edition and there is no OUTPUT available. — Steve, Sep 28 '14 at 20:01

Inserting into SQL Server CE database file and return inserted id

2 Answers2

Linked