I have an employee form, Docked on the left side is a datagridview bound to a datatable which is populated at load, On the right hand side are databound contols(textbox,Combobox,etc....) to the same datatable.
The functionality I'm looking for is This:
1) When the form is loaded, It Displays the data in the datagridview. DGV Columns are First,Last and Display Name
Resolved - I've done this by
Loading a Dataset at load time with all data via adapater and Limiting what columns are displayed in the DGV.
DGV.Columns(0).Visible = false etc....
2) Various Controls Display Detailed Data from the selection in the DGV
Resolved - The controls are databound to the same datatable
tbFirstName.DataBindings.Add("Text", myDataSet.Tables("Employees"), "FirstName", False)
3) Need to be able to Add a new Employee by clicking on new button
In the New Button Click Event i Have
Dim NewRow As DataRow = myDataSet.Tables("Employees").NewRow()
myDataSet.Tables("Employees").Rows.Add(NewRow)
myDataSet.Tables("Employees").Rows.IndexOf(NewRow)
DGV.Rows(myDataSet.Tables("Employees").Rows.IndexOf(NewRow)).Selected = True
but this does not update the databound controls and i would also need to be able to stop multiple new
As Far as Updating or deleting I imagine i can use the adapter to do this
Let me Simplify the question:
How to Data bind with new,edit,delete functions in a windows form that has a datagrid that display only 3 Columns of information and a series of controls that display detailed data based on what is selected in the datagridview
Retrieve your data into a DataTable, bind the DataTable to a BindingSource and the BindingSource to your grid and the other controls. All interaction with the data in code would then be done through the BindingSource. The AddNew method of the BindingSource will create a new row and make it the current record in both the grid and the other controls. Navigating away from that record will commit it to the underlying DataTable or you can call EndEdit on the BindingSource to do it explicitly.
Finding information for this type of common functionality was difficult considering all the little things one had to consider so i am posting this to help anyone out in building this common type of form: Im sure this could be made better by further separating calls to the database via DAL (Data Access Layer) But for functionality here it is.
The Code:
Imports MySql.Data.MySqlClient
Imports MySql.Data
Public Class FormProfiles
Dim DT As New DataTable("Employees")
Dim Binding As New BindingSource
Dim con As New MySqlConnection("Server=localhost;Database=TimeBookDB;Uid=root;Pwd=root")
Dim cmd As New MySqlCommand("SELECT * FROM Employees", con)
Dim builder As New MySqlCommandBuilder()
Dim myDA As New MySqlDataAdapter(cmd)
Private Sub LoadData()
DGV.Rows.Clear()
LoadDepartments()
LoadOvertimes()
Try
con.Open()
myDA.Fill(DT)
'Automatically generates DeleteCommand, UpdateCommand and InsertCommand for DataAdapter object
builder.DataAdapter = myDA
Binding.DataSource = DT
DGV.DataSource = Binding
con.Close()
DGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
DGV.ReadOnly = True
FormatDGV()
BindControls()
DGV.Rows(0).Selected = True
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub FormatDGV()
'Alternating rows
Me.DGV.RowsDefaultCellStyle.BackColor = Color.White
Me.DGV.AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue
DGV.AllowUserToAddRows = False
'Set up the DGV we only want first name, last name and display name to show in the DGV
DGV.RowHeadersVisible = False
DGV.Columns("EmpID").Visible = False
DGV.Columns("PrefixIndex").Visible = False
DGV.Columns("FirstName").Visible = True
DGV.Columns("MiddleName").Visible = False
DGV.Columns("LastName").Visible = True
DGV.Columns("Suffix").Visible = False
DGV.Columns("DisplayName").Visible = True
DGV.Columns("Passcode").Visible = False
DGV.Columns("Status").Visible = False
DGV.Columns("PasscodeHint").Visible = False
DGV.Columns("OvertimeID").Visible = False
DGV.Columns("PayrollSettingsID").Visible = False
DGV.Columns("IsActive").Visible = False
DGV.Columns("IsFullTime").Visible = False
DGV.Columns("DepartmentID").Visible = False
DGV.Columns("OfficeExtension").Visible = False
DGV.Columns("IsSalaried").Visible = False
DGV.Columns("Wage").Visible = False
DGV.Columns("Salary").Visible = False
DGV.Columns("SSN").Visible = False
DGV.Columns("HireDate").Visible = False
DGV.Columns("TerminationDate").Visible = False
DGV.Columns("WorkEmail").Visible = False
DGV.Columns("EmployeeNumber").Visible = False
DGV.Columns("AutoDeductionID").Visible = False
DGV.Columns("Notes").Visible = False
DGV.Columns("ShiftIDsCSV").Visible = False
DGV.Columns("PersonalGroupID").Visible = False
DGV.Columns("Address").Visible = False
DGV.Columns("City").Visible = False
DGV.Columns("Province").Visible = False
DGV.Columns("PostalCode").Visible = False
DGV.Columns("PrimaryPhone").Visible = False
DGV.Columns("SecondaryPhone").Visible = False
DGV.Columns("EmergencyContact").Visible = False
DGV.Columns("EmergencyPhone").Visible = False
DGV.Columns("HomeEmail").Visible = False
DGV.Columns("NotificationRecipient").Visible = False
DGV.Columns("MonitoredActions").Visible = False
DGV.Columns("CDateTime").Visible = False
DGV.Columns("CBY").Visible = False
DGV.Columns("MDateTime").Visible = False
DGV.Columns("MBY").Visible = False
'Full row select and readonly
DGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
DGV.ReadOnly = True
DGV.CellBorderStyle = DataGridViewCellBorderStyle.None
End Sub
Private Sub SaveUpdate()
Binding.EndEdit()
con.Open()
Me.myDA.Update(Binding.DataSource)
con.Close()
End Sub
Private Sub Delete()
If DGV.SelectedRows.Count > 0 Then
If MessageBox.Show("Are you sure you want to delete this employee?", "Delete Verification", MessageBoxButtons.YesNoCancel) = Windows.Forms.DialogResult.Yes Then
Binding.RemoveCurrent()
Binding.EndEdit()
SaveUpdate()
End If
Else
MessageBox.Show("A Valid record must be selected")
End If
End Sub
Private Sub BindControls()
'databind controls
tbFirstName.DataBindings.Add("Text", Binding, "FirstName", False)
tbMiddleName.DataBindings.Add("Text", Binding, "MiddleName", False)
tbLastName.DataBindings.Add("Text", Binding, "LastName", False)
tbEMContact.DataBindings.Add("Text", Binding, "EmergencyContact", False)
tbEMPhone.DataBindings.Add("Text", Binding, "EmergencyPhone", False)
tbAddress.DataBindings.Add("Text", Binding, "Address", False)
tbCity.DataBindings.Add("Text", Binding, "City", False)
tbProvince.DataBindings.Add("Text", Binding, "Province", False)
tbPC.DataBindings.Add("Text", Binding, "PostalCode", False)
tbHomeEmail.DataBindings.Add("Text", Binding, "HomeEmail", False)
tbSSN.DataBindings.Add("Text", Binding, "SSN", False)
tbPrimaryPhone.DataBindings.Add("Text", Binding, "PrimaryPhone", False)
tbSecondaryPhone.DataBindings.Add("Text", Binding, "SecondaryPhone", False)
mtbHireDate.DataBindings.Add("Text", Binding, "HireDate", False)
nudWage.DataBindings.Add("Text", Binding, "Wage", False)
tbEmployeeNumber.DataBindings.Add("Text", Binding, "EmployeeNumber", False)
tbOfficeExtension.DataBindings.Add("Text", Binding, "OfficeExtension", False)
tbDisplayName.DataBindings.Add("Text", Binding, "DisplayName", False)
tbPasscode.DataBindings.Add("Text", Binding, "Passcode", False)
tbPasscodeHint.DataBindings.Add("Text", Binding, "PasscodeHint", False)
rtbNotes.DataBindings.Add("Text", Binding, "Notes", False)
cbIsSalaried.DataSource = System.Enum.GetValues(GetType(ClassBusinessRules.IsSalaried))
cbIsSalaried.DataBindings.Add("SelectedIndex", Binding, "IsSalaried", False)
cbDepartment.DataBindings.Add("SelectedValue", Binding, "DepartmentID", False)
cbOvertime.DataBindings.Add("SelectedValue", Binding, "OvertimeID", False)
cbStatus.DataSource = System.Enum.GetValues(GetType(ClassBusinessRules.EnumUserStatus))
cbStatus.DataBindings.Add("SelectedIndex", Binding, "Status", False)
lvManagedGroups.DataBindings.Add("Text", Binding, "PersonalGroupID", False)
End Sub
Private Sub AddNew()
Binding.AddNew()
'set the tab page to the first tabl
TabControl.SelectedTab = TabPage1
tbFirstName.Focus()
End Sub
Private Sub LoadDepartments()
cbDepartment.DataSource = MainConsole.DAL.ReturnAllDepartments
cbDepartment.DisplayMember = "DepartmentName"
cbDepartment.ValueMember = "RecordID"
End Sub
Private Sub LoadOvertimes()
cbOvertime.DataSource = MainConsole.DAL.ReturnAllOT
cbOvertime.DisplayMember = "OvertimeName"
cbOvertime.ValueMember = "OvertimeID"
End Sub
Private Sub ModifyPasscode()
Dim MPC As New FormModifyPasscode
If MPC.ShowDialog = Windows.Forms.DialogResult.OK Then
'these textboxes are size 0, setting to visible = false doesn't update databindings
tbPasscode.Text = MPC.tbPasscode.Text
tbPasscodeHint.Text = MPC.tbPasscodeHint.Text
SaveUpdate()
End If
End Sub
'/////////////////////////////////////////////
Private Sub FormProfiles_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
LoadData()
End Sub
Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
Me.Close()
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
AddNew()
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
SaveUpdate()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Delete()
End Sub
Private Sub DGV_RowsAdded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsAddedEventArgs) Handles DGV.RowsAdded
'when a new row is added we want to select it automatically
DGV.Rows(e.RowIndex).Selected = True
End Sub
Private Sub btnModifyPasscode_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModifyPasscode.Click
ModifyPasscode()
End Sub
End Class
I hope this is useful, The only thing this doesnt do that might be desired is to prompt the user to save changes when moving from row to row after changes have been made instead of allowing any changes with any record then saving the whole thing. the Payroll Tab has the following fields and etc..
With the above code I am still stuck on adding new. If you open the form then click on add new right away - no problem, but if you open the form click on a different tab page, then click new button, the tabcontrol controls don't clear out, they remain at the last selected value, and the new row doesn't get selected. Also i don't know how to stop navigating away from the selected DGV item before changes are saved or discarded(a prompt that states do you wish to save changes,yes - allows the navigation,no - deletes the new row, cancel - stays on the new row) and to disable the new button until the new record is either saved or discarded.
Anybody out there that knows how to remedy this, please help a fellow out. Thank you.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.
