C#/MS Access Validation Rule RRS feed

  • Question

  • Hi,

    I've created a database in ms access 2003 set the validation rule to <like "[0-9 a-z A-Z]*"> which is supposedly alphanumeric input, using the ms access interface it works fine however, when i use the c# language to execute a non query like:
    insert "value017" into tableA
    it doesn't work.

    My connections are working perfectly when i remove the validation rule i can insert and when i change like into something like >, <... etc.., it works fine.. i wonder if there is some issue regarding the "like" keyword on c#...

    Thursday, August 30, 2007 6:24 AM

All replies

  • I don't think I would use validation rules set at the database level for a Microsoft Access database. For the most part those were designed for the DAO object model. For example, the wild-card character for DAO is an asterisk while for ADO and Jet OLEDB it's a percent sign. That might be one reason why the validation rule is not functioning as expected.



    Friday, August 31, 2007 12:38 PM
  • Thank you for the reply so it's a bad idea to set the validation at the database level... I see so i should set validation at the code level... i wonder if anybody knows a fast and effecient way of data validation instead of looping to all the changed data and check each column if data entries are valid.
    Monday, September 3, 2007 2:07 AM
  • I have the opposite opinion.  I think it IS good practice to validate data at the database level (of course, in addition to validation at the coding level).  Even if it is an MS Access table.  Relying on a programmer to close every loophole in his code opens yourself up to the possibility that bad data will sneak through.  Further, if more than one application and/or programmer is updating the table you open yourself up to problems with your data.  Take extra time to design and set up your validation rules carefully, and you will be assured that bad data won't get into the table in the first place.  Validation rules at the table level hold the programmer accountable by making him clean up code that bombs because it doesn't meet the table's validation rules.  Prevent garbage from getting in due to sloppy programming.


    That aside, to answer your question I think you have your syntax slightly wrong. 


    Instead of this:  Like"[0-9 a-z A-Z]*"

    Use this:          Like "[0-9][a-z][a-z]*"

    Or this:            Like "#[a-z][a-z]*"


    Please note that the [a-z] check for an alpha character is case insensitive.  So using this validation rule, your table will allow either an upper or lower case character in the 2nd or 3rd positions.


    Feel free to use validation rules.  And if you use them in your Access table, make sure to give a good description of the validation rule in terms a user will understand in the "Validation Text" field of your table rules.  (e.g. "First three positions of this field must be Number + Alpha + Alpha")


    As far as a quick and easy way to validate every field in your table via programming?  Not sure there is one.  I'd first go through your table and clean up all your data, next set up validation rules on the table, then test your program thoroughly inserting appropriate error messages and cleaning up any code that allows the validation rules to reject a record.


    Hope this helps.


    -Brian Jasmer





    Thursday, May 1, 2008 8:39 PM