CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Update Database and datagridview from textboxes

  1. #1
    Join Date
    Mar 2018
    Posts
    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

  2. #2
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,188

    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++17 Compiler: Microsoft VS2019 (16.7.0)

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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)