-
March 22nd, 2018, 02:57 PM
#1
Update Database and datagridview from textboxes
I have an application with a datagridview on the bottom half of the page and textboxes, combo-boxes, buttons, etc... on the top half.
When the user changes the highlighted row in the grid then it displays all of the information for that row in the objects on the top half.
If user wants to change the data in a row then he clicks an edit button which enables the textboxes etc... and allows the user to edit the data.
(All of the above works fine).
When he wants to save the changes then he clicks the save button.
This should update the datasource to the grid and show the changes in the grid.
However, I have been unable to get it do this.
Any Help appreciated.
Code below:
Code:
Imports System.Data.SqlClient
Public Class frmEmployeeInformation
Public SQL As New SQLControl()
Dim strEditType As String
Private Sub frmEmployeeInformation_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadGrid()
End Sub
Public Sub LoadGrid(Optional query As String = "")
If query = "" Then
SQL.ExecuteQuery("Select * from EmployeeInformation;")
Else
SQL.ExecuteQuery(query)
End If
If SQL.HasException(True) Then Exit Sub
dgvEmployeeInformation.DataSource = SQL.DBDS.Tables(0)
dgvEmployeeInformation.Rows(0).Selected = True
SQL.DBDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.DBDA).GetUpdateCommand
End Sub
Private Sub DisplayValues()
If dgvEmployeeInformation.RowCount > 2 Then
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("FirstName").Value <> Nothing Then
txtFirstName.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("FirstName").Value.ToString 'EmployeeInformation.FirstName
End If
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("LastName").Value <> Nothing Then
txtLastName.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("LastName").Value.ToString 'EmployeeInformation.LastName
End If
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("SSN").Value IsNot Nothing Then
txtSSN.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("SSN").Value.ToString 'EmployeeInformation.SSN
End If
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("EmployeeInformationID").Value <> Nothing Then
txtEmployeeID.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("EmployeeInformationID").Value.ToString 'EmployeeInformation.EmployeeInformationID
End If
'If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("ADPID").Value <> Nothing Then
'txtADPID.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("ADPID").Value.ToString 'EmployeeInformation.ADPID
'End If
'If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("VP").Value <> Nothing Then
'cboVP.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("VP").Value.ToString 'EmployeeInformation.VP
'End If
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("Default_LocationID").Value <> Nothing Then
cboDefaultLocation.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("Default_LocationID").Value.ToString 'EmployeeInformation.DefaultLocation
End If
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("SystemTableID").Value <> Nothing Then
txtTableID.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("SystemTableID").Value.ToString 'EmployeeInformation.TableID
End If
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("EmployeeActive").Value <> Nothing Then
chkbxActive.Checked = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("EmployeeActive").Value.ToString 'EmployeeInformation.EmployeeActive
End If
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("PrimaryFile").Value <> Nothing Then
chkbxPrimaryFile.Checked = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("PrimaryFile").Value.ToString 'EmployeeInformation.PrimaryFile
End If
If dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("UnallocatedTime").Value <> Nothing Then
chkbxUnallocatedTime.Checked = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("UnallocatedTime").Value.ToString 'EmployeeInformation.UnallocatedTime
End If
End If
End Sub
Private Sub ChangeButtons()
btnClose.Enabled = Not btnClose.Enabled
btnSave.Enabled = Not btnSave.Enabled
btnClear.Enabled = Not btnClear.Enabled
btnFind.Enabled = Not btnFind.Enabled
btnCancel.Enabled = Not btnCancel.Enabled
btnEdit.Enabled = Not btnEdit.Enabled
btnAdd.Enabled = Not btnAdd.Enabled
btnCopy.Enabled = Not btnCopy.Enabled
End Sub
Private Sub ChangeFields()
chkbxActive.Enabled = Not chkbxActive.Enabled
chkbxPrimaryFile.Enabled = Not chkbxPrimaryFile.Enabled
chkbxUnallocatedTime.Enabled = Not chkbxUnallocatedTime.Enabled
txtTableID.Enabled = Not txtTableID.Enabled
txtADPID.Enabled = Not txtADPID.Enabled
cboVP.Enabled = Not cboVP.Enabled
cboDefaultLocation.Enabled = Not cboDefaultLocation.Enabled
End Sub
Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
Me.Close()
End Sub
Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles btnFind.Click
If txtEmployeeID.Text <> "" Then
SQL.AddParam("@EmployeeInformationID", txtEmployeeID.Text)
LoadGrid("select * from EmployeeInformation where EmployeeInformationID = @EmployeeInformationID;")
ElseIf txtSSN.Text <> "" Then
SQL.AddParam("@SSN", txtSSN.Text)
LoadGrid("select * from EmployeeInformation where SSN = @SSN;")
ElseIf txtFirstName.Text <> "" And txtLastName.Text <> "" Then
SQL.AddParam("@FirstName", txtFirstName.Text)
SQL.AddParam("@LastName", txtLastName.Text)
LoadGrid("select * from EmployeeInformation where FirstName = @FirstName and LastName = @LastName;")
Else
LoadGrid("Select * from EmployeeInformation;")
End If
End Sub
Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
txtFirstName.Text = ""
txtLastName.Text = ""
txtSSN.Text = ""
txtEmployeeID.Text = ""
txtADPID.Text = ""
cboVP.Text = ""
cboDefaultLocation.Text = ""
txtTableID.Text = ""
chkbxActive.Checked = "False"
chkbxPrimaryFile.Checked = "False"
chkbxUnallocatedTime.Checked = "False"
End Sub
Private Sub dgvEmployeeInformation_DoubleClick(sender As Object, e As EventArgs) Handles dgvEmployeeInformation.DoubleClick
DisplayValues()
ChangeFields()
ChangeButtons()
End Sub
Private Sub dgvEmployeeInformation_SelectionChanged(sender As Object, e As EventArgs) Handles dgvEmployeeInformation.SelectionChanged
DisplayValues()
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
If strEditType = "Edit" Then
' The code below updates the grid but the changes are not saved to the database. Probably not the way to go
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("FirstName").Value = txtFirstName.Text 'EmployeeInformation.FirstName
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("LastName").Value = txtLastName.Text 'EmployeeInformation.LastName
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("SSN").Value = txtSSN.Text 'EmployeeInformation.SSN
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("EmployeeInformationID").Value = txtEmployeeID.Text 'EmployeeInformation.EmployeeInformationID
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("Default_LocationID").Value = cboDefaultLocation.Text 'EmployeeInformation.DefaultLocation
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("SystemTableID").Value = txtTableID.Text 'EmployeeInformation.TableID
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("EmployeeActive").Value = chkbxActive.Checked 'EmployeeInformation.EmployeeActive
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("PrimaryFile").Value = chkbxPrimaryFile.Checked 'EmployeeInformation.PrimaryFile
dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("UnallocatedTime").Value = chkbxUnallocatedTime.Checked 'EmployeeInformation.UnallocatedTime
' The code above updates the grid but the changes are not saved to the database.
dgvEmployeeInformation.EndEdit()
SQL.DBDS.Tables(0).AcceptChanges()
SQL.DBDA.Update(SQL.DBDS)
'txtADPID.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("ADPID").Value.ToString 'EmployeeInformation.ADPID
'cboVP.Text = dgvEmployeeInformation.Rows(dgvEmployeeInformation.CurrentRow.Index).Cells("VP").Value.ToString 'EmployeeInformation.VP
End If
ChangeFields()
ChangeButtons()
strEditType = ""
'LoadGrid()
End Sub
Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
ChangeFields()
ChangeButtons()
strEditType = ""
End Sub
Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
dgvEmployeeInformation_DoubleClick(Nothing, EventArgs.Empty)
strEditType = "Edit"
End Sub
Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
btnClear_Click(Nothing, EventArgs.Empty)
txtFirstName.Focus()
strEditType = "Add"
ChangeFields()
ChangeButtons()
End Sub
Private Sub btnCopy_Click(sender As Object, e As EventArgs) Handles btnCopy.Click
cboDefaultLocation.Focus()
strEditType = "Copy"
ChangeFields()
ChangeButtons()
End Sub
End Class
Last edited by 2kaud; March 23rd, 2018 at 04:40 AM.
Reason: Added code tags
-
March 23rd, 2018, 04:42 AM
#2
Re: Update Database and datagridview from textboxes
[When posting code, please use code tags. Go Advanced, select the formatted code and click '#'].
Cheers!
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|