• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

How to get the Identity key of a newly added row of a dataset

I need to retrieve the Identity of the record created.

        MyConnection.ConnectionString = GetConnection(CurrentProject.DBName)
        sQuery = "select * from tblEmployee where EmployeeID = " & iEmployeeID
        MyDataAdapter = New SqlDataAdapter(sQuery, MyConnection)
        MyDataSet = New DataSet("Employee")
        MyDataAdapter.FillSchema(MyDataSet, SchemaType.Source, "tblEmployee")
        MyDataAdapter.Fill(MyDataSet, "tblEmployee")
        MyDataTable = MyDataSet.Tables("tblEmployee")
	If MyDataTable.Rows.Count = 0 Then
		MyDataRow = MyDataTable.NewRow()

		All the data fields are set here
		Except the Identity column

		objCommandBuilder = New SqlCommandBuilder(MyDataAdapter)
		MyDataAdapter.Update(MyDataSet, "tblEmployee")

		'This is where I am having problems
		iEmployeeID = CInt(MyDataSet.Tables(0).Rows(0).Item("EmployeeID"))
		MyDataRow.Item("ADPNo") = "N" & iEmployeeID
		MyDataAdapter.Update(MyDataSet, "tblEmployee")

		SaveEmployee = True
	end if

Open in new window

1 Solution
Jacques Bourgeois (James Burger)PresidentCommented:
Call SELECT @@IDENTITY on the same connection.

This returns the last identity key generated on the connection.

Dim id As Integer 'Or whatever type it is
Dim cmd As New OleDBCommand("SELECT @@IDENTITY") 'Or another Command object if not on OleDB
id = Cint(cmd.ExecuteScalar)

Open in new window

etsellincAuthor Commented:
Didn't think to use the command object. Thought the data row would have the answer.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now