Help needed about getting last record ID

Hello. I'm trying to get last record id of a table. I found some code on the net and my code so far:

Code:

---

If IsTimeExists(currentTime) = True Then

        ' If currentTime exists, skip inserting, get last id
        Dim dbStr As String = "SELECT * FROM lastChange_tbl WHERE lastChange = ?"
        ' Get last record id
        Dim query2 As String = "Select @@Identity"
        Dim ID As Integer
        Using Connection
                Using command2 As New OleDbCommand(dbStr, Connection)
                        command2.Parameters.AddWithValue("", currentTime)
                        If Not Connection.State = ConnectionState.Open Then
                                Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + appPath + "\pages.mdb"
                                Connection.Open()
                        End If
                        command2.ExecuteNonQuery()
                        command2.CommandText = query2
                        ID = command2.ExecuteScalar()
                End Using
        End Using

Else

---

This code inserts new record while I just want to get the last id. How can I fix this?


Edit: It's an Access (mdb) database.

To get the last generated ID you use that "SELECT @@IDENTITY". Just create a command with that SQL code and call ExecuteScalar.

That said, under exactly what circumstances do you want to use this? There are reasons to want the last generated ID but rarely in isolation like that. There's every chance that there is a better way to achieve whatever it is that you're trying to do.

Thank you for your reply. I'm trying to prevent duplicates. My current program is inserting currentTime to the table everytime my function is called. I don't know whether it's important but the database has relationships.

Quote:

---

Originally Posted by nikel

Thank you for your reply. I'm trying to prevent duplicates. My current program is inserting currentTime to the table everytime my function is called. I don't know whether it's important but the database has relationships.

---

That doesn't really make any sense. If @@IDENTITY is to be any use then you'd have to be using AutoNumber columns and they will prevent duplicates all on their own, which is the whole point of their existence. You add an AutoNumber column to your table and make it the primary key and hey presto!

Are you talking about avoiding duplicates in some other column? If so then why are you not getting the last value from that column rather than the last ID?

I think I couldn't explain it clearly. I need to get record id because I'll insert it to main table. It's hard for me to give more details because I don't know English and SQL terms well.

I mean I have tables named products_tbl and lastChange_tbl. id in lastChange_tbl table and lastChange_id in products_tbl has relations. If specific record exists in lastChange_tbl, it must skip inserting new record, get the last id and put it to lastChange_id in products_tbl. Please see the diagram below:

Attachment 124769

SELECT TOP 1 [ColumnName] ORDER BY [ColumnName] DESC

It didn't work for me. Any other ideas?

Did you replace [column-name]with your I'D column field name

Quote:

---

Originally Posted by Leary222

Did you replace [column-name]with your I'D column field name

---

Thanks for helping. Actually I couldn't find a way to get the id and set it to a variable.

By the way I'm guessing that you mean the lastChange_tbl table's id column.

Result = my command. Executescalar

I couldn't make it work. I also tried this

Code:

---

Imports System.Data.OleDb
Imports System.Reflection
Imports System.IO

Public Class Form1

    Dim Connection As New OleDb.OleDbConnection
    Dim currentTime As Date = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
    Dim loc = Assembly.GetExecutingAssembly().Location
    Dim appPath = Path.GetDirectoryName(loc)

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        If Not Connection.State = ConnectionState.Open Then
            Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + appPath + "\pages.mdb"
            Connection.Open()
        End If

        Dim ID As Integer
        Dim dbStr As String = "SELECT MAX(id) FROM lastChange_tbl"

        Using Connection
            Using command2 As New OleDbCommand(dbStr, Connection)
                command2.CommandText = dbStr
                ID = command2.ExecuteScalar()
            End Using
        End Using

        Console.WriteLine("ID.ToString() = ", ID.ToString())

    End Sub

End Class

---

This just outputs

Code:

---

ID.ToString() =

---

your not using the SQL string i previously gave to you.

Quote:

---

Originally Posted by nikel

I couldn't make it work. I also tried this

Code:

---

Imports System.Data.OleDb
Imports System.Reflection
Imports System.IO

Public Class Form1

    Dim Connection As New OleDb.OleDbConnection
    Dim currentTime As Date = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
    Dim loc = Assembly.GetExecutingAssembly().Location
    Dim appPath = Path.GetDirectoryName(loc)

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        If Not Connection.State = ConnectionState.Open Then
            Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + appPath + "\pages.mdb"
            Connection.Open()
        End If

        Dim ID As Integer
        Dim dbStr As String = "SELECT MAX(id) FROM lastChange_tbl"

        Using Connection
            Using command2 As New OleDbCommand(dbStr, Connection)
                command2.CommandText = dbStr
                ID = command2.ExecuteScalar()
            End Using
        End Using

        Console.WriteLine("ID.ToString() = ", ID.ToString())

    End Sub

End Class

---

This just outputs

Code:

---

ID.ToString() =

---

---

Um, what exactly do you think this is going to do:

Code:

---

Console.WriteLine("ID.ToString() = ", ID.ToString())

---

That would need to be either this:

Code:

---

Console.WriteLine("ID.ToString() = " & ID)

---

or this:

Code:

---

Console.WriteLine("ID.ToString() = {0}", ID)

---

Wow! It was just a stupid mistake. Thanks for the helps.