Retrieving @@Identity value in ado.net

[David W. Fenton]

You are working across two commands. You have a couple of options
here:

1. Combine statments

St = "INSERT INTO tblClients ( TempID ) SELECT 123; SELECT
@@IDENTITY;"

Um, no, you can't do that. Look at the first line of the post you
quote:

"insert a record into an access table" means that you can't combine
multiple SQL statements, because Jet/ACE does not (and never has)
supported this.

2. Make a stored procedure that returns the IDENTITY (in this
case, I would use SCOPE_IDENTITY() rather than @@IDENTITY)

Until A2010, Jet/ACE has nothing approaching stored procedures
(except parameter queries) -- no procedural logic, and no ability to
do this.

And SCOPE_IDENDITY() is a SQL Server command, not valid in Jet/ACE.

You really should read posts more carefully before embarassing
yourself with a completely non-applicable reply.

 

[David W. Fenton]

Since you are not getting an error, I assume you are hitting SQL
Server.

No, he's not. He's using a Jet/ACE database, which is the first
thing he said in his question.

SELECT @@IDENTITY is, in fact, supported by Jet/ACE. I don't use ADO
or ADO.NET, so can't really explain what should be done in the code
provided. It looks extremely complicated to do something very
simple. In DAO, you'd do this (after your database variable was
initialized):

db.Execute strSQL
ID = db.OpenRecordset("SELECT @@IDENTITY")(0)

That can easily be replicated in classic ADO with a connection
object replacing the database variable. The key is using the same
connection for both the DML statement and the SELECT statement.

 

[David W. Fenton]

This is true if he wants to use 2 queries. He has the option of
chaining queries or using a stored procedure. Selecting
MAX(keyvalue) can be off, esp. if you do not lock the table and it
has any volume in transactions. I would prefer SCOPE_IDENTITY().

Arvin is wrong about SELECT @@IDENTITY.

And you are wrong about batching SQL statements. Jet/ACE does not
and never has supported that, and the very first sentence of the
post asking the question says that it's a Jet/ACE database, not SQL
Server.

So none of your advice is applicable at all.

 

[David W. Fenton]

Saw the OLEDB in there. OOPS!!!

Depends on the database. If Access, he can still hook up as a
query and return the auto number. And, should also consider
locking the table. The fact he is returning 0 on @@IDENTITY
suggests he is hitting SQL Server through OLEDB, or else that
statement should error out (I think?).

He is *not* using SQL Server -- you could know that by going back
and reading the first sentence of the original post.

And, again, Arvin is wrong about Jet/ACE and SELECT @@IDENTITY -- it
has been supported since the introduction of Jet 4 c. 1999, i.e.,
more than a decade. Arvin being a long-time Access programmer, he
probably didn't notice this, since he had other methods to get the
last-inserted Autonumber. I know I didn't start using SELECT
@@IDENTITY until the last 5 years or so.

 

[Arvin Meyer [MVP]]

IMO, Jet has *never* been a viable RDBMS for websites, no matter how
small. It's simply not designed for that scenario...

That's ridiculous. There are probably thousands of small websites tunning on
Jet databases. I know of several, one of which ran for 8 years getting as
many as 5,000 hits a day. Eventually that company sold out and the buyer
incorporated the data into a much larger website. As recently as 1 year ago
I created a website running a Jet database, and it has been running fine.

What Jet cannot do is handle very high traffic sites. Although I've seen
claims to the contrary, my own experience is that Jet cannot handle multiple
complex queries in high volumes. That's definitely a SQL-Server realm. But
for low traffic work, Jet does just fine.

 

[David W. Fenton]

That's ridiculous. There are probably thousands of small websites
tunning on Jet databases. I know of several, one of which ran for
8 years getting as many as 5,000 hits a day. Eventually that
company sold out and the buyer incorporated the data into a much
larger website. As recently as 1 year ago I created a website
running a Jet database, and it has been running fine.

What Jet cannot do is handle very high traffic sites. Although
I've seen claims to the contrary, my own experience is that Jet
cannot handle multiple complex queries in high volumes. That's
definitely a SQL-Server realm. But for low traffic work, Jet does
just fine.

There are a number of issues with Jet behind a web server.

1. Jet is not thread-safe, which can be a problem for web servers,
from what I understand. ADO somehow gets around that, according to
Michael Kaplan, so you'd want to be sure you're using ADO/OLEDB to
access your data (not ODBC).

2. you can't share the database with interactive client users. If
somebody opens the web back end Access, the web server is locked
out.

3. compacting is problematic, as the web server keeps the back end
open all the time. You might have to shut down your HTTP agent to be
able to compact.

#1 has a workaround, if it's supported.

#2 may not be a problem as most websites aren't going to be sharing
a data file with interactive users. But it does make it impossible
to do the right thing and present the same data to web users as that
seen by Access users.

#3 is not problematic for a read-only (or near read-only) web app,
but for anything with writes, it's going to be an issue. Perhaps not
a big issue (it may be sufficient to stop the web server, compact
and restart the web server once a month or so), but still an issue.

None of these things is an issue with a proper server database
behind your website.

 

[Gregory A. Beamer]

Arvin is wrong about SELECT @@IDENTITY.

And you are wrong about batching SQL statements. Jet/ACE does not
and never has supported that, and the very first sentence of the
post asking the question says that it's a Jet/ACE database, not SQL
Server.

So none of your advice is applicable at all.

I assumed the @@IDENTITY meant the user was against SQL Server and rolled
from there. If he is not using SQL Server, @@IDENTITY will NEVER work. But
if you goal was to publicly state "you're wrong", I am not sure it was
really worth the time, unless you are actually going to help with a
solution. Just my two cents. Your mileage may vary.

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************

 

[Banana]

I assumed the @@IDENTITY meant the user was against SQL Server and
rolled from there. If he is not using SQL Server, @@IDENTITY will NEVER
work.

As a matter of fact, several RDBMS, including Microsoft Jet/ACE as of
version 4.0 support @@IDENTITY - it's hardly exclusive to SQL Server.

 

[David W. Fenton]

I assumed the @@IDENTITY meant the user was against SQL Server and
rolled from there.

And you assumed wrong.

If he is not using SQL Server, @@IDENTITY will NEVER work.

You are wrong here, too. Jet has supported it starting with Jet 4.0,
released in 1999. That's over 10 years, by the way.

But
if you goal was to publicly state "you're wrong", I am not sure it
was really worth the time, unless you are actually going to help
with a solution. Just my two cents. Your mileage may vary.

You made erroneous assumptions and posted an erroneous answer. You
should check your assumptions before posting. That's all I was
suggesting.

When you decide to post an answer, try reading the question. If
something in the question contradicts your assumptions about the
answer to the question (such as the user specifying Jet/ACE and you
thinking @@IDENTITY was limited to SQL Server), you should ask for
clarification, do some investigating of your own, or just hold your
tongue.

 

[Risse]

I assumed the @@IDENTITY meant the user was against SQL Server and rolled
from there. If he is not using SQL Server, @@IDENTITY will NEVER work. But
if you goal was to publicly state "you're wrong", I am not sure it was
really worth the time, unless you are actually going to help with a
solution. Just my two cents. Your mileage may vary.

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************