Retrieving @@Identity value in ado.net
in [General]
|
Prev: Switchboard in Access 2010
Next: SendObject format
From: John on 15 Mar 2010 18:21 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
From: Arvin Meyer [MVP] on 15 Mar 2010 21:17 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" -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "John" <info(a)nospam.infovis.co.uk> wrote in message news:%23Kn8L3IxKHA.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? > > Many Thanks > > Regards >
From: Banana on 15 Mar 2010 20:42 Arvin Meyer [MVP] wrote: > 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.
From: a a r o n . k e m p f on 15 Mar 2010 20:54 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(a)Republic> wrote: > Arvin Meyer [MVP] wrote: > > 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.
From: John on 15 Mar 2010 22:48
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(a)Republic> wrote in message news:4B9ED3DA.7050106(a)Republic... > Arvin Meyer [MVP] wrote: >> 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. |