Set date to null in SQL Server

All: I have 2 columns in my SQL Server database in which I need to be able to set dates to Null. For example, project actual end date. How can I pass a null date back to SQL Server. I have tried DBNull and system.dbnull, but get the error "DBNull is a type and cannot be used as an expression". I have exhausted my search across the internet - can anyone help me? Right now I am setting the dates to 1/1/1900 and then having to catch them when I read to set the web text boxes to "" when I display. Thanks!

Ali Ibarguen
BluePrint PM, LLC
blueprintpm.com

 

Had the same problem and finally found this solution somewhere on the web.

Use System.DBNull.Value

 

I had found that as well but it does not work either. I get the message: System.DBNull cannot be converted to Date. Is there something I could be missing? Here is my code that shows where I set the value to 1/1/1900 and would like to set to Null:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click



Dim comp As New CompanyDP, CommComp As New CommonComponent


Dim CompanyRow As Company.CompanyRow


CompanyRow = dsCompany.Company.NewCompanyRow()


'*** first check to see if Company name already exists. If it does


'** we cannot add


If CommComp.ValueExists("Company", "CompanyName", txtCompanyName.Text) Then


'**set label text to error


lblCompanyExists.Visible = True


Else


CompanyRow.CompanyName = txtCompanyName.Text


CompanyRow.CompanyStatus = txtStatus.Text


CompanyRow.Comments = txtComments.Text


CompanyRow.CreatedBy = Session("UserID")


CompanyRow.DateCreated = Now()


CompanyRow.DateUpdated = Now()


CompanyRow.Comments = txtComments.Text


CompanyRow.Profile = txtProfile.Text


CompanyRow.UpdatedBy = Session("UserID")


CompanyRow.WCAnalystID = ddlAnalyst.SelectedItem.Value


'*** set date to 1/1/1900 if blank


If txtDateK.Text = "" Then


CompanyRow.WCContractSigned = "1/1/1900"


Else


CompanyRow.WCContractSigned = txtDateK.Text


End If


If txtDateLOA.Text = "" Then


CompanyRow.WCLOASigned = "1/1/1900"


Else


CompanyRow.WCLOASigned = txtDateLOA.Text


End If


CompanyRow.WCRepID = ddlRep.SelectedItem.Value


comp.AddCompany(CompanyRow)


Response.Redirect("CompanyList.aspx")


End If


End Sub


Ali Ibarguen
BluePrint PM, LLC
blueprintpm.com

 

In data access code you can use System.DBNull.Value as in:
cmd.parameters.add('@FUDate', System.DbNull.value)

However, if Company.CompanyRow.WCContractSigned is defined as a DateTime, you cannot set it to System.DbNull.value.

So you are going to have to look at the point at which you are using the data from Company.Company row as the source for an SQL Server INSERT or UPDATE.

 

If datatype is DateTime in SqlServer,use System.DBNull.Value and make sure Allow Nulls is checked on field (through SQL Server Enterprise Manager).

 

The trick to inserting null date values into a sql column is to use 'Parameters' as opposed to directly trying to insert a string using the command line. Parameters also give the advantage of being able to insert quotes, commas (,) and apostrophes (') without the CLR throwing a wobbler.





Here is an example page of how to insert DBNull values into a SQL database that accepts null datetime entries. Note, it is in C# but there is little difference between C# / VB.NET now anyway bar for the odd keyword or two:


http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp