none
DataView RowFilter

    Question

  • I have a datagridview that is filled by a MS Access database with a tableadapter. I’m using the rowfilter to select customer records form the dataview. The dataview is populated with “DefaultView.ToTable(True, “ to return only distinct records. The problem I have is the data is from a double entry journal and for each record there is a positive and negative amount in the amount column. How can I make not show the records with negative amounts if the all of the fields are the same.

    Wednesday, August 15, 2012 2:46 AM

Answers

  • Oh, sorry ... since you said "for each record there is a positive and negative amount in the amount column", I assumed that there weren't any negative records that didn't have a positive record. Sound like you have a few negatives that don't have a corresponding positive.

    In that case, I think you're going to have iterate through the table created by the DefaultView.ToTable(true), looking for negative values. Are there any other columns in the table that would give an indication of paired records? Some kind of transation id or something like that? If so, then those negative records that don't have a corresponding postive records (based on some id) are the ones you have to keep (the credit memos) and remove the other negative records.

    Keep in mind that when iterating through the Rows of a DataTable in order to remove a row, you must do the iteration backwards (a for loop that starts at Count - 1, and decrements down to 0).


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, August 19, 2012 4:47 PM
    Moderator

All replies

  • How about as part of your filter, you filter on amount > 0?

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, August 15, 2012 4:51 AM
    Moderator
  • Some records are < 0 such as credit memos. I need it to only filter the amount > 0 if all the other fields are equal.
    Thursday, August 16, 2012 2:39 AM
  • Oh, sorry ... since you said "for each record there is a positive and negative amount in the amount column", I assumed that there weren't any negative records that didn't have a positive record. Sound like you have a few negatives that don't have a corresponding positive.

    In that case, I think you're going to have iterate through the table created by the DefaultView.ToTable(true), looking for negative values. Are there any other columns in the table that would give an indication of paired records? Some kind of transation id or something like that? If so, then those negative records that don't have a corresponding postive records (based on some id) are the ones you have to keep (the credit memos) and remove the other negative records.

    Keep in mind that when iterating through the Rows of a DataTable in order to remove a row, you must do the iteration backwards (a for loop that starts at Count - 1, and decrements down to 0).


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, August 19, 2012 4:47 PM
    Moderator
  • Hi

    You can Use RowFilter for that

    DataView dv = new DataView(dt);

    dv.RowFilter = "Col > 0";

    Please mark as answer if found useful

    Monday, August 20, 2012 11:40 AM
  • Rajeev, if you would read the entire thread, you would see that I have already suggested that and @rstambach has already replied that he can't use that. Please read everything before you post!

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, August 20, 2012 3:31 PM
    Moderator