Retrieving @@Identity value in ado.net
in [General]
|
Prev: Switchboard in Access 2010
Next: SendObject format
From: David W. Fenton on 18 Mar 2010 10:59 "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in 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;" Um, no, you can't do that. Look at the first line of the post you quote: >> I am using below vb.net code to insert a record into an access >> table and then retrieve the auto number id using @@Identity; "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 http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 18 Mar 2010 11:04 "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in news:E9AF8714-B486-43D5-93FA-C3A292F2FDA8(a)microsoft.com: > 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 http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 18 Mar 2010 11:05 "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in news:F39B0569-8841-43C7-A20D-CEF147E0FC71(a)microsoft.com: > "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(). 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 http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 18 Mar 2010 11:06 "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in news:4609F6EE-F708-418B-BAAF-D7E4BAAF4C7D(a)microsoft.com: > "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?). 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Arvin Meyer [MVP] on 19 Mar 2010 09:04
"Mark Rae [MVP]" <mark(a)markrae.net> wrote in message news:ekUtT3RxKHA.1548(a)TK2MSFTNGP02.phx.gbl... > 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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access |