none
Select on dataset with keys gives another result than the same dataset without keys RRS feed

  • Question

  • I have got a dataset with 3 keys(client_id, place_id, cropno).

    when I do a DataTable.Select("crop_id = '" + cropid.ToString() + "' AND client_id = '" + clientID + "' AND cropno = " + cropno + ")

    it give me a result of 4 rows.

    when I do a DataTable.Select("client_id = '" + clientID +"' AND  crop_id = '" + cropid.ToString() + "'' AND cropno = " + cropno + ")

    gives me a result of 6 row.

    If I remove the keys from the dataset bouth selects gives a result of 4 rows!?

    Why is that?
    Tuesday, September 1, 2009 1:57 PM

Answers

  • Just found out that it was the cropno that did make the trouble.
    when adding keys to the dataset the cropno AllowDBNull property was changed from True to False.
    • Marked as answer by Yichun_Feng Thursday, September 3, 2009 6:52 AM
    Wednesday, September 2, 2009 8:14 AM

All replies

  • Could you provide some more code or info?

    The only thing that sticks out here is that in the 2nd select you have double single quotes after cropid.ToString().
    Perhaps that is causing the filter to return more rows than you have in the datatable.
    But that doesn't explain why removing the primary keys returns 4 rows.

    Also, according to documentation, the primary keys seems to have an effect on the select:

    "DataTable.Select Method (String)"
    http://msdn.microsoft.com/en-us/library/det4aw50.aspx
      "Gets an array of all DataRow objects that match the filter criteria in order of primary key (or lacking one, order of addition.)"

    //Michael
    This posting is provided "AS IS" with no warranties.
    Tuesday, September 1, 2009 3:12 PM
  • The select's should be the same but the order of the client_id and crop_id.

     

    DataTable.Select("crop_id = '" + cropid.ToString() + "' AND client_id = '" + clientID + "' AND cropno = " + cropno + ")
    DataTable.Select("
    client_id = '" + clientID +"' AND  crop_id = '" + cropid.ToString() + "' AND cropno = " + cropno + ")

     

    But this must be a bug. It cannot be right that the order of the select items and if the dataset have keys or not should give a different result.

    Is there some kind of bug using GUID's. Crop_id and client_id is type GUID.

    I have expirenced that doing a DataTable.Select("client_id = '" + clientID +"'") may not find any rows
    but doing a DataTable.Select("NOT client_id <> '" + clientID +"'") find rows on the same dataset????

    Does GUID's and datasets not work.

    Wednesday, September 2, 2009 6:03 AM
  • GUID and DataTable.Select seems to work. Still hard to know what is going on at your side without more info. Is it possible to provide an example that shows your issue.
    Something like this:

            static void Main(string[] args)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add(new DataColumn("col_1", typeof(Guid)));
                dt.Columns.Add(new DataColumn("col_2", typeof(Guid)));
                dt.Columns.Add(new DataColumn("col_3", typeof(int)));
    
                DataColumn[] keys = new DataColumn[3];
                keys[0] = dt.Columns[0];
                keys[1] = dt.Columns[1];
                keys[2] = dt.Columns[2];
    
                Guid g1 = Guid.NewGuid();
                Guid g2 = Guid.NewGuid();
    
                dt.PrimaryKey = keys;
                for (int i = 0; i < 5; i++)
                {
                    dt.Rows.Add(new object[] {g1, g2, i });
                }
    
                foreach (DataRow r in dt.Rows )
    	        {
                    Console.WriteLine("{0} {1} {2}", r[0].ToString(), r[1].ToString(), r[2].ToString());    		 
    	        }
    
                int cropno = 3;
                //DataRow[] dr = dt.Select("col_1 = '" + g1.ToString() + "' AND col_2 = '" + g2 + "' AND col_3 = " + cropno);
                DataRow[] dr = dt.Select("col_2 = '" + g2 + "' AND col_1 = '" + g1.ToString() + "' AND col_3 = " + cropno);
                Console.WriteLine(dr.Length.ToString());
            }
    Example above works the same independent of order of arguments.
    //Michael

    This posting is provided "AS IS" with no warranties.
    Wednesday, September 2, 2009 7:52 AM
  • Just found out that it was the cropno that did make the trouble.
    when adding keys to the dataset the cropno AllowDBNull property was changed from True to False.
    • Marked as answer by Yichun_Feng Thursday, September 3, 2009 6:52 AM
    Wednesday, September 2, 2009 8:14 AM