Retrieving @@Identity value in ado.net
in [General]
|
Prev: Switchboard in Access 2010
Next: SendObject format
From: David W. Fenton on 17 Mar 2010 13:35 Banana <Banana(a)Republic> wrote in news:4B9FFCF4.8040900(a)Republic: > David W. Fenton wrote: >> 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. 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. >> 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). 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Gregory A. Beamer on 17 Mar 2010 14:29 "John" <info(a)nospam.infovis.co.uk> wrote in message news:#Kn8L3IxKHA.3304(a)TK2MSFTNGP06.phx.gbl... > 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! | ************************************************
From: Gregory A. Beamer on 17 Mar 2010 14:30 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message news:OkqjH4JxKHA.1796(a)TK2MSFTNGP02.phx.gbl... > 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! | ************************************************
From: Gregory A. Beamer on 17 Mar 2010 14:34 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message news:OkqjH4JxKHA.1796(a)TK2MSFTNGP02.phx.gbl... > 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! | ************************************************
From: Gregory A. Beamer on 17 Mar 2010 14:36
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! | ************************************************ "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in message news:2E20495F-018B-4FA9-BD7A-31957DDE32AD(a)microsoft.com... > > > "John" <info(a)nospam.infovis.co.uk> wrote in message > news:#Kn8L3IxKHA.3304(a)TK2MSFTNGP06.phx.gbl... >> 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! | > ************************************************ |