OleDB Select Scope_Identity() after insert ERROR

[Richard]

It gives an error saying there is 2 much text after the SQL command.

with MSSQL i just placed, "; SELECT SCOPE_IDENTITY()" after the insert
statement and i would get the current ID,
but when i use OleDB it gives me the error each time, anyone know if there
is someother syntax for an MS Access DB?

thx in advance
Richard

 

[John Mas]

Don't think access supports it, only sql server

 

[William \(Bill\) Vaughn]

Access/JET does not support scripts (more than one statement at a time). SQL
Server does.
You'll need to execute another (separate) query to get the @@Identity.
SCOPE_IDENTITY() is not supported in Access/JET.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Kevin Yu [MSFT]]

Thanks for John's quick response!

Hi Richard,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you cannot execute multiple statements
in an OleDbCommand. If there is any misunderstanding, please feel free to
let me know.

As far as I know, executing multiple statements in a single command is not
supported by Jet engine. So I think we have to split the statements into
several command objects. Also, it's better to use @@IDENTITY instead of
SCOPE_IDENTITY() when you're working on an Access DB.

OleDbCommand cmd = new OleDbCommand("INSERT INTO Table1(aaa)
VALUES('bbb')", this.oleDbConnection1);
OleDbCommand cmd2 = new OleDbCommand("SELECT @@IDENTITY",
this.oleDbConnection1);
this.oleDbConnection1.Open();
cmd.ExecuteNonQuery();
int i = (int)cmd2.ExecuteScalar();
this.oleDbConnection1.Close();

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Richard]

Yup, this was exactly the information that i needed.

it works now perfect, thx for your help..

Richard