Update DateTime field with null value

Results 1 to 2 of 2

Thread: Update DateTime field with null value

  1. #1
    Join Date
    Dec 1969

    Default Update DateTime field with null value

    On a form I have several date-field text boxes that the user may not fill in (not required). If I do a stored_proc &#039;UPDATE table SET datefld=@txtDatefld.text WHERE id=@id&#039;, I get a &#039;Cast from String (&#039;&#039;) to Date is not Valid&#039; when the field is left blank. <BR>What VB magic do I need to do to txtDatefld.text to get it to save a a blank date?

  2. #2
    Join Date
    Dec 1969

    Default Must use SQL keyword NULL

    How you do that is up to you, but you can NOT present a string value of any kind.<BR><BR>In other words, if this were not a stored proc, it would be the difference between<BR><BR>UPDATE table SET datefld = &#039;12/19/2001&#039; ...<BR>and<BR>UPDATE table SET datefld = NULL ...<BR><BR>Notice not &#039;...&#039; around the keyword! It truly *is* a part of the SQL language and is not a variable or value, per se.<BR><BR>You *should* be able to build a conditional expression in there.<BR><BR>In Access, you&#039;d do something like:<BR><BR>UPDATE table SET datefld = IIF(@arg = &#039;&#039;,NULL,@arg) ...<BR><BR>I&#039;m not a SQL Server user, but I think it would be something along the lines of an in-line IF or inline switch/select?<BR><BR>

Posting Permissions

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