Main Page | Report Page

 

  .NET DotNet Forum Index » ADO .NET Forum » Retrieving at (no spam) at (no spam) Identity value in ado.net...

Author Message
John...
Posted: Mon Mar 15, 2010 4:21 pm
 
Hi

I am using below vb.net code to insert a record into an access table and
then retrieve the auto number id using at (no spam) at (no spam) 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 at (no spam) at (no spam) 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
 
a a r o n . k e m p f at (no spam) g m a i l . c o m...
Posted: Mon Mar 15, 2010 4:21 pm
 
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






On Mar 15, 5:42 pm, Banana <Banana at (no spam) Republic> wrote:
Quote:
Arvin Meyer [MVP] wrote:
Using at (no spam) at (no spam) 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 at (no spam) at (no spam) 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.

 
Banana...
Posted: Mon Mar 15, 2010 6:42 pm
 
Arvin Meyer [MVP] wrote:
Quote:
Using at (no spam) at (no spam) 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 at (no spam) at (no spam) 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.
 
Arvin Meyer [MVP]...
Posted: Mon Mar 15, 2010 7:17 pm
 
Using at (no spam) at (no spam) 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"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"John" <info at (no spam) nospam.infovis.co.uk> wrote in message
news:%23Kn8L3IxKHA.3304 at (no spam) TK2MSFTNGP06.phx.gbl...
Quote:
Hi

I am using below vb.net code to insert a record into an access table and
then retrieve the auto number id using at (no spam) at (no spam) 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 at (no spam) at (no spam) 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

 
John...
Posted: Mon Mar 15, 2010 8:48 pm
 
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" <Banana at (no spam) Republic> wrote in message
news:4B9ED3DA.7050106 at (no spam) Republic...
Quote:
Arvin Meyer [MVP] wrote:
Using at (no spam) at (no spam) 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 at (no spam) at (no spam) 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.

 
Banana...
Posted: Mon Mar 15, 2010 9:03 pm
 
John wrote:
Quote:
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
at (no spam) at (no spam) 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]...
Posted: Tue Mar 16, 2010 5:38 am
 
"Banana" <Banana at (no spam) Republic> wrote in message
news:4B9EF51E.2080107 at (no spam) Republic...

Quote:
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


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

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



--
Mark Rae
ASP.NET MVP
http://www.markrae.net
 
Banana...
Posted: Tue Mar 16, 2010 9:01 am
 
Mark Rae [MVP] wrote:
Quote:
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.

Quote:
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]...
Posted: Tue Mar 16, 2010 9:31 am
 
"Banana" <Banana at (no spam) Republic> wrote in message
news:4B9F9D5C.8020004 at (no spam) Republic...

Quote:
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.

Correct.


Quote:
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...


Quote:
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...

Quote:
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/


--
Mark Rae
ASP.NET MVP
http://www.markrae.net
 
David W. Fenton...
Posted: Tue Mar 16, 2010 1:26 pm
 
Banana <Banana at (no spam) Republic> wrote in news:4B9EF51E.2080107 at (no spam) Republic:

Quote:
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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 
David W. Fenton...
Posted: Tue Mar 16, 2010 1:29 pm
 
Banana <Banana at (no spam) Republic> wrote in news:4B9F9D5C.8020004 at (no spam) Republic:

Quote:
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.

Quote:
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).

Quote:
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.

Quote:
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.

Quote:
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 
Tony Toews [MVP]...
Posted: Tue Mar 16, 2010 8:20 pm
 
"Mark Rae [MVP]" <mark at (no spam) markrae.net> wrote:

Quote:
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]...
Posted: Wed Mar 17, 2010 8:52 am
 
"Tony Toews [MVP]" <ttoews at (no spam) telusplanet.net> wrote in message
news:2se0q5pnngkgn8fn3p7tdacs49j0rrji4n at (no spam) 4ax.com...

Quote:
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...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net
 
Gregory A. Beamer...
Posted: Wed Mar 17, 2010 12:29 pm
 
"John" <info at (no spam) nospam.infovis.co.uk> wrote in message
news:#Kn8L3IxKHA.3304 at (no spam) TK2MSFTNGP06.phx.gbl...
Quote:
Hi

I am using below vb.net code to insert a record into an access table and
then retrieve the auto number id using at (no spam) at (no spam) 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 at (no spam) at (no spam) 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 at (no spam) at (no spam) IDENTITY;"

2. Make a stored procedure that returns the IDENTITY (in this case, I would
use SCOPE_IDENTITY() rather than at (no spam) at (no spam) 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: at (no spam) gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************
 
Gregory A. Beamer...
Posted: Wed Mar 17, 2010 12:30 pm
 
"Arvin Meyer [MVP]" <arvinm at (no spam) mvps.invalid> wrote in message
news:OkqjH4JxKHA.1796 at (no spam) TK2MSFTNGP02.phx.gbl...
Quote:
Using at (no spam) at (no spam) 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: at (no spam) gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************
 
 
Page 1 of 2    Goto page 1, 2  Next
All times are GMT - 5 Hours
The time now is Thu Apr 24, 2014 1:45 am