Retrieve last record ID

Lowyat.NET Lowyat.NET Rules and Regulations

FAQ Help Search Members Calendar

Outline · [ Standard ] · Linear+

Retrieve last record ID, VB.net (.NET)

Share on

| Track this topic | Email this topic | Print this topic

angel-girl

Nov 5 2009, 01:27 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #1

Getting Started

Group: Junior Member
Posts: 166
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Nov 2008

i dun wan to use auto number in microsoft access so i save my ID start at 1
but to avoid same number insert,i would like to check last record number n make it +1
i'd googled around n i found sumthing like @@identity ....
but i dun seems to be understand how is it working...
anyone??

kiasu_kid

Nov 5 2009, 01:35 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #2

Getting Started

Group: Junior Member
Posts: 155
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Sep 2007

you can read the data from the database in descending order then read the first ID

taitianhin

Nov 5 2009, 02:15 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #3

Getting Started

Group: Junior Member
Posts: 82
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Apr 2005
From: too far to see

for practise, to avoid the same number being insert into ur table, you should try to
1. select the number 1st then only
2. insert a new record
or dont insert if record is found while selecting. this way you don have to sort the record or select last record

Avex

Nov 5 2009, 10:35 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #4

Getting Started

Group: Junior Member
Posts: 264
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003
From: Pedoland

For system like i deal with everyday as in few hundred tables, there is a master table which keeps track of all the other tables' id. If one table increase by few records, it will update the master table as well. Less headache to keep on writing using records.count

This post has been edited by Avex: Nov 5 2009, 10:37 PM

hkk86

Nov 6 2009, 02:17 AM

Show posts by this member only |This post's rating (0+, 0-) | Post #5

Getting Started

Group: Junior Member
Posts: 75
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jul 2007

select @@identity from [table] will gives u an id
or u can select max([idcolumn]) ,assuming u use an number base datatype
anyway, tracking id manually is a tedious job when u consider concurrency issue, if it is stand alone and single instance then u have no problem.
if u are willing, u can look into uuid,
else, u r better off to let the db handle concurrency.

silvestrelsl

Nov 6 2009, 09:49 AM

Show posts by this member only |This post's rating (0+, 0-) | Post #6

Getting Started

Group: Junior Member
Posts: 99
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Aug 2009
From: Cyberjaya

Right, I agree with you about manual tracking id is a tedious job, and you may miss the id when it comes to concurrency.

However, you may add a constraint rule for the table to check ID duplication.

angel-girl

Nov 6 2009, 10:33 AM

Show posts by this member only |This post's rating (0+, 0-) | Post #7

Getting Started

Group: Junior Member
Posts: 166
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Nov 2008

alrite...thx for suggestion..
if i let database to do autonumber,how can i retrieve the new record ID?
as for refrence,i wan to noe wat is the number now.

gs20

Nov 6 2009, 12:51 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #8

Enthusiast

Group: Senior Member
Posts: 736
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003
From: Subang Jaya

You don't have to retrieve the new id.
The new ID will be generated when you insert the new record.
Meaning to say, in your Insert SQL statement, you don't have to specify a value for the ID column. For example:

CODE

Insert into student (name,password) values ('name','password')

Even though the column 'ID' exist.

angel-girl

Nov 6 2009, 12:58 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #9

Getting Started

Group: Junior Member
Posts: 166
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Nov 2008

yea i noe wat u mean,i would to hav the ID for sumthing like refrence,so i nid 2 check the current ID....can i do tat?

gs20

Nov 6 2009, 01:00 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #10

Enthusiast

Group: Senior Member
Posts: 736
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003
From: Subang Jaya

In that case, there are two ways to achieve that:

i) Like what other posters suggested, get the last record ID + 1.

ii) Insert the record and get the last record ID.

angel-girl

Nov 6 2009, 01:38 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #11

Getting Started

Group: Junior Member
Posts: 166
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Nov 2008

yup i noe there is sumthing called @@identity....
i have got alot of example but looks messy n complicated,
any of u have sum simple example?

gs20

Nov 6 2009, 04:10 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #12

Enthusiast

Group: Senior Member
Posts: 736
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003
From: Subang Jaya

CODE

Dim iId as integer = nothing
Dim oConnection As SqlConnection = New SqlConnection(ConnectionString)
Dim oReader As SqlDataReader = Nothing
Try
Dim oCommand As SqlCommand = New SqlCommand("select id from student order by id desc limit 0,1", oConnection)
oConnection.Open()
oReader = oCommand.ExecuteReader
While oReader.Read
iId = oReader("id")
End While
Catch ex As Exception
Finally
If Not oReader Is Nothing Then
oReader.Close()
End If
If Not oConnection Is Nothing Then
oConnection.Close()
End If
End Try

angel-girl

Nov 6 2009, 04:14 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #13

Getting Started

Group: Junior Member
Posts: 166
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Nov 2008

erm,how i do i show it on the textbox??

gs20

Nov 6 2009, 04:22 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #14

Enthusiast

Group: Senior Member
Posts: 736
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003
From: Subang Jaya

I guess I should leave some room for you to explore yourself.

Here are the tips:
i) Add a textbox control to wherever you want it to be.
ii) Give it a name.
iii) Copy paste that code into somewhere near the "page_load"
iv) Do the necessary changes.
v) Assign the value that you retrieve to the textbox (hint hint: refer to (ii))

angel-girl

Nov 6 2009, 04:41 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #15

Getting Started

Group: Junior Member
Posts: 166
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Nov 2008

yes i did tat,i created a textbox and assign the valu in iID into the textbox,but ntg show up....

gs20

Nov 6 2009, 04:43 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #16

Enthusiast

Group: Senior Member
Posts: 736
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003
From: Subang Jaya

Do a step-by-step trace in debug mode, make sure the code executes in the right order & also make sure there are at least one record in your db.

angel-girl

Nov 6 2009, 04:45 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #17

Getting Started

Group: Junior Member
Posts: 166
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Nov 2008

CODE

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\database.mdb.mdb"
con.Open()

sql = "SELECT * FROM option1"

da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "Option1")
da.Dispose()

MaxRows = ds.Tables("option1").Rows.Count

inc = 0

Dim iId As Integer = Nothing

Dim oReader As OleDbDataReader = Nothing
Try
Dim oCommand As OleDbCommand = New OleDbCommand("Select OrderID From option1 ORDER BY OrderId DESC LIMIT 0,1", con)

oReader = oCommand.ExecuteReader
While oReader.Read
iId = oReader("OrderID")

End While
TextBox1.Text = iId
Catch ex As Exception
Finally
If Not oReader Is Nothing Then
oReader.Close()
End If
If Not con Is Nothing Then
con.Close()
End If
End Try

like tis??

gs20

Nov 6 2009, 04:46 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #18

Enthusiast

Group: Senior Member
Posts: 736
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003
From: Subang Jaya

Oh, btw what database are you using?

angel-girl

Nov 6 2009, 04:52 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #19

Getting Started

Group: Junior Member
Posts: 166
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Nov 2008

access 2007 but i used as mdb

gs20

Nov 6 2009, 04:53 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #20

Enthusiast

Group: Senior Member
Posts: 736
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003
From: Subang Jaya

You have to replace

CODE

select id from student order by id desc limit 0,1

to

CODE

select top 1 id from student order by id desc

« Next Oldest · Codemasters · Next Newest »