Help needed about getting last record ID

[nikel]

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

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.

[jmcilhinney]

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

[jmcilhinney]

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.

[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.

[jmcilhinney]

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?

[nikel]

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:

[Leary222]

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

[nikel]

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

[Leary222]

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

[nikel]

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.

[Leary222]

Result = my command. Executescalar

[nikel]

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

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

ID.ToString() =

[Leary222]

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

[jmcilhinney]

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

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

ID.ToString() =

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

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

That would need to be either this:

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

or this:

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

[nikel]

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