BlakeMcKenna
asked on
Storing a NULL value in a DateTime column in SQL Server?
I have a VB.Net Windows App that updates a SQL Server 2008 DB. In this particular table are several columns defined as "datetime". Is it possible to store a NULL value in a "datetime" column? If so, how?
Thanks!
Thanks!
Try using NOTHING (not NULL) in your code, or post the code snippet for more focused help.
Dim someDate As SqlDateTime
someDate = SqlDateTime.Null
someDate = SqlDateTime.Null
I'm assuming you want to update a value in the column with NULL rather than do an insert?
If an insert then simply don't include the date column in your INSERT statement and you will have your NULL value.
If an insert then simply don't include the date column in your INSERT statement and you will have your NULL value.
You should check to see if your table allows a NULL value in that column:
USE databasesname;
GO
EXEC sp_help 'tablename'
Check the 'nullable' column in the second of results to whichever column correlates to the DATETIME field you're trying to set to NULL. It could be that it does *not* allow NULL values.
Do like this
update tab1
set datecol=null
...
ASKER
I tried using this way
However, in VB.Net SqlDateTime.NULL does not exist...
Dim someDate As SqlDateTime
someDate = SqlDateTime.Null
However, in VB.Net SqlDateTime.NULL does not exist...
ASKER
Here is the code in question:
cmd.Parameters.AddWithValue("@calibrationDate", clsHE.calibrationDate = If(clsHE.calibrationDate.ToString = "#12:00:00 AM#", Nothing, clsHE.calibrationDate))
cmd.Parameters.AddWithValue("@nextCalibrationDate", clsHE.nextCalibrationDate = If(clsHE.nextCalibrationDate.ToString = "#12:00:00 AM#", Nothing, clsHE.nextCalibrationDate))
ASKER
Here is a screenshot...
Screenshot.jpg
Screenshot.jpg
Does this work for you?
Dim nullValue as system.dbnull
'[...]
cmd.Parameters.AddWithValue("@calibrationDate", clsHE.calibrationDate = If(clsHE.calibrationDate.ToString = "#12:00:00 AM#", nullValue, clsHE.calibrationDate))
cmd.Parameters.AddWithValue("@nextCalibrationDate", clsHE.nextCalibrationDate = If(clsHE.nextCalibrationDate.ToString = "#12:00:00 AM#", nullValue,clsHE.nextCalibrationDate))
ASKER
sirbounty...I tried that and it still didn't work. In my stored procedure, I have the variables setup like this:
Can the way these are defined be different?
@extensionDate DATETIME=NULL,
@calibrationDate DATETIME=NULL,
@nextCalibrationDate DATETIME=NULL,
Can the way these are defined be different?
You've defined default values for your stored procedure parameters. That's good. However, if you want that default value to be in effect, you would simply *not* provide them when you call your stored procedure (assuming you're using named parameters, which you are).
If you want these values to be NULL, then do *not* add these parameters when you call the stored procedure. I would think some IF() statements around the two cmd.Parameters.AddWithValu e() lines would solve your issue.
If you want these values to be NULL, then do *not* add these parameters when you call the stored procedure. I would think some IF() statements around the two cmd.Parameters.AddWithValu
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I figured it out myself...