Lowyat.NET Forums

Welcome Guest ( Log In | Register )

3 Pages  1 2 3 > 
RSS feedBump TopicReply to this topicStart new topicStart Poll

Outline · [ Standard ] · Linear+

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

angel-girl
post 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??
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
kiasu_kid
post 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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
taitianhin
post 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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Avex
post 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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
hkk86
post 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.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
silvestrelsl
post 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.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
angel-girl
post 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.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
gs20
post 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.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
angel-girl
post 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?
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
gs20
post 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.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
angel-girl
post 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?
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
gs20
post 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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
angel-girl
post 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??
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
gs20
post 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))
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
angel-girl
post 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....
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
gs20
post 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.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
angel-girl
post 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??
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
gs20
post 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?
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
angel-girl
post 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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
gs20
post 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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post

3 Pages  1 2 3 >
Bump TopicReply to this topicTopic OptionsStart new topic
 



----debug section please ignore----
Lo-Fi Version Time is now: 25th November 2009 - 02:48 PM
All Rights Reserved 2003-2009 Vijandren Ramadass (~living on a prayer~)