This isn't a tutorial, just a lump of code that I wanted to make available that demonstrates retrieving INCREMENT values for newly inserted SQL Server (Express) records, and displaying them on a WinForm. (There are some links in the code to articles on this subject.)

Of course, you should question whether you need to retrieve, or even display, these numbers. Even if so, the alternative is to wait a while longer and Update the data source in one go, and re-fill the DataSet at this point. You don't want to re-fill a DataSet too frequently, but it is the best way to ensure that everything is up-to date and accurate.

As I say, this isn't a tutorial, so you'll need some experience with VB.NET to make use of it. Refer to the MSDN documentation to fill-in any gaps.





The StaffID number for a new record is retrieved when clicking into the Holidays DGV, or after pressing Save.

The key ingredient is this code:

    cmd.CommandText &= " SET @ID = SCOPE_IDENTITY()"

which makes the new id-number available as a parameter.

Imports System.Data.SqlClient

Public Class frmStaff
    Private _sConn As String = "Server=.\SQLExpress;Database=StaffDB;Trusted_Connection=Yes"
    Private _conn As New SqlConnection(_sConn)
    Private _dsStaff As New DataSet()

    Private _bsStaff As New BindingSource()
    Private _bsDepartment As New BindingSource()
    Private _bsHoliday As New BindingSource()

    Private _daStaff As SqlDataAdapter
    Private _daDepartment As SqlDataAdapter
    Private _daHoliday As SqlDataAdapter

    Private Sub frmStaff_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim sSqlStaff As String = "SELECT * FROM Staff"
        Dim cmdStaff As New SqlCommand(sSqlStaff, _conn)
        _daStaff = New SqlDataAdapter(cmdStaff)

        _daStaff.Fill(_dsStaff, "Staff")
        _dsStaff.Tables("Staff").Columns("StaffID").AutoIncrement = True
        _dsStaff.Tables("Staff").Columns("StaffID").AutoIncrementSeed = -1
        _dsStaff.Tables("Staff").Columns("StaffID").AutoIncrementStep = -1

        'http://www.codeproject.com/Tips/288850/Using-SCOPE-IDENTITY-with-CommandBuilder-and-DataA
        Dim cbStaff As New SqlCommandBuilder(_daStaff)
        _daStaff.DeleteCommand = cbStaff.GetDeleteCommand.Clone()
        _daStaff.UpdateCommand = cbStaff.GetUpdateCommand.Clone()
        Dim cmd = cbStaff.GetInsertCommand.Clone()
        cmd.CommandText &= " SET @ID = SCOPE_IDENTITY()"
        Dim param As New SqlParameter()
        param.Direction = ParameterDirection.Output
        param.Size = 4
        param.SqlDbType = SqlDbType.Int
        param.ParameterName = "@ID"
        param.DbType = DbType.Int32
        cmd.Parameters.Add(param)
        _daStaff.InsertCommand = cmd
        cbStaff.Dispose()

        AddHandler _daStaff.RowUpdated, AddressOf GetStaffID

        'http://blogs.msdn.com/b/vsdata/archive/2009/09/14/refresh-the-primary-key-identity-column-during-insert-operation.aspx

        'Managing an @@IDENTITY Crisis
        'https://msdn.microsoft.com/en-us/library/ms971502.aspx

        Dim sSqlDepartment As String = "SELECT * FROM Department"
        Dim cmdDepartment As New SqlCommand(sSqlDepartment, _conn)
        _daDepartment = New SqlDataAdapter(cmdDepartment)
        Dim cbDepartment As New SqlCommandBuilder(_daDepartment)
        _daDepartment.Fill(_dsStaff, "Departments")

        Dim sSqlHoliday As String = "SELECT * FROM Holiday"
        Dim cmdHoliday As New SqlCommand(sSqlHoliday, _conn)
        _daHoliday = New SqlDataAdapter(cmdHoliday)

        _daHoliday.Fill(_dsStaff, "Holidays")
        _dsStaff.Tables("Holidays").Columns("HolidayID").AutoIncrement = True
        _dsStaff.Tables("Holidays").Columns("HolidayID").AutoIncrementSeed = -1
        _dsStaff.Tables("Holidays").Columns("HolidayID").AutoIncrementStep = -1
        Dim cbHoliday As New SqlCommandBuilder(_daHoliday)

        _bsStaff.DataSource = _dsStaff
        _bsStaff.DataMember = "Staff"

        _bsDepartment.DataSource = _dsStaff
        _bsDepartment.DataMember = "Departments"

        _bsHoliday.DataSource = _dsStaff
        _bsHoliday.DataMember = "Holidays"

        Dim pColumn() As DataColumn = {_dsStaff.Tables("Staff").Columns("StaffID")}
        _dsStaff.Tables("Staff").PrimaryKey = pColumn

        'establish a relation between Staff and Holidays
        Dim relation As New DataRelation("StaffHolidays", _
                _dsStaff.Tables("Staff").Columns("StaffID"), _
                _dsStaff.Tables("Holidays").Columns("StaffID"))
        _dsStaff.Relations.Add(relation)

        Me.txtStaffID.DataBindings.Add("Text", _bsStaff, "StaffID")
        Me.txtFirstName.DataBindings.Add("Text", _bsStaff, "FirstName")
        Me.txtLastName.DataBindings.Add("Text", _bsStaff, "LastName")
        Me.txtGrade.DataBindings.Add("Text", _bsStaff, "Grade")
        'Me.txtStartDate.DataBindings.Add("Text", _bsStaff, "StartDate")
        'Me.txtEndDate.DataBindings.Add("Text", _bsStaff, "EndDate")
        'Me.ckbBonus.DataBindings.Add("Checked", _bsStaff, "Bonus")

        cboDept.DataSource = _bsDepartment
        cboDept.DisplayMember = "Department"
        cboDept.ValueMember = "DeptID"
        cboDept.DataBindings.Add("SelectedValue", _bsStaff, "DeptID")

        _bsHoliday.DataSource = _bsStaff
        _bsHoliday.DataMember = "StaffHolidays"     'the relation

        dgvHolidays.AutoResizeColumns()
        dgvHolidays.DataSource = _bsHoliday
    End Sub

    Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
        If _bsStaff.Count > 0 Then
            _bsStaff.MoveFirst()
        End If
    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If _bsStaff.Position > 0 Then
            _bsStaff.MovePrevious()
        End If
    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If _bsStaff.Position + 1 < _bsStaff.Count Then
            _bsStaff.MoveNext()
        End If
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
        If _bsStaff.Count > 0 Then
            _bsStaff.MoveLast()
        End If
    End Sub

    Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
        _bsStaff.AddNew()
    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        SaveAll()
    End Sub

    Private Sub SaveAll()
        _bsStaff.EndEdit()
        _bsHoliday.EndEdit()
        If _dsStaff.HasChanges Then
            _daStaff.Update(_dsStaff.Tables("Staff"))
            _daHoliday.Update(_dsStaff.Tables("Holidays"))
        End If
    End Sub
    
    Private Sub dgvHolidays_Enter(sender As Object, e As EventArgs) Handles dgvHolidays.Enter
        'commit the parent row to the DataTable before adding child rows
        _bsStaff.EndEdit()
        If _dsStaff.HasChanges Then
            'we need to ensure we get the new StaffID (if there is one)
            _daStaff.Update(_dsStaff.Tables("Staff"))
        End If
    End Sub

    Private Sub GetStaffID(sender As Object, e As SqlRowUpdatedEventArgs)
        If e.StatementType = StatementType.Insert Then
            Dim inc = e.Command.Parameters("@ID").Value
            Dim pk = e.Row.Table.PrimaryKey
            If pk IsNot Nothing AndAlso pk.Count = 1 Then
                e.Row(pk(0)) = CInt(inc)
                e.Row.AcceptChanges()
            End If
        End If
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        'make sure everything is up-to-date first
        SaveAll()

        If dgvHolidays.SelectedRows.Count > 0 Then
            If MessageBox.Show("Delete holiday?", "Confirm", MessageBoxButtons.YesNo, _
                               MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                _bsHoliday.RemoveCurrent()
            End If
        Else
            If _bsStaff.GetRelatedCurrencyManager("StaffHolidays").Count > 0 Then
                MessageBox.Show("Cannot delete staff member, there are holiday details.", _
                                "Cannot Delete", MessageBoxButtons.OK)
                Exit Sub
            End If
            If MessageBox.Show("Delete staff member record?", "Confirm", MessageBoxButtons.YesNo, _
                               MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                _bsStaff.RemoveCurrent()
            Else
                MessageBox.Show("To delete a holiday record select its row.", "Info", _
                                MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End If
    End Sub

    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub frmStaff_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        _bsStaff.EndEdit()
        _bsHoliday.EndEdit()
        If _dsStaff.HasChanges Then
            If MessageBox.Show("Abandon changes?", "Abandon", MessageBoxButtons.YesNo, _
                               MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
                e.Cancel = True
            End If
        End If
    End Sub
End Class