Get the ID of Last Inserted Record in SQL Server

Submitted by admin on Tuesday, April 24, 2012 - 08:27.

Sometimes you need to know the value of your Primary Key when you insert record in your table. This is true if you have a Master/Detail form and you need to know the Primary key value of the parent table so that you can insert rows in the related table.

Consider the following scenario:

You have and Invoice table with InvoiceDetails as the related table. Invoice table has primary key named “InvoiceID” and in your InvoiceDetails you also have InvoiceID as a foreign key. Before you can insert record in the related table (i.e. in InvoiceDetails), you need to know the value of the InvoiceID from your primary table.

Of course you only need to get the value of InvoiceID if this column is an Identity in your SQL Server table or autoincrement in MS Access database.

The technique is very simple by simply adding the SELECT SCOPE_IDENTITY(); at the end of your INSERT statement. Then using ExecuteScalar() instead of ExecuteNonQuery().

Sample code:

    Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
    Dim identity As Integer
 
    identity = Integer.Parse(cmd.ExecuteScalar().ToString())

To learn more on this matter please refer to “Sample Invoice with Master/Detail Form”.

Comments

How to prevent duplicate data to be inserted in SQL Server

I have some code in vb.net on an asp.net webpage. i want to stop the user from inserting his record data if his personal details already exist in the sql server database table. Here is the code i have. rotected Sub cmdSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSave.Click Dim cnt As Integer = 0 Dim iNo As Integer = 0 Dim Str As String = "" Dim ds As New DataSet Dim DisplayMessage = "Employee record already Exists" Dim DisplayMessage1 = "Employee doesnot Exist" Dim Msg As String = "" Dim ContID As String = "" Msg = "Contractor Employee Record has been Saved Succesfully. Please Search your Entered name in the Contractor Employee List above" If CheckData() = False Then Exit Sub 'If CheckDuplicate() = True Then Exit Sub 'cnt = cMain.GetValInt("Select Count(ID) from T_IC_ContractorStaff Where KOCID = '" & txtKOC.Text & "'") cnt = ds.Tables("T_table").Rows.Count 'If cnt = 0 Then ' lblDisplayMessage.Text = "" 'stit = "Dear User" For i = 0 To cnt - 1 iNo = cMain.GetValInt("SELECT COUNT(*) FROM T_table Where KOCID = '" & txtKOC.Text & "'") If iNo = 0 Then Str = "insert into T_table " & _ " (KOCID,Name,ContractorName,CivilID,PassportNo,Designation,ContractNo,DirID,grpID,TeamID,ContractID,ContractorID) " & _ " values ('" & txtKOC.Text & "','" & txtName.Text & "','" & txtContractor.Text & "','" & txtCivilid.Text & "','" & _ txtPassportNo.Text & "','" & txtDesignation.Text & "','" & txtContractNo.Text & "'," & ddRes_Dir.SelectedValue & "," & _ ddGrp.SelectedValue & "," & ddOrgLoc.SelectedValue & "," & ddContractNo.SelectedValue & "," & ddContractor.SelectedValue & ")" 'lblDisplayMessage.Text = "" Else lblDisplayMessage.Text = DisplayMessage 'If cnt = 1 Then Exit Sub End If Next Dim cmd As New OleDbCommand(Str, cnn) Try cnn.Open() cmd.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message & ex.StackTrace) Finally cnn.Close() End Try lblRecordSaved.Text = Msg End Sub

Add new comment

Add new comment
131 views