DataGridView filter rows based on a value

Question

I am working on a windows form in vs2013.I am trying to display records based on a variable on to DataGridView when my form loads. I have tried a whole variety of code based on some google research.

I am connect form data to an access database.

Here is the code:

Dim maxrecords As Integer
Dim firstrecord As Integer
Dim currentrecord As Integer
Dim deletedrecordnum As Integer
Dim con As New OleDb.OleDbConnection
Dim del As New OleDb.OleDbConnection
Dim dbprov As String
Dim dbsource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim dsDelete As New DataSet
Dim daDelete As OleDb.OleDbDataAdapter
Dim sql As String
Dim sqlDelete As String
Dim currentday As String

'This is the first thing that happens before the form loads up on the screen
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    dbprov = "Provider = Microsoft.ACE.OLEDB.12.0;"
    dbsource = "Data Source = C:\VB_Projects\database1.accdb"
    sql = "SELECT * FROM T_Cutdata"
    sqlDelete = "SELECT * FROM T_DeletedData"
    con.ConnectionString = dbprov & dbsource
    con.Open()
    MsgBox("Open")
    da = New OleDb.OleDbDataAdapter(sql, con)
    da.Fill(ds, "lawncutdata")
    daDelete = New OleDb.OleDbDataAdapter(sqlDelete, con)
    daDelete.Fill(dsDelete, "lawncutdata")
    con.Close()
    MsgBox("Closed")

    If IsDBNull(ds.Tables("lawncutdata").Rows(0).Item(3)) Then
        CheckBox1.Checked = False
    Else
        CheckBox1.Checked = ds.Tables("lawncutdata").Rows(0).Item(3)
    End If

    If IsDBNull(ds.Tables("lawncutdata").Rows(0).Item(5)) Then
        TextBox5.Text = ""
    Else
        TextBox5.Text = ds.Tables("lawncutdata").Rows(0).Item(5)
    End If
    maxrecords = ds.Tables("lawncutdata").Rows.Count - 1
    currentrecord = 0
    firstrecord = 0
    currentday = "Monday"
    DataGridView1.DataSource = ds.Tables("lawncutdata")
    For i As Integer = firstrecord To maxrecords
        If DataGridView1.Rows(i).Cells(1).ToString = currentday Then
        DataGridView1.DataSource = ds.Tables("lawncutdata").Rows(3)
    EndIf
Next
    ds.Tables("lawncutdata").DefaultView.RowFilter.Contains("Monday")
End Sub

PS: What is the best way to store daily data? I will be inputting data on a daily basis. Should I create a table for monday, tuesday, etc?

I am having such a hard time with this. Any help is truly appreciated.

Thanks melmatvar

Answer 1

Get rid of your loop. Set the DefaultView.RowFilter of the DataTable and then assign the DataTable to the DataSource of the grid. That's it: filter and bind. To filter, you actually have to assign a String to the RowFilter, e.g.

myDataTable.DefaultView.RowFilter = filterString

That said, if you only want one day's data to display then you should only retrieve one day's data from the database. If you want to be able to change the filter without requerying then get all the data.

No, you should absolutely not create multiple tables. Each table represents a single entity. The entity is not different because it relates to a different day. One table with a column for the day/date/whatever is the correct option.