How to get the primary key after inserting a record

Question

Hi all,

After inserting a new record, I want to know how to get the primary key which belongs to the inserted record? The primary key is identity in the database.

Answer 1

hi,

look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconretrievingidentityorautonumbervalues.asp

---

Regards, Nico

Answer 2

SELECT @IDENT_Current, returns the latest identity value for the session, write this tep next to the insert

---

Abhinav

Answer 3

Nico's link is good (use SCOPE_IDENTITY) ... but it's specific to SQL Server. You didn't specify what database you were using ... if it's not SQL Server, you may need some other ideas (I can't help you with other databases though, I only use SQL Server).

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 4

Hi

All the replies above can help you.

And I add this working snippet code that I use with SQl Server database.

I create a function that returns an integer in order to get that ID inserted .

 Public Function insertfunction() As Integer

        Dim con As New SqlConnection("Data Source=server;Initial Catalog=urdatabase;Persist Security Info=True;User ID=sa;Password=password")
        Dim sqlStatement As String = "insert into Interventions(Code_client,code_mission,Obs,utilisateur,date_modification,Type_intervention, Désignation, Date, Etat,Contexte) values (@Code_client,@Code_mission,@Obs,@utilisateur,getdate(),'Téléphone', '', getdate(), 'Brouillon','')"
        Try

            con.Open()
            Dim cmd As New SqlCommand(sqlStatement, con)
            cmd.Parameters.AddWithValue("@Code_mission", Txt1.Text)
            cmd.Parameters.AddWithValue("@Code_client", cbo1.Text)
            cmd.Parameters.AddWithValue("@obs", txt6.Text)
            cmd.Parameters.AddWithValue("@utilisateur", Me.User_name)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
            Dim x As Integer = cmd.ExecuteScalar
            Return x
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String = "insert  Error:"
            msg += ex.Message

            Throw New Exception(msg)
        Finally

            con.Close()
        End Try

    End Function

And then get that value after insert like that:

dim urvalue as integer = insertfunction()

Regards 

---

Best Regards...Please mark as answer if my post is helpful http://yosr-jemili.blogspot.com

Answer 5

SELECT @@IDENTITY
Use SqlCommand and set the CommandText to "Select @@identity" and use ExecuteScalar method to get the result.

---

Ali Hamdar (alihamdar.com - www.ids.com.lb)

Answer 6

@@IDENTITY is not recommended, mainly because triggers could cause @@IDENTITY to return the wrong key. SCOPE_IDENTITY() is the preferred way to do this. Do a Google search on "scope_identity vs @@identity" for some reading on the subject.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 7

Doreaemon,

Be aware that with scope identity, @@Identity and @Ident you get the last inserted key in the database. 

Not very much related with datasets as a dataset can insert thousands of rows and therefore keys where many users can do that in non sequential procedures. 

Try to do your updates well, then you don't need the primary key if that is an auto identifier.

Read for this the articles about AutoIncrement (See and Step)

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.autoincrementseed(v=vs.100).aspx

The one I show above are more meant for operations with the executeNonQuery

If you want to avoid this completely create yourself the keys and then the GUID to keep it unique.

---

Success
Cor