SqlCeParameter return Auto ID (Primary Key)

Question

I have an SQL SqlCeParameter statement for example:

mySQLCommand1.CommandText = @"
   INSERT INTO clientSubjectiveComplaints (clientSubComplaintCreated)
   VALUES (@ClientSubComplaintCreated)
   ";

Once the INSERT is successful I need the auto generated ID (INT Primary Key) returned so that I can use it in a second INSERT statement.

Is this possible for SqlCe ? Please if you can provide an example.

score 7 · Accepted Answer · answered Jun 25 '11 at 19:25

7

Like this:

using (var connection = new SqlCeConnection(Settings.Default.Database1ConnectionString))
using (var command = connection.CreateCommand())
{
    connection.Open();

    command.CommandText = @"
        INSERT Test (Name)
        VALUES (@TestName)
        ";
    command.Parameters.AddWithValue("TestName", "SomeName");
    command.ExecuteNonQuery();

    command.CommandText = "SELECT @@IDENTITY";
    var id = command.ExecuteScalar();
}

answered Jun 25 '11 at 19:25

Alex Aza

76,499
26
155
134

3

BTW, if you need to get `int id`, the easiest way is to do this: 'var id = Convert.ToInt32((decimal)command.ExecuteScalar());' – Alex Aza Jun 25 '11 at 20:23
1

@DevilCode - just curious, what was the judgement, when you selected the answer. I spent time to make working code for you, didn't paste it until made sure it worked. And you selected short answer, that does not even contain valid code statement. On top of that the referenced url does not contain full solution. Also it shows conversion from decimal via string, which is very bad practice. Well... this is something rather discouraging about StackOverflow... – Alex Aza Jun 25 '11 at 20:39
1

Hi Alex, This was my bad, it was late at night whenI ticked when i was half asleep. Your answer was great, apologies. I have fixed this now. – DevilCode Jun 26 '11 at 18:26
'var id = Convert.ToInt32((decimal)command.ExecuteScalar());' 'int id = Convert.ToInt32((decimal)command.ExecuteScalar());' – DevilCode Jun 26 '11 at 19:21

score 4 · Answer 2 · edited Jun 22 '13 at 03:28

4

ExecuteScalar("SELECT @@IDENTITY")

More details can be found here.

edited Jun 22 '13 at 03:28

Danny Beckett

20,529
24
107
134

answered Jun 25 '11 at 19:24

ribram

2,392
1
18
20

SqlCeParameter return Auto ID (Primary Key)

2 Answers2

Linked