Retrieving @@Identity value in ado.net

[John]

Hi

I am using below vb.net code to insert a record into an access table and
then retrieve the auto number id using @@Identity;

If LocalConn.State = ConnectionState.Closed Then
LocalConn.Open()
End If
Dim DBCommand As System.Data.OleDb.OleDbCommand
Dim I As Integer
St = "INSERT INTO tblClients ( TempID ) SELECT 123 "
DBCommand = New System.Data.OleDb.OleDbCommand(St, LocalConn)
I = DBCommand.ExecuteNonQuery()
Dim Cmd As OleDb.OleDbCommand
Dim Reader As OleDb.OleDbDataReader
Dim ID As Int32
St = "SELECT @@Identity as ID"
Cmd = New OleDb.OleDbCommand(St, LocalConn)
Reader = Cmd.ExecuteReader()
If (Reader.Read()) Then
ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
End If

The problem is that ID returns a 0 (zero) value instead of the actual id
value. What am I doing wrong?

Many Thanks

Regards

 

[Banana]

Using @@identity is what's wrong. That's a SQL-Server method. Once you've
inserted the record, the Value property of the Key is what you query.

Actually, JET does support @@identity since 4.0.

That said, I do believe it's fussy about the scope, and you're executing
this in a new command, so it's possible that Jet perceives it as a
separate scope. What happens if you use the first command to execute the
second statement? There should be no problem using same command, though
I've not actually tried this before.

 

[a a r o n . k e m p f]

jet doesn't support connection state, right?

so.. you can't do this accurately, right?

only in SQL Server can you accurately determine which number is truly
from your machine / session / connection

-Aaron

 

[Arvin Meyer [MVP]]

Using @@identity is what's wrong. That's a SQL-Server method. Once you've
inserted the record, the Value property of the Key is what you query.

"Select IDFieldName From Tablename"

 

[John]

Tried, no luck. Could it be that db is access 97? I am reasonably sure I
tried something similar with access 2000 db in the past and it worked.

Thanks

Regards

 

[Banana]

Tried, no luck. Could it be that db is access 97? I am reasonably sure I
tried something similar with access 2000 db in the past and it worked.

Aha. If I'm not mistaken, 97 uses Jet 3.5. Hence, no support for
@@Identity. You have to use Jet 4.0 (e.g. Access 2000). BTW, so you
know, it is possible to use Jet 4.0 without Access to go with it. I'm
not sure if you still can download Jet 4.0 as a part of MDAC but if you
can, well, you can do that and thus get the most current functionality.

HTH.

 

[Mark Rae [MVP]]

BTW, so you know, it is possible to use Jet 4.0 without Access to go with
it.

Yes indeed. Many people even now still don't know the difference...

Every version of Access apart from 2007 is now out of mainstream support:
http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=Access&Filter=FilterNO

I'm not sure if you still can download Jet 4.0 as a part of MDAC

http://support.microsoft.com/kb/271908

 

[Banana]

Yes indeed. Many people even now still don't know the difference...

Every version of Access apart from 2007 is now out of mainstream support:
http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=Access&Filter=FilterNO

Fascinating. I kind of expected anything before 2003 to be out of
mainstream support, but not 2003 as well which seem to be out of
mainstream since last year approximately. I don't have anything to back
it up but I'd wager that there are still more 2003 users than there are
2007 users, hence my surprise.

http://support.microsoft.com/kb/271908

Also, FWIW, 2007 is when they forked Jet into ACE. Jet is considered to
be 'deprecated' from the MDAC and has been for long time. There's even
more enhancements to the engine coming 2010. I'm not so sure whether
it's still viable as a standalone engine for say, light use websites as
was popularly done with classic ASP + Jet, though. I say that mainly
because I believe you now can't get ACE without Access as you could with
Jet without Access, but I could be wrong on that point. It's possible
that either SQL Server Compact Edition or SQL Server Express Edition
will be a better choice for ASP.NET, especially that they have native
ADO.NET providers.

 

[Mark Rae [MVP]]

Fascinating. I kind of expected anything before 2003 to be out of
mainstream support, but not 2003 as well which seem to be out of
mainstream since last year approximately.
Correct.


I don't have anything to back it up but I'd wager that there are still
more 2003 users than there are 2007 users, hence my surprise.

No idea either...

Also, FWIW, 2007 is when they forked Jet into ACE. Jet is considered to be
'deprecated' from the MDAC and has been for long time. There's even more
enhancements to the engine coming 2010. I'm not so sure whether it's still
viable as a standalone engine for say, light use websites

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

It's possible that either SQL Server Compact Edition or SQL Server Express
Edition will be a better choice for ASP.NET, especially that they have
native ADO.NET providers.

SQL Server Express, definitely.

SqlCe isn't officially supported for use in disconnected environments like
ASP.NET:
http://www.zorched.net/2007/01/17/using-sql-compact-edition-under-aspnet/

 

[Banana]

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

Interesting. I never actually tried to use Jet as a backend for a
website, mainly because I build database applications, not web
applications. I only went off from what seemed to be high numbers of
articles instructing how to set up Jet with classic ASP. I wasn't too
clear on the rationale, but I think it had to do with that back then it
was a choice between Jet or MSDE. I don't know.

SQL Server Express, definitely.

SqlCe isn't officially supported for use in disconnected environments
like ASP.NET:
http://www.zorched.net/2007/01/17/using-sql-compact-edition-under-aspnet/

Cool. Good to know and thanks for sharing.

 

[David W. Fenton]

I'm
not sure if you still can download Jet 4.0 as a part of MDAC but
if you can, well, you can do that and thus get the most current
functionality.

Why would you need to download something that is part of the
operating system?

 

[David W. Fenton]

Jet is considered to
be 'deprecated' from the MDAC

No, Jet was omitted from the MDAC because it was redundant to
include something that was part of the OS.

Jet was deprecated for use in MS's .NET-based programming languages,
but was never deprecated elsewhere.

and has been for long time.

Jet has been part of the OS since the introduction of Windows 2000
in 1999 (the same year Office 2000 was released, i.e., the first
software that was based on Jet 4).

There's even
more enhancements to the engine coming 2010. I'm not so sure
whether it's still viable as a standalone engine for say, light
use websites as was popularly done with classic ASP + Jet, though.

Jet/ACE is really not suitable for use with web sites.

I say that mainly
because I believe you now can't get ACE without Access as you
could with Jet without Access, but I could be wrong on that point.

You are. You can download the ACE for use without Access.

It's possible
that either SQL Server Compact Edition or SQL Server Express
Edition will be a better choice for ASP.NET, especially that they
have native ADO.NET providers.

CE would not be, but Express would be.

 

[Banana]

No, Jet was omitted from the MDAC because it was redundant to
include something that was part of the OS.

...

Jet has been part of the OS since the introduction of Windows 2000
in 1999 (the same year Office 2000 was released, i.e., the first
software that was based on Jet 4).

But same was true of MDAC itself - it was distributed with several
different Windows OS, starting with NT 4.0 as well in conjuncture with
other distribution methods. As far as I can tell, MDAC was a part of
Windows framework, so your claim seems to be a distinction without a
difference to me.

Jet/ACE is really not suitable for use with web sites.

I'm not doubting this statement, and I'm in no position to take sides.
However, I have to confess my puzzlement because as I already mentioned
to Mark, the subject on using Jet as backend for websites is well
documented and broad, and indeed there are few KB articles discussing
about using Jet with IIS, giving the hint that even if the Jet isn't the
preferred method, it's OK for light use website (otherwise why didn't
MSFT go and say "Do not use Jet with IIS" in those KB articles). For
whatever reasons that still are unclear to me there was at least sizable
interest in using Jet as backend for websites. I'd really love to know
the rationale.

Nonetheless, even if I did end up in web development somehow, I wouldn't
really think to reach for Jet as a store for a website, even for a light
use website.

You are. You can download the ACE for use without Access.

It does seem to be the case. I stand corrected. Thanks.

 

[Tony Toews [MVP]]

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

Well, not quite. Former MVP and current MS employee Michael Kaplan runs
Trigeminal.com with all the pages served from an Access database. Now that's pretty
much read only as I doubt he has updated it much in five or eight years. And he's
the only one doing any updates.

He did this as much to anythnig to disprove blanket comments such as yours. However
I would agree that his website is a very special case and that storing website data
is Access is very much not a good idea.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Mark Rae [MVP]]

However I would agree that his website is a very special case and that
storing
website data is Access is very much not a good idea.

Well there you are, then...

 

[David W. Fenton]

But same was true of MDAC itself - it was distributed with several
different Windows OS, starting with NT 4.0 as well in conjuncture
with other distribution methods. As far as I can tell, MDAC was a
part of Windows framework, so your claim seems to be a distinction
without a difference to me.

Certainly MDAC existed independent of the OS's, but my point is that
many people read the removal of Jet from MDAC as a form of
deprecation, when it was really just practical -- why include in
MDAC something that all modern OS's already have installed? I don't
know the exact timeframe in which Jet was eliminated from MDAC, but
I'm pretty certain it was after WinXP/Server 2003 were
well-established. Yes, of course, if you were using MDAC for
installation on Win9x or NT 4, you'd have to provide Jet separately,
but I'm pretty sure that by 2004 or so, very few developers were
targetting their programs to run on those earlier OS's.

I'm not doubting this statement, and I'm in no position to take
sides. However, I have to confess my puzzlement because as I
already mentioned to Mark, the subject on using Jet as backend for
websites is well documented and broad, and indeed there are few KB
articles discussing about using Jet with IIS, giving the hint that
even if the Jet isn't the preferred method, it's OK for light use
website (otherwise why didn't MSFT go and say "Do not use Jet with
IIS" in those KB articles).

It works fine for prototyping, read-only sites and sites with very
small user populations. But I just don't see the point when there
are almost always more appropriate databases available for any
website back end. About the only time it might be reasonable is if
you're running something from a Windows workstation, in which case
you're serving a very small user population (since only 10 users can
connect to your workstation simultaneously).

Also, I have found that most getting-started web scripting tutorials
seem to date from c. 1998 in regard to the practices they recommend.
And, in general, I find that web-oriented people don't understand
databases at all, and frequently recommend bad practices (such as
denormalized tables, or, pace another thread, storing ZLS's).

For
whatever reasons that still are unclear to me there was at least
sizable interest in using Jet as backend for websites. I'd really
love to know the rationale.

I would attribute it to ignorance on the part of the people writing
the tutorials for getting started with ASP and PHP and other web
scripting languages.

 

[Gregory A. Beamer]

Hi

I am using below vb.net code to insert a record into an access table and
then retrieve the auto number id using @@Identity;

If LocalConn.State = ConnectionState.Closed Then
LocalConn.Open()
End If
Dim DBCommand As System.Data.OleDb.OleDbCommand
Dim I As Integer
St = "INSERT INTO tblClients ( TempID ) SELECT 123 "
DBCommand = New System.Data.OleDb.OleDbCommand(St, LocalConn)
I = DBCommand.ExecuteNonQuery()
Dim Cmd As OleDb.OleDbCommand
Dim Reader As OleDb.OleDbDataReader
Dim ID As Int32
St = "SELECT @@Identity as ID"
Cmd = New OleDb.OleDbCommand(St, LocalConn)
Reader = Cmd.ExecuteReader()
If (Reader.Read()) Then
ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
End If

The problem is that ID returns a 0 (zero) value instead of the actual id
value. What am I doing wrong?

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

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

The second option gives you the ability to either select or return:

SELECT SCOPE_IDENTITY()

RETURN SCOPE_IDENTITY()

If you use return, you can create an out parameter for the return value
(google it) and use that for ID.

I prefer a Repository pattern where you actually return the inserted object
with its ID value, but I prefer state only models.

--
Peace and Grace,
Greg

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

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

 

[Gregory A. Beamer]

Using @@identity is what's wrong. That's a SQL-Server method. Once you've
inserted the record, the Value property of the Key is what you query.

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().

--
Peace and Grace,
Greg

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

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

 

[Gregory A. Beamer]

Using @@identity is what's wrong. That's a SQL-Server method. Once you've
inserted the record, the Value property of the Key is what you query.

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?).

--
Peace and Grace,
Greg

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

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

 

[Gregory A. Beamer]

Let me clarify this farther.

Since you are not getting an error, I assume you are hitting SQL Server. If
so, I would move to a System.Data.SqlClient, as it is more efficient. You
can then either chain the two commands in one statement (a bit sloppy) or
create a stored procedure. You do want the queries in the same command
object rather than creating a new command, as you need to either lock the
table or, at minimum, ensure you are in the same scope. it would be bad for
an insert on one request, then another insert, then an @@IDENTITY Request
from the second insert. Ouch!


--
Peace and Grace,
Greg

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

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