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

Answer 1

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);