etsellinc
asked on
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks,
Mark