Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-02-24
2
128 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB 2005 Tooltips on Form Load Event 15 34
.NET 2008 VB and C# 6 39
Calculate number of nights between two dates 5 53
vb.net convert long time to mm:ss 23 12
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

856 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