#1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Location
    Switzerland
    Posts
    1,152
    Rep Power
    1902

    Filter DataGridView Content like Excel AutoFilter


    Hello folks.

    I am writing this application where i need to display a log that is stored in a database. It's no problem to get the data into a dataset and display it in the form. But now i would like to give the user the abillity to filter the displayed rows. I know how to make a DataView once i know the parameters.

    What i'm looking for is an easy and intuitive way for the user to input the filter paramters. Ideally something like the AutoFilter from MS Excel. Is there already something like that and if not (how) could it be done?

    If i left something out feel free to ask.

    Thanks for your help.
    Hugh of Borg
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    1
    Rep Power
    0

    Sae thing I want


    I search for the same thing.

    I have even given up the idea to have combos inside datagridview and rather put them outside.

    If you have found a solution by now, please be so kind and give me some clues.

    Thanks in advance
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    25
    Rep Power
    0
    You can write your own SQL and run them on DataAdaptors in coding.
    These are 'Command' objects which you can store in the adapters Select command(You even can write delete, insert commands too and store them in the adapters respective command property.
    Ex: for a datagrid with a single column
    Add a combo box above the column header and assign a dataset, which gives all the filtering criteria, to it's data member property.
    On the combo boxs' selected index change event, write a code to change the datagrids data adapters select command.

    combobox1_selectedIndexChange
    Dim command1 As New OleDb.OleDbCommand("<The SQL you have to run to change the data on the datagrid>")
    Connection1.Open() 'You connection to the database
    command1.Connection = Connection1
    DataAdapter2.SelectCommand = command1 'assign the command to the data adapts' select command
    DataAdapter2.Fill(DataSet31) ' fill the dataset which gives data to your dataview with the new data

    Now your datagrid displays data with respect to the selection of the combo box.
    I hope this is the answer that you are seeking
    Or, explain your answer further more, because, you can do a lot of work with databases using vb.net

    Cheers!
  6. #4
  7. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Location
    Switzerland
    Posts
    1,152
    Rep Power
    1902
    Thanks for your reply and your idea on this problem.

    Would this also work with adapting a dataset's view instead of re-reading the entire database? It might become a rather large one so i don't want to read it in too often.

    about the dropdown menus. Is it not possible to add them to the gridview itself? That would remove a lot of work when the columns are resizeable.
    - Hugh of Borg

    The first thing young borg are taught: Keep away from Microsoft software!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    25
    Rep Power
    0
    You can use the dataviews' 'RowFilter' property.
    You can change the rowfilter property in run time with a condition
    Ex: DataView1.RowFilter = "<condition>"
    DataView1.RowFilter = "column1='test1'"
    You don't add a drop down menu to the grid, you add one just above the column you want filter.
    I don't know how to manage the resizing action of the column, but if you could find the columns width after the re size, you can re size the combo box accordingly

    There is a way for you to add a control to a datagrids field, but i don't think you can work with databases when you do that.

    Good luck!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    122
    Rep Power
    11
    you can even make this:
    click on a particular value on the grid so that records pertaining to the clicked value only will be displayed in the grid.
    equate the dataview's row filter equated as stated above to the grid's selected cell value

    i can give you the code if you want later
  12. #7
  13. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Location
    Switzerland
    Posts
    1,152
    Rep Power
    1902
    Originally Posted by daffodils
    you can even make this:
    click on a particular value on the grid so that records pertaining to the clicked value only will be displayed in the grid.
    equate the dataview's row filter equated as stated above to the grid's selected cell value

    i can give you the code if you want later
    You mean that if i click on a cell in the column 'sex' containing 'male' the view is filtered to display only men? That's a nice thing but not what i'm looking for... But thanks anyway.

    Originally Posted by eranga
    There is a way for you to add a control to a datagrids field, but i don't think you can work with databases when you do that.
    Can you tell me how. That could help me a step further...
    - Hugh of Borg

    The first thing young borg are taught: Keep away from Microsoft software!
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    25
    Rep Power
    0
    http://www.codeproject.com/vb/net/Add_controls_to_DataGrid.asp
    or
    try searching for string "Add controls to datagrid"
  16. #9
  17. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Location
    Switzerland
    Posts
    1,152
    Rep Power
    1902
    I managed to make a static example that look like i want it to. I extended a DataGridView added the collumns i wanted and then added one more row than i needed for the data. In the first row i replaced the cells with ComboBoxCells with the appropriate selections and set the row to be frozen.

    Now there's still a lot to do. First i'll have to make this somehow dynamic and modify the class that it starts to display data at the second row instead of the first. Then of course i'll have to add the filtering logic.

    Just wanted you to know what i've done so far in case you are working on it too and are stuck somewhere. I'm not sure if i will finish this because i have discovered a tool that already can do this. It's just a bit slow imo and the look is not what i had in mind. *goes thinking*

    EDIT:
    I think I almost got it. The problem I now have is this. In a DataGridView bound to a DataSet how do i make one unbound row? Can anyone give me a hint on this?
    Last edited by Hugh of Borg; June 21st, 2006 at 02:28 AM.
    - Hugh of Borg

    The first thing young borg are taught: Keep away from Microsoft software!
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    25
    Rep Power
    0
    use two datagrids, one is not visible but communicated with the dataset, create the other datagrid in coding and fill the data in coding starting from the 2nd row onwards. this might work.
  20. #11
  21. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Location
    Switzerland
    Posts
    1,152
    Rep Power
    1902
    Thanks for your tipp. I'm getting close...

    In my DataGridView descendent I declared a DataSet which I use for data storage and I have overridden the DataSource property to assign to the dataset instead of something else. Now this control appears databound to the outside but really is in VirtualMode on the inside. It got me a bit of a headache till i figured out how to interact correctly with the datagrid but it now works.

    I'll keep you updated as I add the filter logic to the first row...
    - Hugh of Borg

    The first thing young borg are taught: Keep away from Microsoft software!
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    1
    Rep Power
    0
    Can you send me the code until this point ? Sample would be gr8.

    My E-mail E-Mail address blocked: See forum rules)

    Thanks in advance.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    1
    Rep Power
    0
    here is an msdn article that shows you exactly how to do this, and
    provides a reusable dll for doing so.

    it wont let me post links, but search msdn for 'Building a Drop-Down Filter List for a DataGridView Column Header Cell'

IMN logo majestic logo threadwatch logo seochat tools logo