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:
which makes the new id-number available as a parameter.
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
0 Comments On This Entry
Trackbacks for this entry [ Trackback URL ]
← January 2021 →
S | M | T | W | T | F | S |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
31 |
Tags
My Blog Links
Recent Entries
Recent Comments
Search My Blog
2 user(s) viewing
2 Guests
0 member(s)
0 anonymous member(s)
0 member(s)
0 anonymous member(s)