SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    allow zero length strings in this field

    One of the options access gives you when defining a field is "allow zero length strings in this field"

    What are the ramifications of my choice here?

    I was thinking that if I say no, then I would prevent the value of a blank text field such as request.form("text1") where the user submitted the form without filling in the field, from getting inputed into the db. This way, unpopulated fields would have a value of null instead of "".

    Knowing that this is the case, I will be able to filter by WHERE IS null or WHERE NOT null. Also, I would have to be careful not to write a script that enters an empty string into that field since this will cause an error.

    Are these the main issues here? How important are they? Are there other issues I need to be aware of when making this choice?

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Allowing zero length strings simply gives you the option to include/not include the field in your insert and update statements. If zero length is allowed, then you may ignore the fields at will. If zero length is NOT allowed, then you must provide SOME value for the field.

    It's a matter of preference. If I know something is required, then I don't allow zero length strings. If it's going to be optional, then I do.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    So what's the difference between yes for "allow zero length strings" and choosing no for "required" or vice versa?

  4. #4
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    an empty string is not the same as a null. lets take a look at some code that will make things clear to you...

    Code:
    INSERT INTO TableName
      (PKCol, StringCol)
    Values
      (1, '')
    this statement requires that the allow zero length constraint on the 'StringCol' column be true. however, the allow nulls constraint can be set to either true or false.

    if you wanted to insert a null value into that column instead of an empty string you could do either of the following:

    Code:
    INSERT INTO TableName
      (PKCol, StringCol)
    Values
      (2, null)
    
    INSERT INTO TableName
      (PKCol)
    Values
      (3)
    of course, to do this the 'StringCol' column must be set up to allow nulls.

    hope this clears things up for you. just remember that empty strings are not the same as nulls. in other words, the two following queries would return different results:

    Code:
    SELECT *
    FROM   TableName
    WHERE  StringCol IS NULL
    Code:
    SELECT *
    FROM   TableName
    WHERE  StringCol = ''

  5. #5
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks DarkEye and Dave,

    Really my question is about access as much as about nulls and empty strings.

    the way I'm understanding it now is that if I choose no for the "required" property and no for the "allow zero length string" then I can place a value of null in the field but not a string value of "".

    I figure this would be a good thing to do in the parentAddressID field in the following table;

    Code:
    addresses
    ---------
    addressID
    userID
    recipientName
    street
    city
    parentAddressID
    To filter for principal addresses in this table I can use WHERE parentAddressID IS NULL.

    This make sense?
    Last edited by akohl; Sep 15, 2002 at 00:21.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •