Getting @@Identity from an Access table

My code below always returns 0 for the Identity. Any idea why. The
database is in Access 2k.

int ID=0;
OleDbConnection conn=new OleDbConnection();
OleDbCommand aCommand=new OleDbCommand();
conn.ConnectionString =ConnectionString;
conn.Open();

aCommand=conn.CreateCommand();
aCommand.CommandText =strSQL;
aCommand.ExecuteNonQuery();

strSQL = "SELECT @@IDENTITY AS ID";
aCommand.CommandText = strSQL;
ID=(int)aCommand.ExecuteScalar();
conn.Close();
return (ID);

Miha
Thu Dec 04 03:09:28 CST 2003

Is by any case your id *not* defined as AutoNumber?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

"mike" <mychevworld@yahoo.com> wrote in message
news:1ffc07e3.0312031642.57ab4102@posting.google.com...
> My code below always returns 0 for the Identity. Any idea why. The
> database is in Access 2k.
>
> int ID=0;
> OleDbConnection conn=new OleDbConnection();
> OleDbCommand aCommand=new OleDbCommand();
> conn.ConnectionString =ConnectionString;
> conn.Open();
>
> aCommand=conn.CreateCommand();
> aCommand.CommandText =strSQL;
> aCommand.ExecuteNonQuery();
>
> strSQL = "SELECT @@IDENTITY AS ID";
> aCommand.CommandText = strSQL;
> ID=(int)aCommand.ExecuteScalar();
> conn.Close();
> return (ID);

Paul
Thu Dec 04 08:28:13 CST 2003

On 3 Dec 2003 16:42:29 -0800, mychevworld@yahoo.com (mike) wrote:

¤ My code below always returns 0 for the Identity. Any idea why. The
¤ database is in Access 2k.
¤
¤ int ID=0;
¤ OleDbConnection conn=new OleDbConnection();
¤ OleDbCommand aCommand=new OleDbCommand();
¤ conn.ConnectionString =ConnectionString;
¤ conn.Open();
¤
¤ aCommand=conn.CreateCommand();
¤ aCommand.CommandText =strSQL;
¤ aCommand.ExecuteNonQuery();
¤
¤ strSQL = "SELECT @@IDENTITY AS ID";
¤ aCommand.CommandText = strSQL;
¤ ID=(int)aCommand.ExecuteScalar();
¤ conn.Close();
¤ return (ID);

Don't believe you need the alias. Try using:

"SELECT @@IDENTITY"

Also, I'm assuming your strSQL value is an INSERT statement?


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

mychevworld
Thu Dec 04 16:54:24 CST 2003

Turns out the database wasn't in 2k after all. Once I converted it all
worked as expected.

Here is a Microsoft article explaining the @@Identity situation.
http://support.microsoft.com/default.aspx?scid=kb;en-us;816112&Product=adonet

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote in message news:<vvgusvgkgmv34vc8hd5d5g9jra69cbvcc9@4ax.com>...
> On 3 Dec 2003 16:42:29 -0800, mychevworld@yahoo.com (mike) wrote:
>
> ¤ My code below always returns 0 for the Identity. Any idea why. The
> ¤ database is in Access 2k.
> ¤
> ¤ int ID=0;
> ¤ OleDbConnection conn=new OleDbConnection();
> ¤ OleDbCommand aCommand=new OleDbCommand();
> ¤ conn.ConnectionString =ConnectionString;
> ¤ conn.Open();
> ¤
> ¤ aCommand=conn.CreateCommand();
> ¤ aCommand.CommandText =strSQL;
> ¤ aCommand.ExecuteNonQuery();
> ¤
> ¤ strSQL = "SELECT @@IDENTITY AS ID";
> ¤ aCommand.CommandText = strSQL;
> ¤ ID=(int)aCommand.ExecuteScalar();
> ¤ conn.Close();
> ¤ return (ID);
>
> Don't believe you need the alias. Try using:
>
> "SELECT @@IDENTITY"
>
> Also, I'm assuming your strSQL value is an INSERT statement?
>
>
> Paul ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)