• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • 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)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

0
 
etsellincAuthor Commented:
Didn't think to use the command object. Thought the data row would have the answer.
Thanks,
Mark
0
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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