none
How to filter with DataTable.Select() with blank field in filter expression RRS feed

  • Question

  • Hi Everybody,

    I recently faced a problem,I am doing a data append program, in which I have to check for records in two tables for matching. If not found then add it to the one table. I used the following filter expression to filter the matching records.

    string fstring = "schcd='" + mschcd + "' AND vilcd = '" + mvilcd + "' AND tolaname='" + mtolaname +
                            "' AND PAGENO= '" + mpageno + "' AND SLNO= '" + mslno + "' AND CHILDNAME= '" + mchildname + "' AND FATHERNAME= '"
                                + mfathername + "'";
    DataRow[] F_dtDetails1Rows  = dtDetails1.Select(fstring);

    But Select() does not return the desired resul,  if anyone or all of the variables mtolaname, mchildname and mfathername  are blank (i.e. =''). Although the same record is in the other table. That means it should filter the records but it is not doing the same.

    When I tried it with DataView.RowFilter it is fine. What may be the problem? Please anyone help. It is really frustrating.

    Omprakash
    • Moved by Paul Zhou Wednesday, September 28, 2011 8:50 AM (From:.NET Base Class Library)
    Monday, September 26, 2011 11:54 AM

All replies

  • Are you sure they're really empty and not null? And the same RowVersion is used for both methods?

    This would be really strange if DataView worked fine with the same data, because both Select and RowFilter internally use the same filter expression parser.

    Monday, September 26, 2011 12:14 PM
  • Hi Alex,

    The databases are Access 2003 databases and yes they are empty(zero length string) not null. I checked it through the statement

    SELECT householddetails.SCHCD, householddetails.VILCD,IIf(IsNull([tolaname]),"Unknown",Format([tolaname],"@;""ZLS""")) AS Expr1
    FROM householddetails
    WHERE (((householddetails.TolaName)=""));

    I used dataview to filter with the string  str= "schcd='" + mschcd + "' AND vilcd = '" + mvilcd + "' AND tolaname='" + mtolaname;  and looped through dataview for matching, and it worked fine.

    Do I need to use DataView for this as DataRow[] collection is not suitable also for calculations or using aggregate functions with them like max, min, avg etc.


    Omprakash
    Tuesday, September 27, 2011 6:27 AM
  • Move 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.

    Wednesday, September 28, 2011 8:50 AM
  • Hello,

    It looks your code snippet is OK. IMO, I recommend you can check the following suggestions.

    1. I suggest you first copy your query statement into ACCESS and execute it. Can it return the correct result?

    2. Please double check the string concatenation such as double quotation, quotation, etc.

    Thanks,


    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.

    • Marked as answer by Larcolais Gong Wednesday, October 5, 2011 5:04 AM
    • Unmarked as answer by Larcolais Gong Sunday, October 9, 2011 2:39 AM
    Wednesday, September 28, 2011 2:24 PM
  • Hi Gong and everybody,

    Sorry for late reply, it is festive season here in India,

    Gong,  I tested my queries in access I copied the expression value from application as it is here and used with select statement as below

    SELECT * FROM HOUSEHOLDDETAILS
    WHERE
    schcd='10021500601' AND vilcd = '100215006' AND tolaname='SARAIBANWARI' AND PAGENO= '3' AND SLNO= '8' AND CHILDNAME= 'sadab ali' AND FATHERNAME= ''

    but it showed data type mismatch, I corrected the pageno and slno values and it did not showed any records but when I removed the blank valued field FATHERNAME from query it showed the result record. 

    Again when I executed the same query with all fields with non-blank values it showed  data type mismatched error, after correction it worked.

    so, I think there is no problem in  the expression. as it worked with non-blank values in the application without data type mismatch error. I hope it is the problem of DataTable.Select(). Please Help

     


    Omprakash
    Saturday, October 8, 2011 11:03 AM
  • Hi Gong and everybody,

    Sorry for late reply, it is festive season here in India,

    Gong,  I tested my queries in access I copied the expression value from application as it is here and used with select statement as below

    SELECT * FROM HOUSEHOLDDETAILS
    WHERE
    schcd='10021500601' AND vilcd = '100215006' AND tolaname='SARAIBANWARI' AND PAGENO= '3' AND SLNO= '8' AND CHILDNAME= 'sadab ali' AND FATHERNAME= ''

    but it showed data type mismatch, I corrected the pageno and slno values and it did not showed any records but when I removed the blank valued field FATHERNAME from query it showed the result record. 

    Again when I executed the same query with all fields with non-blank values it showed  data type mismatched error, after correction it worked.

    so, I think there is no problem in  the expression. as it worked with non-blank values in the application without data type mismatch error. I hope it is the problem of DataTable.Select(). Please Help

     


    Omprakash


    This is incorrect: "PAGENO= '3' AND SLNO= '8' "

    Integers are not supposed to be wrapped in quotes.


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Saturday, October 8, 2011 1:31 PM
  • I don't know if Access is the same as SQL Server, but perhaps it has to do with char vs varchar data types. In SQL Server, a char is never actually empty (as in having a value of "") because it will actually be filled with a number of spaces. So, a char(8) field will contain 8 spaces ... this will not compare correctly to a string with nothing in it at all. Whereas a varchar(8) field, if it is empty, will not have anything in it, no spaces.

    I suggest you try using a TRIM in your query somewhere (for example, TRIM(FATHERNAME) = '').   I haven't actually tried this, so I may have the syntax wrong, but it will be something like this. I know for sure that in SQL Server, this will be a problem with char fields unless they're trimmed somewhere.

    You could also consider trimming them when you read the data into your DataTable ... then you won't have to worry about it when you manipulate the data in your DataTable ... this is probably preferable, but it depends on what you're doing with your data I suppose.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, October 8, 2011 5:17 PM
  • James, yes that's right,but isn't it the way we create strings for use in filteration etc. in .Net. e.g.

     string fstring = "schcd='" + mschcd + "' AND vilcd = '" + mvilcd + "' AND tolaname='"        +mtolaname +"' AND PAGENO= '" + mpageno + "' AND SLNO= '" + mslno + "';

    And it works well.

     


    Omprakash
    Friday, October 14, 2011 7:21 AM
  • Hi Bonnie,

    It does not work for blank values or zero length strings as it is said in Access. I want to know that is there any way to use Select () method with blank values or zero length strings?

    Thanks.

     


    Omprakash
    Friday, October 14, 2011 7:26 AM
  • James, yes that's right,but isn't it the way we create strings for use in filteration etc. in .Net. e.g.

     string fstring = "schcd='" + mschcd + "' AND vilcd = '" + mvilcd + "' AND tolaname='"        +mtolaname +"' AND PAGENO= '" + mpageno + "' AND SLNO= '" + mslno + "';

    For strings, yes.  For numeric values, no.  So instead of:

     
    "PAGENO= '3' AND SLNO= '8' "
    
    

     

    You'd want to remove those single quotes:

     

     
    "PAGENO= " & 3 & " AND SLNO= " & 8
    
    

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Friday, October 14, 2011 12:01 PM
  • Omprakash,

    First, your string would be a lot more readable if you used string.Format():

    string fstring = string.Format("schcd='{0}' AND vilcd = '{1}' AND tolaname='{2}' AND PAGENO= {3} AND SLNO= {4} AND CHILDNAME= '{4}' AND FATHERNAME= '{5}'", 
        mschcd, mvilcd, mtolaname, 
        mpageno, mslno, 
        mchildname, mfathername  );
    DataRow[] F_dtDetails1Rows  = dtDetails1.Select(fstring);
    
    

    Second, for Access command, are you sure that the following doesn't work in Access?

    SELECT * FROM HOUSEHOLDDETAILS
     WHERE schcd='10021500601' 
         AND vilcd = '100215006' 
         AND tolaname='SARAIBANWARI' 
         AND PAGENO= 3 
         AND SLNO= 8 
         AND CHILDNAME= 'sadab ali' 
         AND TRIM(FATHERNAME)= ''
    
    
    

    Third, how are you filling your DataSet? What is the query? Have you tried using TRIM()?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, October 14, 2011 3:48 PM
  • Hi BonnieB,

    I will use the string.Format statement as you say.

    Yes it does not show me the intended record. When I remove the Fathername field (the field with blank value)  from expression, it shows me the record.

    For filling of dataset, I am asking the user to select the database through OpenFileDailog and then creating the connection. The Code is below

    //Creating Connection
    
    try
     {
          File1.Fname = 	System.IO.Path.GetFileName(openFileDialog1.FileName);
          File1.path = System.IO.Path.GetDirectoryName(openFileDialog1.FileName);
     }
     catch 
     {
          MessageBox.Show("File not selected");
          Environment.Exit(0);
     }
    con1string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File1.path.Trim()+ "\\" + "\\" + File1.Fname.Trim() + ";Jet OLEDB:Database Password=admin345";
    con1 = new OleDbConnection();
    con1.ConnectionString = con1string;
    
    //creating Dataset
    HHS1Dataset = new DataSet();
    
    //Filling the Dataset 
    Master1DataAdapter.Fill(HHS1Dataset, "HouseHoldMaster");
    Details1DataAdapter.Fill(HHS1Dataset, "HouseHoldDetails");
    schools1dataadapter.Fill(HHS1Dataset, "Steps_Keyvar");
    
    

     

     


    Omprakash
    Monday, October 17, 2011 6:37 AM
  • Hi James,

    Thanks for reminding me. Actually I was using string variables to store the page nos and sl nos. that is why this is. 

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

    But now when I want to store pageno from Dataview to an integer variable like below and use in comparision

    int mpageno = (int)Details2View[x]["pageno"];
    int mslno = (int)Details2View[x]["slno"];
    
    if (Details1View[y]["schcd"].ToString() == mschcd && Details1View[y]["vilcd"].ToString()
                                    == mvilcd && Details1View[y]["tolaname"].ToString() == mtolaname &&
                                    (int)Details1View[y]["Pageno"] == mpageno &&
                                    (int)Details1View[y]["slno"] == mslno &&
                                    Details1View[y]["Childname"].ToString() == mchildname &&
                                    Details1View[y]["Fathername"].ToString() == mfathername)
                                {
                                    matched = true;
                                    break;
                                }
    

    It show the error during runtime that "specified cast is not valid".

    As I know that objects like dataview or datarow allows convertion only in string type.I would like to know the other way to store the objects(dataview,datatables,datarow,datarow[] etc)  into variables of different types. 

    Thanks in advance


    Omprakash
    Monday, October 17, 2011 7:58 AM
  • It show the error during runtime that "specified cast is not valid".

    As I know that objects like dataview or datarow allows convertion only in string type.I would like to know the other way to store the objects(dataview,datatables,datarow,datarow[] etc)  into variables of different types. 

    Thanks in advance


    Omprakash

     

    The short answer is: correct your database.

    Why would you store a whole number as a string?  Probably the best advice I have ever recieved over the years by anyone was to start ANY system/implementation/application with the database.  I mean, don't even write a single line of code in the application tier until you get your database finalized and CORRECT. 

    [And if you think about it, Microsoft enforces this idea in a round-about way too with the DataSet designer (to setup your application datasets, you must be able to connect to an existing database first).]

    In any event, I think your variable in the application should model the objects in your database.  But again, you should probably correct the database first.  If the pageno field is actually storing integer values, then set the field to integer and you will not have these kinds of conversion issues to deal with in your application. 


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Monday, October 17, 2011 11:24 AM
  • Yes it does not show me the intended record. When I remove the Fathername field (the field with blank value)  from expression, it shows me the record.


     

    That is because there is no match for a childname='sadab ali' AND a fathername=''

    If you change this to an OR, it will not enforce a match on BOTH child and father names.  This would probably find match(es):

    childname='sadab ali' OR a fathername=''


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Monday, October 17, 2011 11:28 AM
  • As I know that objects like dataview or datarow allows convertion only in string type

    I don't know what you mean by that. You can store any data type in a DataRow. And you can use any of the various conversion mechanisms to convert any data type. Could you explain what you mean?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, October 17, 2011 3:36 PM
  • Hi James,

    Actually there is a match and I see that in the table. When I remove the fathername='' condition from the filter string it shows the record. 

    As I said earlier, it is working fine in the application but when it encounters the field with zero length string or blank value it is not filtering the record.

     


    Omprakash
    Saturday, October 22, 2011 11:30 AM
  • Hi BonnieB,

    Sorry for confusion. Actually Iwanted to say that like the example given above how can I store the value stored in a DataRow Object into a variable. e.g. 

    int mpageno = 0;
    mpageno = dtDetails2Rows[x]["pageno"]
    

    here dtDetails2Rows is a datarow pageno is an integer field.

    But it shows a compile-time error  "Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?)". Then I am using a string variable mpageno and getting the value as below

    string mpageno;
    mpageno = dtDetails2Rows[x]["pageno"].ToString();
    
    so i wanted to know that how to store object values into variables. Hope I am stating what I want. Thanks.

     


    Omprakash
    Saturday, October 22, 2011 11:54 AM
  • A couple of things:

    1) You did not reply to my question about whether or not this query works (in Access):

    SELECT * FROM HOUSEHOLDDETAILS
     WHERE schcd='10021500601' 
         AND vilcd = '100215006' 
         AND tolaname='SARAIBANWARI' 
         AND PAGENO= 3 
         AND SLNO= 8 
         AND CHILDNAME= 'sadab ali' 
         AND TRIM(FATHERNAME)= ''
    
    
    

    2) You can use Convert or TryParse to convert an object to int (or any other datatype):

    // either this:
    int mpageno = Convert.ToInt32(dtDetails2Rows[x]["pageno"]);
    
    // or this:
    int mpageno = 0;
    int.TryParse(dtDetails2Rows[x]["pageno"], out mpageno);
    
    


    3) If you use Typed DataSets, you don't need to convert at all because the pageno column would already be of type int:

    int mpageno = dtDetails2Rows[x].pageno
    




    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, October 22, 2011 7:14 PM
  • Hi BonnieB,

    No, the query didn't work, but after removing the   AND TRIM(FATHERNAME)= '' from query expression it shows the record.

    And, I couldn't use typed datasets because Databases are selected at runtime,  although the schema is known at design time.

     

    I don't remember but I have read somewhere  or in MSDN that  DataTable.Select() does not work for blank or null field values in expression. ie. it won't select the rows for null values or zero length strings in the filter expression.

    Thanks.


    Omprakash
    Monday, October 24, 2011 6:10 AM
  • Ah ... now I realize what the problem is! It's NULLs. Your data probably contains NULL in that FatherName column, not an empty string. Are you sure you want to include a NULL as being an empty string? If so, your query becomes this:

    ... AND (FATHERNAME = '' OR FATHERNAME IS NULL) ...
    

    That would be used for either the database query or the DataTable.Select

    And, I couldn't use typed datasets because Databases are selected at runtime,  although the schema is known at design time.

    A very column misconception about Typed DataSets. That is why I don't like the fact that the Typed DataSet generator puts the TableAdapters in the with the generated DataSets. It shouldn't do that  (and it didn't used to back in the early days) and it can easily be avoided. See my blog post about this:

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, October 24, 2011 2:54 PM
  • Hi BonnieB,

    appending AND (FATHERNAME = '' OR FATHERNAME IS NULL)  in the filter string works in Access.

    SELECT * FROM HOUSEHOLDDETAILS 
    WHERE schcd='10021500601' And vilcd='100215006' 
    And tolaname='SARAIBANWARI' And PAGENO=3 And SLNO=8 
    And CHILDNAME='sadab ali' And (TRIM(FATHERNAME)='' Or 
    FATHERNAME Is Null);
    
    

    But when I use it with DataTable.Select(), it  takes very very long time even for just 500 records and application goes non-responsive. 

    And one more thing,  I used the following query to test the values for null or Zero Length String it did not show a single record with NULL value, although there were fields with  blank(or null value as you say) values

    SELECT householddetails.SCHCD, 
    householddetails.VILCD,IIf(IsNull([tolaname]),"Null","ZLS") AS Expr1
    FROM householddetails
    WHERE (((householddetails.TolaName)=""));
    
    

    As it worked with access but takes very long time to search with  Select() method I would like to know the way out for this

    Thanks a lot.

     

     


    Omprakash
    Friday, October 28, 2011 9:14 AM
  • Hi Omprakash,

    I'm sorry, I know better but for some reason I posted my example backwards. One should always check for nulls first, so that example should really have been

    AND (FATHERNAME IS NULL OR TRIM(FATHERNAME)='')
    

    Try that ... it is much quicker!

    And one more thing,  I used the following query to test the values for null or Zero Length String it did not show a single record with NULL value, although there were fields with  blank(or null value as you say) values

    SELECT householddetails.SCHCD, 
    householddetails.VILCD,IIf(IsNull([tolaname]),"Null","ZLS") AS Expr1
    FROM householddetails
    WHERE (((householddetails.TolaName)=""));
    

    I'm not sure where the above code is being used ... and what you're actually trying to see by using it. And also, for clarification ... blank is not the same as null. Do you get the same results (no hits with the IsNull() function ) if you leave off the WHERE clause?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, October 28, 2011 3:10 PM
  • Hi BonnieB,

    Sorry for late reply. 

    I did alter my select filter string as you suggested but nothing changes with Datatable.select(). It takes almost 8 minutes to search for 800 records. 

    And actually, I used that query to check that values are null or empty and it shows that values are not null. Yes even after removing the where clause it didn't show a single null value.

    As I said in my post of 24 Sept. I think select() method is not used for Blank field values. Instead it is better to use DataView. I am usnig it and it is working fine and fast.

    If you don't mind I would like to know the better ways to use ADO.Net objects in applications. any suggestion or links are welcome.

    Thanks a lot.

     

     

     


    Omprakash
    Friday, November 4, 2011 7:12 AM
  • Remove the Trim() function -- you should see faster results.

    If you designed the database in the Access UI the CHAR datatype is not accessible, you'd have to use a DDL SQL to create your table. So, basically every Text typed field in an Access (actually Jet/ACE :) ) database is a nvarchar.  Also, be sure to check the value of the Allow Zero Length property of the FATHERNAME field -- as well as all the other Text datatyped fields.  You can check it easily in the Access UI, if it is set to Yes, then obviosly ZLS's are allowed, if set to No, then ZLS's are not allowed and there is no need to check for them.

    So, Trim() is likely not neccessary.

    SELECT * FROM HOUSEHOLDDETAILS
    WHERE schcd='10021500601' And vilcd='100215006'
    And tolaname='SARAIBANWARI' And PAGENO=3 And SLNO=8
    And CHILDNAME='sadab ali' And (FATHERNAME Is Null Or FATHERNAME='');

    Also, I would suggest that you add indexes (at the database table level using the Access UI) to the fields you commonly search on that have a large domain of values, especially the Text datatyped fields, but don't overdo it because inserts can be effected --- at a minimum an index on CHILDNAME and FATHERNAME is likely appropriate in this case.


    Brent Spaulding | Access MVP
    Saturday, November 5, 2011 2:58 AM
  • PS ...

    My standard mode of operation, with working with a Jet/ACE datafile is to set Allow Zero Length to No (False).  I do not want to be troubled with testing for Null OR a ZLS when searching for 'Blanks'.

    Although that is my standard mode, I have been faced with dataloggers, or dataconsumers, that don't have a understanding of what Null is, so, I go the other extreme -- I set Allow Zero Length to Yes (which is the default for the A2003 UI and up -- IIRC), then set the field to be Required, then set the default to a ZLS, then if I see a 'blank', I know its a ZLS.


    Brent Spaulding | Access MVP
    Saturday, November 5, 2011 4:17 AM
  • Hi Omprakash,
    As I said in my post of 24 Sept. I think select() method is not used for Blank field values. Instead it is better to use DataView. I am usnig it and it is working fine and fast.

    OK ... it sounds like you'd prefer using the DataView anyway. I guess this discussion was more along the lines of you wondering why you got different results using DataView vs Select() ... ?

    Anyway, take whatever advice Brent gives you ... since he's an Access MVP, he'll know more than I do about the proper way to use Access (I use SQL Server).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, November 6, 2011 1:07 AM
  • And one more thing,  I used the following query to test the values for null or Zero Length String it did not show a single record with NULL value, although there were fields with  blank(or null value as you say) values

     

    SELECT householddetails.SCHCD, 
    householddetails.VILCD,IIf(IsNull([tolaname]),"Null","ZLS") AS Expr1
    FROM householddetails
    WHERE (((householddetails.TolaName)=""));
    
    

    As it worked with access but takes very long time to search with  Select() method I would like to know the way out for this

    Thanks a lot.

     


    As a point of note, the above SQL statement will NOT test for Nulls.  Nulls and ZLS's are not the same thing.  In the above your WHERE clause is only allowing records to be returned if their Tolaname value is a ZLS, then you are using IsNull() to see if the field value of Tolaname is a Null or not, which it will NEVER be since your WHERE clause prevented anything other than ZLS's to be returned.

    A proper test for Null's in a field would be (against a Jet/ACE backend):

    SELECT SCHCD, VILCD,IIf([tolaname] Is Null,"Null","ZLS") AS IsNullOrZLS
    FROM householddetails
    WHERE TolaName="" Or Tolaname Is Null
    
    

    You can modify the statement to test other fields as well.


    Brent Spaulding | Access MVP
    Monday, November 7, 2011 7:43 PM
  • >> Anyway, take whatever advice Brent gives you <<

    Eeeek! ... the pressures on now! :)

    >> ... (I use SQL Server). <<

    Actually -- I do to :)  I just use Access as the Front End to my SQL Server data as a standard mode (at least outside of my day job :) ). 

    >> I guess this discussion was more along the lines of you wondering why you got different results using DataView vs Select() ... ? <<

    .. Umm .. in the paraphrased words of a person I admire and look up to in the C# world ... take whatever advice Bonnie gives you ... since she's a C# MVP, she'll know more than I do about the proper way to utilize DataView's or DataTable's (I am a novice at best with C# :) )


    Brent Spaulding | Access MVP
    Monday, November 7, 2011 7:50 PM
  • Alex ...

    Sorry for the multiple clicks on proposed answer and such :-s.  My browser was not refreshing -- etc, etc, ... but in the end, the issue seems to definiately be with respect to Nulls and ZLS's, so ... there you have it.


    Brent Spaulding | Access MVP
    Monday, November 7, 2011 8:16 PM
  • I just use Access as the Front End to my SQL Server data as a standard mode (at least outside of my day job :) )

    That's interesting! I guess that way you don't have to write any .NET code! ;0)

    .. Umm .. in the paraphrased words of a person I admire and look up to in the C# world ... take whatever advice Bonnie gives you ...

    Thanks so much, Brent! That's very nice of you to say!  =0)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, November 7, 2011 8:53 PM
  • >> That's interesting! I guess that way you don't have to write any .NET code! ;0) <<

    Nope ... but extensive use of VBA, and Linked Table objects if you do an Access Application "right".  It is my opinion that for internal buisness apps that focus utilize Forms, Reports, and databases ... Access is a really tough package to beat, if not the package to beat.  I have looked at LightSwitch ... and my opinion is unchanged!

    Unfortunately, Access (and 'true' Access Developers) are often given the short end of the stick with the assumption that a bad Access Front End (or Back End for that matter) is the fault (or limitation) of Access Application or the Jet/ACE database engine, instead of the developer's knowledge base of the Access Application as a development environment and the capbilities of the Jet/ACE database engine.

    My "flagship" Access app uses SQL Server as a backend (lots of triggers and stored proc's), many tables have "millions" of rows. The Front End has about 35k lines of code.  Typical concurrent connections to the Back End hovers around 50, peaks around 150.  The Access FE is distributed world wide and the data is accessed through the secured WAN -- response is kinda slow overseas, but the bottle neck is not Access :).  The main purpose of the application is presentation of data that is automatically logged to the main datastore, while linking that data with other SQL Server datastores in order to answer questions like: "what vehicle was built on pallet x at 5:03pm?" or "What robots contributed to the 33mins of accumulated stop time on line x for the todays shift?" or "What welds had been checked on vehicle 53498 as it passed station X?"

    Ok ... enough rambling about Access! :D   I gotta get back to writing some more code with C# --- HttpWebRequests! -- Woo Hoo!  {Now, that is just a bit challenging in Access/VBA!}


    Brent Spaulding | Access MVP
    Monday, November 7, 2011 10:26 PM
  • You app sounds very cool!!

    I just really never gave it a thought about using Access as a front-end UI to a back-end SQL Server database (although I know people do it all the time). That probably comes from the fact that I came from a FoxPro background and used *that* as my front-end UI to SQL Server databases. I guess it's all what you're used to doing, eh? But, I dropped FoxPro for .NET/C# in early 2002 and haven't looked back. ;0)

    As for the HttpWebRequests ... I'll leave that to you ... I don't do web apps either! ;0)

    Have fun!


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, November 7, 2011 11:26 PM
  • Hi Brent,

    Removing trim() works but it is slower.

    I can't modify the databases as they are dispersed in the field and a lot of data is there.


    Omprakash
    Tuesday, November 8, 2011 10:59 AM
  • Hi Bonnie,

    Oh No!!, I would prefer to use select(). As it helped me a lot until I discovered this problem.

    You are the person who suggested to use select() and it helped, I am thankful for  that.

    Actually it irritates me to change my code for such simple things again and again. 

    And I think that Access Databases are more vulnerables  and error prone, as I have experienced. But I can't convert them to others as it will take a lof time.

    So I would like to polish my knowledge about .net objects and ways of their usage,  and I am not  fluent in both Access and SQL Server.

    Regards


    Omprakash
    Tuesday, November 8, 2011 11:09 AM
  • >> Removing trim() works but it is slower. <<

    In what environment is is slower?  There are many situations and configurations that can adversly affect the performance of a query for data that is managed by the Jet/ACE database engine, removing a function call is not one of them.  If you saw slower performance in the Access application of your SQL statement without the Trim() function in it, then something else slowed it down.  Remember that Jet/ACE database files are managed via a local instance of the Jet/ACE database engine.  What that means is that if your computer is 'running slow' on a particular day, then access to your Jet/ACE managed data sources will 'run slow' also.  In addition, if your datafile is buried in a network folder, the quality of your connection can have a drastic affect upon the speed in which your query is processed.  Also, the number of concurrent connections to the datafile affect the speed of your query as well.  But, again, the removing of a function call will not have an adverse effect upon the speed with which Jet/ACE processes the query.

    Now if the slow down occurred using a DataTable object's .Select(filter) method, that may be a different story.  To start off with, the filter argument must be created with a syntax that is compliant to what .Net understands (defined here) and thus evaluated the data as it is tested against the filter criteria.  Next, lets look as the .Select(filter) method.  The .Select() method is not a request to the database engine for data.  The .Select() method performs its operations on in memory data.  In theory, filtering/sorting data in memory is faster than asking the database engine to do it since disk IO and/or network resources are not consumed.  However, the .Select() has been categorized by some to be 'slow' and in many circumstances it is faster to just .Fill() your DataTable with parameterized .SelectCommand (aka: a SELECT statement with a WHERE clause) in order to retrieve (load into memory) the sub-set of data you want to work with.

    >> Actually it irritates me to change my code for such simple things again and again. <<

    Understanding of your datastorage and how the database engine interacts with .Net (or any language performing the data access) is not always simple.  For example, the understanding of the difference between Null (the absence of data) and an empty string (a string with no length).  You must understand how the database engine that manages your data handles the two distinctly different values.  Then on top of that, you have to understand (or be aware of) how your programming language sends a query (aka: SQL statement) to the database engine for processing.  Plus on top of all this, the paradigm in which the programming lanquage or data access object library manages your data may add its own little quirks to the mix.  Note that .Net utilizes a 'disconnected' paradigm, which means: connect, load data into memory, disconnect, manipulate/use your data.  And if your data changes; connect, write changes back, disconnect.

    So as we seek resolution to your issue remember that the .Select() method of the DataTable object is not doing the same thing a SELECT query executed in the Access UI environment is doing.  They are two completely different processes.  Comparisons of speed and behavior between the two processes should be judged understanding that difference.  It is more appropriate to have the same expectations of speed and behavior from a .SelectCommand coupled with a .Fill of a DataAdapter and a SELECT query executed in the Access environment since both of those scenarios are requesting the database engine to return data.  But one important note -- Access is very smart, and when Access tells the Jet/ACE engine to return data; in most cases, the engine will only work hard enough to return the data the user (client) can see (has need for).  In other words, if your Datasheet View of a Query object in the Access environment is 20 rows in height, the database engine will only return the full row data for those 20 rows -- I call this 'lazy loading'.  So, in order to 'fully populate' your memory (like .Net does with the .Fill method), you will need to go to the last record of the resultant set of data.  Also, if your query (ran in the Access environment) has an expression column, the expression is not evaluated until there is a need to evaluate it (ie: becomes visible to user).

    >> I think that Access Databases are more vulnerables  and error prone <<

    I respect your opinion, but I must disagree with statements of vagueness that may lead the reader to assumptions that are not true.  What vulnerabilities are you refering to? and what errors are more prone to Jet/ACE database files?  In this thread I have not read about any errors or vulnerabilities regarding the Jet/ACE database file.  I have only read about the differences in efficiencies and behaviors between the .Select() method of a DataTable object and a SELECT query executed from the Access UI.

    ----

    I hope this information contains some useful thoughts and is interpreted by the reader in the same way in which it was written -- just folks talking tech -- no negative tones or conotations embedded within!


    Brent Spaulding | Access MVP
    Tuesday, November 8, 2011 5:25 PM
  • >> I guess it's all what you're used to doing, eh? <<

    Most definately!

    >> I dropped FoxPro for .NET/C# in early 2002 and haven't looked back. ;0) <<

    I like to say I am 'integrated .Net/C# into my toolset :) ... in otherwords, I still look sideways to make sure my security blanket is still there!

    >> As for the HttpWebRequests ... I'll leave that to you ... I don't do web apps either! ;0) <<

    That's the "fun" part ... I don't either! :-s


    Brent Spaulding | Access MVP
    Tuesday, November 8, 2011 5:40 PM
  • Ok ... I was thinkin' ... I like LINQ ...  so I am wondering if something like this might be a bit faster <shrug> ...

    DataRow[] F_dtDetails1Rows = (from r in dt.Rows.Cast<DataRow>()
                            where (((string)r["schcd"] ?? "") == (mschcd ?? ""))
                                && (((string)r["vilcd"] ?? "") == (mvilcd ?? ""))
                                && (((string)r["tolaname"] ?? "") == (mtolaname ?? ""))
                                && ((int?)r["pageno"] == (int?)mpageno)
                                && ((int?)r["slno"] == (int?)mslno)
                                && (((string)r["childname"] ?? "") == (mchildname ?? ""))
                                && (((string)r["fathername"] ?? "") == (mfathername ?? ""))
                            select r).ToArray();
    
    

    {note: air code!! so its untested}


    Brent Spaulding | Access MVP
    Tuesday, November 8, 2011 7:41 PM
  • Brent, I did some benchmark tests comparing DataTable.Select() against LINQ .Select() and wrote a blog post about it:

    http://geek-goddess-bonnie.blogspot.com/2011/04/linq-with-datasets.html

    Generally speaking, LINQ comes out faster if you're not using it to select DISTINCT. With DISTINCT, it depends more on the data. Here's my benchmark testing comparing the two when using DISTINCT:

    http://geek-goddess-bonnie.blogspot.com/2011/09/distinct-linq-versus-dataset.html


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, November 9, 2011 6:02 AM
  • Hi Brent,

    Sorry for late reply. I could not be on the net last week.

    I read your post, then I tried to search how .Net and Access treats the empty or zerolengthstring. I found that  --

    Access treats them as zero length string (if allowed in the database) and .Net treats them as Null (Dbnull). 

    mfathername = (string)dr["fathername"];

    did not work if fathername's length is zero, DbNull exception occurs. 

    I could not found any way to test any table field for null with select()

    dt.select("field=Null")

    I think using IS NULL in filter string with a Access database in .Net platform does not work as access treats not entered string fields as zero length string and not null.

    Any help?

    thanks

     

     

     

     


    Omprakash
    Thursday, November 17, 2011 10:37 AM
  • Hi

    I just tried to say that access databases gets easily corrupted as I am experiencing while abnormal program executions or some thing other. it may be due to my little  knowledge of Access too.  

    I would like to know about the differences in efficiencies and behaviors between the .Select() method of a DataTable object and a SELECT query executed from the Access UI or better ways to use Access with .Net.

    Regards!


    Omprakash
    Thursday, November 17, 2011 10:46 AM
  • Hi Omprakash,

    Use the Select like this for Nulls and Blanks instead of dataTable.Select("FATHERNAME='' ");

    dataTable.Select(" ISNULL(FATHERNAME , '')='' ");

    Thanks.


    Wednesday, September 25, 2013 7:19 AM