Question Update Database and datagridview from textboxes

[Shimshai]

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:

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

 

[jmcilhinney]

Firstly, please don't post unformatted code. It is very hard to read, primarily because HTML ignores the leading indents. I have added appropriate formatting tags to your post and, as you can see, it's far more readable. Please do this yourself in future. You can type the tags manually or use the toolbar buttons on the advanced editor.

Secondly, please don't just post all your code. Only post what is relevant to the problem. If we have to wade through reams of irrelevant code then it just makes it harder for us to find the issue and thus less likely that we'll bother. If we give up because it's too much trouble, that's bad for you. You know what part of your code is relevant to the functionality you're having issues with so you know what part of your code is relevant to our trying to fix it. Rather than make everyone who volunteers their time to help you work that out for themselves, how about you do it once for everyone?

Ignoring the issue for the moment, most of your code is probably redundant anyway. Maybe this is an assignment and you aren't allowed to do this but a real application should use data-binding for this, e.g.

'Retrieve the data.
myDataAdapter.Fill(myDataTable)

'Bind the data to the grid.
myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource

'Bind the data to the individual controls, e.g.
givenNameTextBox.DataBindings.Add("Text", myBindingSource, "GivenName")
familyNameTextBox.DataBindings.Add("Text", myBindingSource, "FamilyName")

If the data is bound like that, the TextBoxes will be populated automatically and any changes made in them will be automatically pushed back to the DataTable and thus pushed to the grid as well. I've never tried that with disabled controls but I would expect that it should still work. Saving your changes is then two simple lines of code:

'Commit any in-progress edit.
myBindingSource.EndEdit()

'Save changes.
myDataAdapter.Update(myDataTable)

If you can't or don't want to go that way then I would suggest that you first debug your existing code, which I suspect that you haven't done. Set a breakpoint at the top of the section of code that is not doing as you expect and then step through it, testing the state at each step. As soon as the state doesn't match your expectations, you've found an issue. Even if you can't fix it yourself, at least you can post all the relevant information for us, including ONLY the relevant code.