How to grab the autoincrement value after Insert?

[Dave Clark I]

Is there a way to easily find out what is the resulting value in an autoincrement column after issuing the following against an Access database:

conn.Execute "Insert ..."

For example, with PHP and MySQL, there is a MySQL function you call to get the last autoincrementd value after SQL Insert over a particular database connection. Can this be done with ADO/Access? ...or, do you have to execute a Select query to try to find this information? What?

Thanks.

[Dave Clark I]

OK, I think I Googled out my own answer:

rst.Open "SELECT myID = @@IDENTITY;", conn
myID = rst.Fields("myID").Value

Thanks.

[hspc]

Hi
Instead of sending 2 queries to the DB
I prefer to use a stored procedure to make the task...

It should look like :

Create Proc InsertProc(@paramerter .............)
As
Insert into ..... values ....

return SCOPE_IDENTITY( )
Go

[Krzemo]

Dave Clark I was asking for solution against Microsoft Access database , not an Microsoft SQL Server database...

Best regards,
Krzemo.

[Dave Clark I]

Yep, in fact I found that I could not even string two SQL statements (Insert followed by Select) together in the same call (separated by a semicolon) without something complaining.
(ADO? Access? I don't know.)

[erickwidya]

..in fact I found that I could not even string two SQL statements (Insert followed by Select) together in the same call (separated by a semicolon) without something complaining.

try something like this

sSQL = "INSERT INTO dest_table " & _
"SELECT * FROM source_table

or u can do something like this

sSQL= "INSERT INTO dest_table(field1, field2, field3) " & _
"SELECT field1, field2, field3 FROM source_table"
PS : for that to work..u have to got same field and same datatype for the field u wish to INSERT

hope it can works

[Dave Clark I]

I don't know, but it looks like those suggestions would be for selecting data from one table with which to insert into another table -- i.e., using a sub-select. But, I've not tried merely omitting the semicolon. Otherwise, it seems that the following syntax doesn't work in MS Access databases either:

rst.Open "SELECT myID = @@IDENTITY;", conn
myID = rst.Fields("myID").Value

Seems that might also be just for SQL Server. The following worked:

rst.Open "SELECT @@IDENTITY;", conn
myID = rst.Fields(0).Value

I'll have to go try it this way, too -- to see what happens:

rst.Open "SELECT @@IDENTITY As myID;", conn
myID = rst.Fields("myID").Value

[erickwidya]

I don't know, but it looks like those suggestions would be for selecting data from one table with which to insert into another table yes it is..seem i'm not read carefully..if u want to add syntax after the semicolon, i think it can work if u creating PARAMETERS for Parameterized Query..but that's different case i think..semicolon is like a stop sign (my opinion)

rst.Open "SELECT myID = @@IDENTITY;", conn what is myID? is it a variable u want that query to returned? if it is, that it can not work with Access..it work for MS SQL

for u original question..i u want to retrieve the last increment value maybe u can try somehting like this..


...
'add this after ur INSERT query

rs.open "SELECT TOP 1 auto_inc FROM tbl1 ORDER BY auto_inc DESC", conn
'then u can do this
myID=rs("auto_Inc")


hope u understand

[Krzemo]

or u original question..i u want to retrieve the last increment value maybe u can try somehting like this..

It is much much simpler..:
rst.Open "SELECT @@IDENTITY;", conn

Krzemo.

[erickwidya]

Krzemo
rst.Open "SELECT @@IDENTITY;", conn is it can be done in Access?

[Krzemo]

is it can be done in Access?Yes!:thumb:

[erickwidya]

woow..it's new to me..
can u explain a bit more about this Krzemo? with example maybe? ;)

thx

[Krzemo]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconretrievingidentityorautonumbervalues.asp

:rolleyes:

And an example from it:
[Visual Basic]

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic

Public class Sample

Shared nwindConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Program Files\Microsoft Office\Office\Samples\northwind.mdb;")

Public Shared Sub Main()

' Use the DataAdapter to fill and update the DataSet.
Dim catDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID", nwindConn)

catDA.InsertCommand = New OleDbCommand("INSERT INTO Categories (CategoryName) Values(?)", nwindConn)
catDA.InsertCommand.CommandType = CommandType.Text

catDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName")

nwindConn.Open()

' Fill the DataSet.
Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")

' Add a new row.
Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)

' Include an event to fill in the Autonumber value.
AddHandler catDA.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)

' Update the DataSet.
catDA.Update(catDS, "Categories")

nwindConn.Close()
End Sub

Private Shared Sub OnRowUpdated(sender As Object, args As OleDbRowUpdatedEventArgs)
' Include a variable and a command to retrieve the identity value from the Access database.
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", nwindConn)

If args.StatementType = StatementType.Insert
' Retrieve the identity value and store it in the CategoryID column.
newID = CInt(idCMD.ExecuteScalar())
args.Row("CategoryID") = newID
End If
End Sub
End Class

Hope it helps.

[erickwidya]

thx for the link Krzemo..but i'm afraid i'm not familliar with the syntax since it use .NET but i get the 'logic' now..

thx for the info

[Krzemo]

but i'm afraid i'm not familliar with the syntax since it use .NET example in VBA (Access 2000):

Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
cmd.CommandText = "INSERT INTO MyTable(Parametr,ParValue) VALUES('y',1)"
cmd.CommandType = adCmdText
cmd.ActiveConnection = CurrentProject.Connection
cmd.Execute
rst.Open "SELECT @@IDENTITY", CurrentProject.Connection
Debug.Print rst.Fields(0)


Your solution: "SELECT TOP 1 auto_inc FROM tbl1 ORDER BY auto_inc DESC"
has a risk of concurency problem. If another user (from different computer for example) insert a value beteen INSERT and SELECT than U will grab wrong value.

Variable @@IDENTITY is maintained per user session and updated only with values from inserts in current session, so that risk is smaller (it increases if U multi-thread a lot :D ).


Best regards,
Krzemo.
:wave:

[erickwidya]

not familliar with VBA too :D only familliar with VB ;)
i try that at Access query, it only returned empty row with several row depends on how many row inserted before at related table..not returned a value which is (from the reading) should be the last value of identity field..

If another user (from different computer for example) insert a value beteen INSERT and SELECT than U will grab wrong value. yess i'm aware, that's why never use auto_number as PK anymore :)

Than "Rate this Post" already try that but it said i have to spread to others :p

regards

[erickwidya]

Change "CurrentProject.Connection" to a valid ADODB connection object and U have VB code CurrentProject.Connection is contain the Database not the tables, if i got 2 or more table that got auto_number then how can i be sure it point to the specific table i want? since never tried it with VB..but it's my thought when i saw the MSDN site..

from that site..it use DataTable which already SELECT the appropriate table (Categories)..but in this case :confused:

PS : i try at Access 2000

hope u understand what i'm saying

[Krzemo]

CurrentProject.Connection is contain the Database not the tables It is acctualy ADODB.Connection. @@IDENTITY is maintained for all tables available for that connection. It is designed to retrieve value (disregarding from witch table) of the last INSERT. You should use that value immediately after insert (it has no origin information) and only after INSERT it has any value. Since U develop in VB6 U can depend on that value (VB6 does not support multithreading, which can be walked around only by using COM object)

hope u understand too :)

[erickwidya]

It is acctualy ADODB.Connection yes, i agree. it just my assumption to make simple to understand to me :D

It is designed to retrieve value (disregarding from witch table) of the last INSERT. You should use that value immediately after insert (it has no origin information) and only after INSERT it has any value now it started to make sense..from where u got that desc? from the link u post earlier??

thx for the explanation Krzemo :thumb:

[Krzemo]

from where u got that desc? from the link u post earlier?? from expirience ...:D

[erickwidya]

o..he2 why don't u said earlier :blush: (coz i never asked before right? :D)

thank you very much, really appreciated..