Question Listview Not Updating Correctly

[OminousVortex]

I have a form in my program that has a listview on it. I am able to add data to the listview without any problems. The issue I have is when I try to add/delete an item from the database. The add/delete works properly on the database side. However, when I call the routine to fill the listview again, it doesn't update until I reselect a date from the calendar. When I click on the date in the calendar, the listview fills again like planned and the data has been added/deleted from the list. I have looked and looked but have not been able to find a resolution for this. Any help would be appreciated.

I created another simple form to try to make it easier to understand. Here is the code:

Public Class Form1
Dim sngSelectedDaily As Single
Dim dteDateSelected As Date


Private Sub List_Load(ByVal dteViewDate As Date)
Dim strGetListData As String
Dim oleDbCmdGetListData As OleDb.OleDbCommand
Dim dtrGetListData As OleDb.OleDbDataReader


'MsgBox(ListView1.Items.Count.ToString)


ListView1.Clear()


''Set view property
ListView1.View = View.Details
ListView1.GridLines = True
ListView1.FullRowSelect = True


strGetListData = "Select tblDaily.ProjectID, tblDaily.TaskID, tblDaily.TaskDesc, tblDaily.EntryDate, tblDaily.TimeSpent, tblDaily.Phase, tblProjects.Project, tblJKKPhases.PhaseName, tblDaily.Abnormal FROM (tblProjects INNER JOIN tblDaily ON tblProjects.ProjectID = tblDaily.ProjectID) LEFT JOIN tblJKKPhases ON tblDaily.Phase = tblJKKPhases.PhaseID Where (tblDaily.EntryDate = " & "#" & dteDateSelected & "#)" & " Order By TaskID"


Try
oleDbCmdGetListData = New OleDb.OleDbCommand(strGetListData, GetAccessConn(1))
dtrGetListData = oleDbCmdGetListData.ExecuteReader()


''Add column header
ListView1.Columns.Add("ProjectID", 0, HorizontalAlignment.Center)
ListView1.Columns.Add("TaskID", 0, HorizontalAlignment.Center)
ListView1.Columns.Add("Description", 250, HorizontalAlignment.Left)
ListView1.Columns.Add("Entry Date", 95, HorizontalAlignment.Center)
ListView1.Columns.Add("Time", 50, HorizontalAlignment.Center)
ListView1.Columns.Add("PhaseNumber", 0, HorizontalAlignment.Center)
ListView1.Columns.Add("Project", 250, HorizontalAlignment.Left)
ListView1.Columns.Add("Phase", 220, HorizontalAlignment.Left)
ListView1.Columns.Add("Type", 50, HorizontalAlignment.Center)


While dtrGetListData.Read


'Add items in the listview
Dim arr(8) As String
Dim itm As ListViewItem


'Add first item
arr(0) = dtrGetListData(0)
arr(1) = dtrGetListData(1)
arr(2) = dtrGetListData(2)
arr(3) = dtrGetListData(3)
arr(4) = dtrGetListData(4)
arr(5) = dtrGetListData(5)
arr(6) = dtrGetListData(6)
arr(7) = GetNullValue(dtrGetListData(7))
If dtrGetListData(8) = False Then
arr(8) = "N"
Else
arr(8) = "A"
End If


arr(8) = dtrGetListData(8)
itm = New ListViewItem(arr)
ListView1.Items.Add(itm)


End While


'dtrGetListData.Close()
dtrGetListData.Dispose()
oleDbCmdGetListData.Dispose()
GetAccessConn(2)


'MsgBox(ListView1.Items.Count.ToString)


Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub


Private Sub ListView1_Click(sender As Object, e As System.EventArgs) Handles ListView1.Click
sngSelectedDaily = ListView1.SelectedItems(0).SubItems(1).Text
End Sub


Private Sub DateTimePicker1_ValueChanged(sender As Object, e As System.EventArgs) Handles DateTimePicker1.ValueChanged
dteDateSelected = FormatDateTime(DateTimePicker1.Value, DateFormat.ShortDate)
Call List_Load(dteDateSelected)
End Sub


Private Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
Call DeleteData()
End Sub
Private Sub DeleteData()
Dim strDeleteDaily As String
Dim oleDbCmdDeleteDaily As OleDb.OleDbCommand
Dim dtrDeleteDaily As OleDb.OleDbDataReader


strDeleteDaily = "DELETE FROM tblDaily Where tblDaily.TaskID = " & sngSelectedDaily
Try
oleDbCmdDeleteDaily = New OleDb.OleDbCommand(strDeleteDaily, GetAccessConnB(1))
dtrDeleteDaily = oleDbCmdDeleteDaily.ExecuteReader()
Catch ex As Exception
MsgBox(ex.Message)
End Try
'MsgBox(cboActivity.SelectedValue.ToString)


dtrDeleteDaily.Dispose()
oleDbCmdDeleteDaily.Dispose()
GetAccessConnB(2)


Call List_Load(dteDateSelected) 'fill list box with entries for selected date
End Sub


End Class

 

[IanRyder]

Hi,

Firstly, when posting code please use the Advanced button to add code tags for readability of the code.

As it is, I disagree with your comments since this routine does not make any sense:-

Private Sub DeleteData()
  Dim strDeleteDaily As String
  Dim oleDbCmdDeleteDaily As OleDb.OleDbCommand
  Dim dtrDeleteDaily As OleDb.OleDbDataReader
 
 
  strDeleteDaily = "DELETE FROM tblDaily Where tblDaily.TaskID = " & sngSelectedDaily
  Try
    oleDbCmdDeleteDaily = New OleDb.OleDbCommand(strDeleteDaily, GetAccessConnB(1))
    dtrDeleteDaily = oleDbCmdDeleteDaily.ExecuteReader()
  Catch ex As Exception
    MsgBox(ex.Message)
  End Try
  'MsgBox(cboActivity.SelectedValue.ToString)
 
 
  dtrDeleteDaily.Dispose()
  oleDbCmdDeleteDaily.Dispose()
  'GetAccessConnB(2)
 
 
  Call List_Load(dteDateSelected) 'fill list box with entries for selected date
End Sub

When you want to delete an item from your database you would create a valid SQL query, which it seems you have, and you would then call the ExecuteNonQuery method of the OleDbCommand object that you have created.

What you are currently doing is to set a OleDbDataReader to oleDbCmdDeleteDaily.ExecuteReader() with an SQL DELETE command. This is totally wrong and should not be deleting anything in your database. So, I would agree that your final call in the routine List_Load(dteDateSelected) would return the same list of data. This means that what I do not understand is this statement:-

When I click on the date in the calendar, the listview fills again like planned and the data has been added/deleted from the list.

After all that is said, is there any reason that you are using a ListView to do this since a DataGridView would seem a more appropriate control for you to use to manipulate your DataTable.

Hope that helps.

Cheers,

Ian

 

[OminousVortex]

Issue with Datagridview

Ian,

Thanks for the info. I removed the listview and started using the datagridview. Unfortunately, I'm still having the same issue. When I delete a record from the database, the datagridview still shows the deleted record after I reload the data.

Code to populate the datagridview:

Private Sub PopulateDataGridView(ByVal dteViewDate As Date)


        Dim strGetListData As String
        Dim oleDbCmdGetListData As OleDb.OleDbCommand
        Dim dtrGetListData As OleDb.OleDbDataReader
        Dim intCountTime As Integer


        Me.dailyDataGridView.Rows.Clear()


        'MsgBox(lvwDaily.Items.Count.ToString)


        strGetListData = "Select tblDaily.ProjectID, tblDaily.TaskID, tblDaily.TaskDesc, tblDaily.EntryDate, tblDaily.TimeSpent, tblDaily.Phase, tblProjects.Project, tblJKKPhases.PhaseName, tblDaily.Abnormal FROM (tblProjects INNER JOIN tblDaily ON tblProjects.ProjectID = tblDaily.ProjectID) LEFT JOIN tblJKKPhases ON tblDaily.Phase = tblJKKPhases.PhaseID Where (tblDaily.EntryDate = " & "#" & dteDateSelected & "#)" & " Order By TaskID"


        Dim row As String()


        Try
            oleDbCmdGetListData = New OleDb.OleDbCommand(strGetListData, GetAccessConn(1))
            dtrGetListData = oleDbCmdGetListData.ExecuteReader()


            intCountTime = 0


            If dtrGetListData.HasRows = False Then
                txtTotalTimeM.BackColor = Color.White
                txtTotalTimeH.BackColor = Color.White
                Exit Sub
            End If


            While dtrGetListData.Read


                row = New String() {dtrGetListData(0), dtrGetListData(1), dtrGetListData(2), dtrGetListData(3), dtrGetListData(4), dtrGetListData(5), dtrGetListData(6), GetNullValue(dtrGetListData(7)), dtrGetListData(8)}
                Me.dailyDataGridView.Rows.Add(row)


                intCountTime = intCountTime + dtrGetListData(4)


            End While


            txtTotalTimeM.Text = intCountTime
            txtTotalTimeH.Text = Math.Round(txtTotalTimeM.Text / 60, 1)


            If txtTotalTimeM.Text < 480 Then
                txtTotalTimeM.BackColor = Color.Red
                txtTotalTimeH.BackColor = Color.Red
            Else
                txtTotalTimeM.BackColor = Color.White
                txtTotalTimeH.BackColor = Color.White
            End If


            dtrGetListData.Dispose()
            oleDbCmdGetListData.Dispose()
            GetAccessConn(2)


            'MsgBox(lvwDaily.Items.Count.ToString)


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try




        With Me.dailyDataGridView
            .Columns(0).DisplayIndex = 6
            .Columns(1).DisplayIndex = 7
            .Columns(2).DisplayIndex = 0
            .Columns(3).DisplayIndex = 1
            .Columns(4).DisplayIndex = 2
            .Columns(5).DisplayIndex = 8
            .Columns(6).DisplayIndex = 3
            .Columns(7).DisplayIndex = 4
            .Columns(8).DisplayIndex = 5


        End With


    End Sub

Code to delete from database:

Private Sub NewDelete()
        Dim cmd As New OleDb.OleDbCommand

        cmd.Connection = GetAccessConn(1)
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "DELETE FROM tblDaily Where tblDaily.TaskID = " & sngSelectedDaily
        cmd.ExecuteNonQuery()

        cmd.Dispose()
        GetAccessConn(2)

        Call PopulateDataGridView(dteDateSelected) 'fill list box with entries for selected date
   End Sub

 

[jmcilhinney]

I tend to get frustrated addressing what is basically the same issue again and again and it makes me wonder where so many people get the same idea to do things backwards. There are some dodgy examples and teachers out there and they are damaging new developers by the bucket-load.

As I said, you're doing things backwards. You don't update the database first and then try to make the grid match the database. You update the grid first. You should be using a data adapter rather than a data reader and populating a DataTable, which you then bind to the grid. The user can then edit the data in the grid, adding, modifying and deleting records as required. You can also edit the data in the DataTable in code. Courtesy of data-binding, any changes in the grid affect the DataTable and vice versa. That means that the grid has to be updated. You then use the same data adapter that retrieved the data in the first place to save the changes from the DataTable to the database. The two are then obviously in sync. You can find an example of the data access part here:

Retrieving and Saving Data in Databases

To perform the binding, you can assign the DataTable to the DataSource of the DataGridView. If you need to work with the data source in code then it may be better to use a BindingSource in between the two.