dataset not being saved

[divjoy]

Hi, Im using vb express 2010.

I have a datagridview based on a dataset which works well but if I make any changes to the data in the grid and then click on my save button not all the data is saved back to the table.

Here is my Save Data code below, I would appreciate if someone could advise am I missing something out here?

I am also concerned that users don't know when they enter data like dates etc into the grid they must press return key or tab key to confimr entry of the data or it will not being saved...

Public Class frmHHWorkedHours
    '...Add / Edit Clients Assessments...
    Dim cmd As New SqlCommand
    Dim da, da1 As New SqlDataAdapter
    Dim ds As New DataSet
    Dim bs, bs1 As New BindingSource

'on Form Load
'..Check dataset empty before filling...
           Try
            If Not IsNothing(ds.Tables("HHWorkedHours")) Then
                ds.Clear()
            End If
            cmd = New SqlCommand("SELECT * FROM TblStaff", conn)
            cmd.Parameters.AddWithValue("@StaffID", StaffID)
            da1 = New SqlDataAdapter(cmd)
            da1.Fill(ds, "TblHHWorkedHours")
               'Populate BindingSource Datasource
                bs1.DataSource = ds.Tables("TblHHWorkedHours").DefaultView
                'bind BindingSource to Bingding Navigator
                Me.BindingNavigator1.BindingSource = bs1
                'bind DataGridView1 to BindingNavigator's BindingSource   
                Me.DataGridView1.DataSource = Me.BindingNavigator1.BindingSource
             
                For Each column As DataGridViewColumn In Me.DataGridView1.Columns
                    column.DisplayIndex = DataGridView1.Columns.IndexOf(column)
                Next
                Me.DataGridView1.AutoResizeColumns()
             
             
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
        End Try
                Me.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells


'...Code to Save Changes...
Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
         Try
                     
            Dim objCommandBuilder As New SqlCommandBuilder(Me.da1)
            Me.Validate()
            Me.bs1.EndEdit()
            Me.da1.Update(Me.ds, "TblStaff")
            MessageBox.Show("Changes saved!")


       Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
        End Try
    End Sub

 

[jmcilhinney]

I am also concerned that users don't know when they enter data like dates etc into the grid they must press return key or tab key to confimr entry of the data or it will not being saved...

There is no need for concern because they don't have to. When they do leave the cell they have just edited it invokes the EndEdit method of the underlying BindingSource and that's what commits the edit. You are calling EndEdit on the BindingSource explicitly before saving, as you should, so you're guaranteed that any pending edit is commited before saving.

As for the issue, most likely there is no issue. When you call Update on the data adapter there are only three possible outcomes:

1. The save fails and an exception is thrown.
2. The save succeeds and Update returns zero.
3. The save succeeds and Update returns a non-zero value.

You didn't mention anything about an exception being thrown so I would assume that there wasn't one, meaning that the save succeeded. You should, therefore, be testing the value returned by Update to see whether it's zero or not. If it is then that means that there were no changes in the DataTable to save. If it's not zero then that means that there were changes to save and they were indeed saved. If it's the latter then you are simply looking for the data in the wrong place or at the wrong time. In that case, follow the first link in my signature to learn how local data files are managed.