none
DataView.Count shows double of count filtered records RRS feed

  • Question

  • Hi All,
    I have encountered a strange problem using DataView. It shows double the count of filtered records when applied a filter. Strange thing is that it's happening only once i.e when first applied, after that it shows the actual result.
     My Database is a Access Database (actually two) being used for Check and Merge. I have to check records of one table into another and if not found, add. I used the following code

    string filterstring = "vilcd='"+mvilcd+"' AND schcd='"+mschcd+"' AND tolaname ='"+mtolaname+"'";
    DataView Details1View = new DataView(dtDetails1); //It shows more records than present in Datatable
    Details1View.RowFilter = filterstring;  //it again shows more filtered records than presentin datatable
    Details1View.RowStateFilter = DataViewRowState.CurrentRows;
     
    on both the tables/Databases I am using. Is it a Access vulnerability or there is a problem in my code. I would be really thankfull. Please help.

    Omprakash
    • Moved by Paul Zhou Thursday, July 28, 2011 5:37 AM (From:.NET Base Class Library)
    Tuesday, July 26, 2011 12:29 PM

Answers

  • Sorry for the slow reply ... I've been on vacation for a few days.

    Without digging too much further, the problem is probably related to the following: you're using Details1View and Details2View in the first "if" without any declaration of the variables, so I assumed they were members of the class (defined at the class-level), but then in the "else" you're defining them there. These will be two different sets of variables! I don't think that's what your intention was (and if this *is* intentional, then it's pretty lousy code and you should *NOT* do this!!). I *always* advocate using "this" for class-level members to avoid this potentially confusion.

    Why don't you try taking care of that little "gotcha" and see if it helps. Let us know ....


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Omprakash Ojha Saturday, September 3, 2011 8:42 AM
    Friday, August 12, 2011 4:00 PM
  • Omprakash,

    A DataView is nothing more than a filtered "look" at a DataTable. If you do anything to a row in the DataView, you are also affecting the same row in its associated DataTable ... the two cannot be separated because they are one and the same.

    As far as the for{} loops go for finding matches, I think you'd be better off using a DataTable.Select or perhaps use LINQ. I can give you quick examples:

    Example using DataTable.Select():

    for (int n = 0; n < dtMaster2.Rows.Count; n++)
    {
      //M_Row_found = false;
      mschcd = dtMaster2.Rows[n]["schcd"].ToString();
      mvilcd = dtMaster2.Rows[n]["vilcd"].ToString();
      mtolaname = dtMaster2.Rows[n]["tolaname"].ToString();
    
      string M1Select= string.Format("schcd = {0} AND vilcd = {1} AND tolaname = {2}", mschcd, mvilcd, mtotaname);
    
      DataRow[] M1Rows = dtMaster1.Select(M1Select);
    
      //if (M_Row_found == true)
      if (M1Rows.Length > 0)
      {
        // etc.etc.etc. 
      }
    }
    
    
    

    Example using LINQ:

    for (int n = 0; n < dtMaster2.Rows.Count; n++)
    {
      //M_Row_found = false;
      mschcd = dtMaster2.Rows[n]["schcd"].ToString();
      mvilcd = dtMaster2.Rows[n]["vilcd"].ToString();
      mtolaname = dtMaster2.Rows[n]["tolaname"].ToString();
    
      DataRow[] M1Rows = dtMaster1.AsEnumberable()
        .Where(row => row.Field<string>("schcd") == mschcd &&
              row.Field<string>(vilcd") == mvilcd &&
              row.Field<string>("totaname") == mtotaname)
        .ToArray();
    
      //if (M_Row_found == true)
      if (M1Rows.Length > 0)
      {
        // etc.etc.etc. 
      }
    }
    
    

    These are only examples to show you the two different syntaxes.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Omprakash Ojha Saturday, September 3, 2011 8:43 AM
    Wednesday, August 17, 2011 3:48 PM
  • Hi Omprakash,

    No, the DataTable.Select() does not require primary keys. You're thinking of the DataTable.Find(). You can put as many search criteria as you like in the .Select() method. Apparently you did not try the example I posted. =0(

    As far as passing anything to other forms, I've got a blog post about that:

    http://geek-goddess-bonnie.blogspot.com/2011/01/passing-data-between-forms.html


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Omprakash Ojha Saturday, September 3, 2011 8:43 AM
    Saturday, August 20, 2011 4:25 PM

All replies

  • You'd better ask the question in System.Data forum:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/threads


    Hard hard work, Day day up!
    Thursday, July 28, 2011 5:26 AM
  • I'm moving this thread to dataset forum to get better support.

    Have a nice day.


    Paul Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, July 28, 2011 5:36 AM
  • Hi,

    After I checked your post, I suggest you can post a simple demo or code snippet to us to analyze your question easier.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 29, 2011 9:48 AM
  • How are you determining the number of rows in your DataTable and in your DataView? Before you even apply any filter, you say the count is different, but that's really not possible ... so, I'm assuming you're looking at incorrect row counts?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, August 1, 2011 1:54 AM
  • Sorry for late reply.

    I have many tables in the database, 2 of which are master/details related on primery key, so by opening the master table I know that how many details records are there for a single master record. Yes it is not possible to get wrong result as when I did it by using Datatables instead of DataView it shows corrected value. Two things I have noticed -First it is happening only for first  master records after that it shows correct results. Second, It shows just the double of No. of records for first master record. The following is the code:-

    private void Check_Click(object sender, EventArgs e)

     

            {

                Results.Items.Clear(); //ListView 

                string mschcd, mvilcd, mtolaname, mschname;

                string mpageno = "";

                string mslno = "";

                string mchildname = "", mfathername = "";

                int m = 0;

                Boolean M_Row_found = false;            

                string school_matched = "";

                mschname = "";

                int Matched_DetailsRow = 0;

                int Unmatched_DetailsRow = 0;

                int MaxDetails1Pageno = 0;

                int MaxDetails2Pageno = 0;

                Boolean matched = false;

                string matched_details = "";

                for (int n = 0; n < dtMaster2.Rows.Count; n++)

                {

                    M_Row_found = false;

                    mschcd = dtMaster2.Rows[n]["schcd"].ToString();

                    mvilcd = dtMaster2.Rows[n]["vilcd"].ToString();

                    mtolaname = dtMaster2.Rows[n]["tolaname"].ToString();

                    for (int i = 0; i < dtMaster1.Rows.Count; i++)

                    {

                        if (dtMaster1.Rows[i]["schcd"].ToString() == mschcd && dtMaster1.Rows[i]["vilcd"].ToString() ==

                            mvilcd && dtMaster1.Rows[i]["tolaname"].ToString() == mtolaname)

                        {

                            M_Row_found = true;

                            break;

                        }

                    }

                    if (M_Row_found == true)

                    {

                        string cmdstr = "schcd='" + mschcd + "' AND vilcd = '" + mvilcd + "' AND tolaname='" +  mtolaname + "'";

                        Details1View.RowFilter = cmdstr;

                        Details1View.RowStateFilter = DataViewRowState.CurrentRows;

     

                        Details2View.RowFilter = cmdstr;

                        Details2View.RowStateFilter = DataViewRowState.CurrentRows;

                        for (int x = 0; x < Details2View.Count; x++)

                        {

                            mpageno = Details2View[x]["pageno"].ToString();

                            mslno = Details2View[x]["slno"].ToString();

                            mchildname = Details2View[x]["childname"].ToString();

                            mfathername = Details2View[x]["Fathername"].ToString();

                            for (int y = 0; y < Details1View.Count; y++)

                            {

                                if (Details1View[y]["schcd"].ToString() == mschcd && 

     Details1View[y]["vilcd"].ToString()

                                    == mvilcd && Details1View[y]["tolaname"].ToString() == mtolaname &&

                                    Details1View[y]["Pageno"].ToString() == mpageno &&

                                    Details1View[y]["slno"].ToString() == mslno &&

                                    Details1View[y]["Childname"].ToString() == mchildname &&

                                    Details1View[y]["Fathername"].ToString() == mfathername)

                                {

                                    matched = true;

                                    break;

                                }

                            }

                            if (matched == true)

                            {

                                Matched_DetailsRow++;

                            }

                            else

                            {

                                Unmatched_DetailsRow++;

                            }

                        }

                    }

                    else

                    {

                        string filterstring = "vilcd='" + mvilcd + "' AND schcd='" + mschcd + "' AND tolaname ='" +  mtolaname + "'";

     

                        DataView Details1View = new DataView(dtDetails1);

                        Details1View.RowFilter = filterstring;

                        Details1View.RowStateFilter = DataViewRowState.CurrentRows;

     

                        DataView Details2View = new DataView(dtDetails2);

                        Details2View.RowFilter = filterstring;

                        Details2View.RowStateFilter = DataViewRowState.CurrentRows;

                        Details2View.Sort = "SCHCD ASC, VILCD ASC, TOLANAME ASC";

                        for (int x = 0; x <Details2View.Count; x++)

                        {

                            if (Details2View[x]["schcd"].ToString() == mschcd && Details2View[x]["vilcd"].

                                ToString() == mvilcd && Details2View[x]["tolaname"].ToString() == mtolaname)

                            {

                                m++;

                            }

                        }

                    }

                    school_matched = (M_Row_found == true) ? "Found" : "Not Found";

                    matched_details = (matched == true) ? "Yes" : "No";

                    ListViewItem checkeditem = new ListViewItem(mschcd.ToString());

                    checkeditem.SubItems.Add(mschname);

                    checkeditem.SubItems.Add(mtolaname);

                    checkeditem.SubItems.Add(school_matched);

                    checkeditem.SubItems.Add(m.ToString());

                    checkeditem.SubItems.Add(matched_details + "  -  (" + Matched_DetailsRow.ToString() + "/" +

                       Unmatched_DetailsRow.ToString() + ")");

                    checkeditem.SubItems.Add(MaxDetails1Pageno.ToString());

                    checkeditem.SubItems.Add(MaxDetails2Pageno.ToString());

                    Results.Items.Add(checkeditem);

                    m = 0;

                    matched = false;

                    Matched_DetailsRow = 0;

                    Unmatched_DetailsRow = 0;

                    MaxDetails1Pageno = 0;

                    MaxDetails2Pageno = 0;

                }

            }

     

     


    Omprakash
    Wednesday, August 10, 2011 11:53 AM
  • Sorry for the slow reply ... I've been on vacation for a few days.

    Without digging too much further, the problem is probably related to the following: you're using Details1View and Details2View in the first "if" without any declaration of the variables, so I assumed they were members of the class (defined at the class-level), but then in the "else" you're defining them there. These will be two different sets of variables! I don't think that's what your intention was (and if this *is* intentional, then it's pretty lousy code and you should *NOT* do this!!). I *always* advocate using "this" for class-level members to avoid this potentially confusion.

    Why don't you try taking care of that little "gotcha" and see if it helps. Let us know ....


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Omprakash Ojha Saturday, September 3, 2011 8:42 AM
    Friday, August 12, 2011 4:00 PM
  • Hi BonnieB, 

    Thanks for your reply and concern. I am really thankfull. It was also festive season here in India. So I am sorry for late reply.

     

    No, Details1View and Details2View are declared in the module as you can see in the code. sorry for code omition.  Regarding what you have denoted, I write my steps so that it may clarify the application flow :-

    1.in Form1, 2 access databases (eg hhs1 and hhs2) are selected and stored in the datasets.

    2. Datasets are then transferred to the Form2 or Form3 by attaching them to a class SelectedDatabases and calling Form2 or Form3 through constructors, on pressing of different option buttons (eg check)

    3. In Form2 or Form3, Datasets transferedd from Form1 are stored in local datasets. In Form2 two access databases are checked for matching records (code given previously is regarding Checking of data) and appending if not duplicates. In Form3 some repairs are done (eg replacing page nos with numbers more than in one table).

    I am not sure that my way is correct, but when using Datatables only instead of DataViews everything is fine but it takes a lot of time for each time storing a table into a DataTable. That's is why I tried to use DataViews. 

    Now I want to know that  

    - Is there fault in my way of Dataset transfer to other forms.

    - Is using  IF{} is correct for matching one record with all records in the other table (using for{} and Rows.count)?

    I hope I am stating my real problem. If anything more you really want, please inform me the same.

     


    Omprakash
    Wednesday, August 17, 2011 11:43 AM
  • Omprakash,

    A DataView is nothing more than a filtered "look" at a DataTable. If you do anything to a row in the DataView, you are also affecting the same row in its associated DataTable ... the two cannot be separated because they are one and the same.

    As far as the for{} loops go for finding matches, I think you'd be better off using a DataTable.Select or perhaps use LINQ. I can give you quick examples:

    Example using DataTable.Select():

    for (int n = 0; n < dtMaster2.Rows.Count; n++)
    {
      //M_Row_found = false;
      mschcd = dtMaster2.Rows[n]["schcd"].ToString();
      mvilcd = dtMaster2.Rows[n]["vilcd"].ToString();
      mtolaname = dtMaster2.Rows[n]["tolaname"].ToString();
    
      string M1Select= string.Format("schcd = {0} AND vilcd = {1} AND tolaname = {2}", mschcd, mvilcd, mtotaname);
    
      DataRow[] M1Rows = dtMaster1.Select(M1Select);
    
      //if (M_Row_found == true)
      if (M1Rows.Length > 0)
      {
        // etc.etc.etc. 
      }
    }
    
    
    

    Example using LINQ:

    for (int n = 0; n < dtMaster2.Rows.Count; n++)
    {
      //M_Row_found = false;
      mschcd = dtMaster2.Rows[n]["schcd"].ToString();
      mvilcd = dtMaster2.Rows[n]["vilcd"].ToString();
      mtolaname = dtMaster2.Rows[n]["tolaname"].ToString();
    
      DataRow[] M1Rows = dtMaster1.AsEnumberable()
        .Where(row => row.Field<string>("schcd") == mschcd &&
              row.Field<string>(vilcd") == mvilcd &&
              row.Field<string>("totaname") == mtotaname)
        .ToArray();
    
      //if (M_Row_found == true)
      if (M1Rows.Length > 0)
      {
        // etc.etc.etc. 
      }
    }
    
    

    These are only examples to show you the two different syntaxes.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Omprakash Ojha Saturday, September 3, 2011 8:43 AM
    Wednesday, August 17, 2011 3:48 PM
  • Hi BonnieB,

    Thanks for all,

    Yes, you are right about using DataTable.select(), but I think it requires the primary keys of the tables. And if I need  to search on more fields than primary keys e.g. I have defined schcd,vilcd and tolaname as my primary key in the table but during matching tables for duplicates I need to match on childname and Fathername also, then, will it work?. I have not tried LINQ but gonna try it.

    And hey, what about transferring Datasets or Datatables to other Forms? I would like to know about it I am very confused about it. It is stoping me from doing other projects. Hope I am not irritating you. It will be a great help for me. Thanks again and again.

     

     


    Omprakash
    Saturday, August 20, 2011 6:04 AM
  • Hi Omprakash,

    No, the DataTable.Select() does not require primary keys. You're thinking of the DataTable.Find(). You can put as many search criteria as you like in the .Select() method. Apparently you did not try the example I posted. =0(

    As far as passing anything to other forms, I've got a blog post about that:

    http://geek-goddess-bonnie.blogspot.com/2011/01/passing-data-between-forms.html


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Omprakash Ojha Saturday, September 3, 2011 8:43 AM
    Saturday, August 20, 2011 4:25 PM
  • Hi BonnieB Sorry for late reply. Actually I couldn't work on the application in lastweek due to heavy workload, I am going to try what you have stated and after I will contact you. Sorry for this, but request you to have a watch on this. I will reply soon very soon please. Hey I had a look on your blog post and found it very useful I have used the constructor approach for Dataset Sharing in my application, but unfortuaately due to the dataview error I could not fully utilize this because due to wrong records in Dataview it prevents me from appending,as it duplicates the records in the Database. I hope you understand my problem and let me try your suggestions.
    Omprakash
    Monday, August 29, 2011 6:20 AM
  • No problem Omprakash. After you've had a chance to try my suggestions, reply back here to let me know how it went.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, August 29, 2011 3:33 PM
  • Hi BonnieB,

    Using DataTable.Select() helped me a lot. It just did 'not replaced my for loops but also speeded my application and prevented apperance of MDA Exceptions "ContextSwitchDeadlock was detected". Now my application searches tables faster with a a million (more than 1000000) records within 2 to 5 minutes. Before, it actually refused to work.

    And I am pleased to inform you that my DataView's Double count show problem has been solved by the help of no one else but you. During this discussion with you and others I was forced to check my code because if you and other experts/MVP's were saying that it can not be so, I checked my code and found the cause. It was DataAdapter.Fill(). Actually in my applications's first Form I am selecting two access databases and displaying there records in DataGridViews, where Selecting a record in a DataGridView triggers to show the releated records in other DataGridView. Here I found that I was using the DataAdapter.Fill with a filtered query i.e.

    "select * form HouseHoldDetails where schcd=mschcd and vilcd=mvilcd and tolaname = mtolaname"

    Which was adding filtered records to the Dataset(and doubling the cound of that filtered records). The same dataset was transferred to the Next form where I was checking for duplicates. That is what causing the problem.

    When I used Select() there, it was all solved.

    I am greatfull to you all specially you, for your instant help and supervisions, and hope that you will reply more on threads started by me. I learned a lot from you during this discussion.

    A trillion thanks to you Bonnie B.

    With warm regards



    Omprakash
    Tuesday, August 30, 2011 6:50 AM
  • You're quite welcome, Omprakash! I'm so glad I could help and steer you in the right direction!

    How about coming back to this thread and marking one (or more) posts as an answer ... you know best which posts helped you.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, August 31, 2011 3:32 PM
  • Hi BonnieB,

    I have marked the posts as answer that all helped me to do so.

    Thanks for everything.

    Regards.

     


    Omprakash
    Saturday, September 3, 2011 8:45 AM