Retrieving last inserted ID from MS Access database

Question

I have the following set of code for a Sub program. It's inserting a row into a MSAccess Database using data provided in the containing form. What I would like to do is grab the ID number of this added record so that it can be set for a property of a window that is invoked when successfully added. I tried looking this up but I get something about @@IDENTITY but it's using an entirely different way of connecting.

Private Sub CreateTournament_Click(sender As System.Object, e As System.EventArgs) Handles CreateTournament.Click
    ' TODO: Check the form for errors, or blank values.
    ' Create the tournament in the database, add the values where needed. Close the form when done.

    Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Dim icount As Integer
    Dim str As String

    Try
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Master.mdb'")
        cn.Open()
        str = "insert into Tournaments (SanctioningID,TournamentName,TournamentVenue,TournamentDateTime,TournamentFirstTable,Game,Format,OrganizerID) values(" _
            & CInt(SanctioningIDTxt.Text) & ",'" & Trim(TournamentNameTxt.Text) & "','" & _
            "1" & "','" & EventDateTimePck.Value & "','" & TableFirstNumberNo.Value & "','" & GameList.SelectedIndex & "','" & FormatList.SelectedIndex & "','" & Convert.ToInt32(ToIDTxt.Text) & "')"

        'string stores the command and CInt is used to convert number to string
        cmd = New OleDbCommand(Str, cn)
        icount = cmd.ExecuteNonQuery
        MessageBox.Show(icount)
        'displays number of records inserted
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try

    Me.Close()

    Dim n As New TournamentWindow ' Open a new Tournament window if everything is successful
    n.TournID = Counter '<< This should be set to the ID of the most recently inserted row
    n.Show(HomeForm)'Invoke the form and assign "HomeForm" as it's parent.

End Sub

score 1 · Accepted Answer · answered Feb 23 '12 at 17:31

1

Assuming you have an auto increment column in the Tournaments table, you can do a "SELECT @@IDENTITY" to get the id of the last inserted record.

BTW, is the SanctioningIDTxt.Text unique? If so, can't you use that?

answered Feb 23 '12 at 17:31

José Mira

641
7
4

It's not. In the event that someone doesn't have a sanctioning number, it's default to -1 instead. So there can be multiple -1 values. Question about @@IDENTITY, Not that it can happen but what if someone else was to also add a record, would it give the very last one or the one which I inserted? – Paul Williams Feb 23 '12 at 17:32
It will give you the very last one. – José Mira Feb 23 '12 at 17:40
Guess I'll have to make sure the database is locked and only used by one instance of the program. Thanks. – Paul Williams Feb 23 '12 at 17:48
1

In Access, the ID returned is the last for the connection instance. I have not tested with c#, but I imagine it is the same. it is easy enough to test, pause your code, edit access manually and then continue the code. See http://stackoverflow.com/questions/595132/how-to-get-id-of-newly-inserted-record-using-excel-vba/595271#595271 – Fionnuala Feb 23 '12 at 20:47
1

No need for locking the database. As pointed out, it based on conneciton, so if there is 1 or 10 users, you always get your last ID, not the ID of others. – Albert D. Kallal Feb 23 '12 at 21:49

Retrieving last inserted ID from MS Access database

1 Answers1

Linked

Related