SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Location
    Outside Providence
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a record in my SQL database that includes a date (something like 2/15/2001). Now, the user needs to have the ability to remove the date (there was a mistake entering it into the wrong field). I can't figure out how to enter a null value into the field programmatically. Can anyone help?

    April
    TEAMWORK is the fuel that allows common people
    to produce uncommon results.

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    April, what kind of database are we talikng about? MySQL, MS SQL?? When should the NULL value be put in, when the record gets added or when the record is modified?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Location
    Outside Providence
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, let me clarify. I am using Sql Server 7.0 and the record starts off with a null date, but it's updated with a date (which works fine). However, when the user wants to reset the date back to blank (null), I can't figure out how to insert a null value when one already exists in the table. In other words, using an update statement (because there's already a record there), set thisdate=null??? I've tried everything I can think of.

    Thanks for your help!
    TEAMWORK is the fuel that allows common people
    to produce uncommon results.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about

    UPDATE thistable set datefield = '' WHERE id = idnumber?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Location
    Outside Providence
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep... doesn't work. The date is set to 01/01/1900. Very bizarre, eh?

    April
    TEAMWORK is the fuel that allows common people
    to produce uncommon results.

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like the field gets set to a date format automatically. I really wish Icould help you out more. Sorry
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure if this will work but try:

    UPDATE thistable set datefield = 'null' WHERE id = idnumber?

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Location
    Outside Providence
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My query:

    update jobplan actualstartdate to 'null' where pkey = 3337


    SQL Says:

    Line 1: Incorrect syntax near 'actualstartdate'

    And it doesn't work without the quotes either. I'm at a loss.

    Can't believe no one has run into this before...

    April
    TEAMWORK is the fuel that allows common people
    to produce uncommon results.

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by acox
    My query:

    update jobplan actualstartdate to 'null' where pkey = 3337


    SQL Says:

    Line 1: Incorrect syntax near 'actualstartdate'

    And it doesn't work without the quotes either. I'm at a loss.

    Can't believe no one has run into this before...

    April
    That query won't work at all, you're forgetting several aspects of SQL here.

    Code:
    update jobplan actualstartdate to 'null' where pkey = 3337
    should be:

    Code:
    UPDATE jobplan
       SET actualstartdate = NULL
     WHERE pkey = 3337

  10. #10
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Location
    Outside Providence
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is how I did it. The query doesn't work. It doesn't allow me to enter the command in that way. (I must have made a typo in my entry of the code on the site).

    Anyway, were you able to try this and get it to work in your version of Sql 7.0. Maybe it's a setting within the table or database that is preventing this from working. Any idea?
    TEAMWORK is the fuel that allows common people
    to produce uncommon results.

  11. #11
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, check the table schema. Perhaps actualstartdate has been defined as NOT NULL in which case it is not allowed to have a NULL value. Also, ensure that it is not part of the PRIMARY KEY.

  12. #12
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Location
    Outside Providence
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope. The date field allows nulls and is not part of a primary key. When I try to insert using the word NULL I get an error. When I use '' (open and close single quotes, I get the date of 1/1/1900.

    Does this work in yours?

    April
    TEAMWORK is the fuel that allows common people
    to produce uncommon results.

  13. #13
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Has the column been given a default value? I'm not in front of a windows machine so I can't test myself.

    OK, the only other thing I can think of is that the column was not defined to allow null (remember by default SQL Server sets the column to not null. Why? Because the ANSI SQL92 standard says the opposite and MS dosn't follow standards).
    Last edited by freakysid; Feb 16, 2001 at 00:14.

  14. #14
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works fine on Sybase which uses T-SQL like MS SQL server does. But, why not just have the application check for a Jan 1, 1900 date and treat it as a NULL value? Seems to be the easiest 'fix' rather than hunting down any further issues.


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
  •