get the PK right after an insert statement ODBC SQL Anywhere

Question

I do have this insert statement over a SQL Anywhere database, for the insert I use ODBC and this is a little snippet of the sentence(just an example)

 cmd_ex.CommandText = @" insert into tabla
                         (numero1, numero2, numero3, numero4...numeroN) 
                         VALUES
                         (?,?,?,?,?)";

and works just fine, the only problem is that the PK is autoincrement and I need to recover that value if it possible in the same statement, could you help me?

base upon for the suggestions I tried this

cmd_.CommandText = @" insert into table(...) 
                         VALUES
                         (?,?,...,?) SELECT SCOPE_IDENTITY()";
Int32 id = Convert.ToInt32(cmd_.ExecuteScalar());
                    MessageBox.Show("valor del nuevo registro    "+id);

but got the error ERROR [HY000] [Sybase][ODBC Driver][SQL Anywhere]Host variables may not be used within a batch

do I have to set it at the end of the sentence? (?,?,...) select @scope_identity() ? — Pablo Tobar, Apr 05 '18 at 15:49
You have three different options. Pick which one applies best: https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql — dfundako, Apr 05 '18 at 16:06

score 1 · Accepted Answer · answered Apr 06 '18 at 13:32

You can't execute 2 sentences at the same time and get the value of second statement into a variable.

You need to execute them one by one. I am not sure which language are you using, but something like the following should work.

cmd_.CommandText = @" insert into table(...) 
                     VALUES
                     (?,?,...,?)";
cmd_.ExecuteScalar();
cmd_.CommandText = @"select @@Identity";
Int32 id = Convert.ToInt32(cmd_.ExecuteScalar());
                MessageBox.Show("valor del nuevo registro    "+id);

get the PK right after an insert statement ODBC SQL Anywhere

1 Answers1