• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • 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)
        MyConnection.Open()
        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()
		MyDataRow.BeginEdit()
		'******************************

		All the data fields are set here
		Except the Identity column

		'******************************
		MyDataTable.Rows.Add(MyDataRow)
		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

0
etsellinc
Asked:
etsellinc
1 Solution
 
Jacques Bourgeois (James Burger)Commented:
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

0
 
etsellincAuthor Commented:
Didn't think to use the command object. Thought the data row would have the answer.
Thanks,
Mark
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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