Solved

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

Posted on 2014-02-24
2
127 Views
Last Modified: 2014-02-24
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
Comment
Question by:etsellinc
2 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 39884106
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
 

Author Closing Comment

by:etsellinc
ID: 39884151
Didn't think to use the command object. Thought the data row would have the answer.
Thanks,
Mark
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question