passing null date value to database

how can i pass null value to database? date is not required field in my database. i can pass default date but i think default date is not good in my case as it is DOB of a customer.Hi there,

Check out the DBNull class. :)|||DBNull.Value should do the trick ...
But don't forget when reading back from the table to check for the NULL value|||well, i tried that it says can't convert System.DBNull to datetime. i also used Convert.DBNull but it shows error at run time if the value isn't provided. well, it seems like i have to pass DBNull.Value directly but in my case i have to assign it to variable coz i am using stored procedure and using SqlHelper class.

what about integers though? is it the same thing?|||I think you should be able to use System.Data.SqlTypes.SqlDateTime.Null.

Terri|||hi terri,
it still says "value of type System.Data.SqlTypes.SqlDateTime.Null cannot be converted to 'Date'"|||Can you please post the relevant code?

Terri|||

 Dim commDate As DateTime
 If tbCommDate.Text = "" Then
 'commDate = New DateTime(1900, 1, 1)
 'can't use this statement
 commDate = System.Data.SqlTypes.SqlDateTime.Null
 Else
 commDate = CType(tbCommDate.Text, DateTime)
 End If

I pass this "commDate" to the stored procedure. i am using SQLHelper class.|||We need to also see the code where you are telling the SQLHelper class the data type of the commDate parameter.

Terri|||

Public Shared Function ProspectiveInsert(ByVal agentId As Integer, ByVal tripId As Integer, ByVal institution As String, ByVal nationality As String, ByVal title As String, ByVal dob As DateTime, ByVal sex As String, ByVal elicos As String, ByVal course1 As String, ByVal course2 As String, ByVal fname As String, ByVal mname As String, ByVal lname As String, ByVal osStud As Boolean, ByVal osAdd As String, ByVal osPhone As String, ByVal osMobile As String, ByVal osFax As String, ByVal osSuburb As String, ByVal osPostcode As String, ByVal osCity As String, ByVal osCountry As String, ByVal add As String, ByVal phone As String, ByVal mobile As String, ByVal fax As String, ByVal suburb As String, ByVal postcode As String, ByVal city As String, ByVal country As String, ByVal email As String, ByVal ugQual As String, ByVal ugIns As String, ByVal ugYear As String, ByVal seconQual As String, ByVal seconYear As Integer, ByVal seconIns As String, ByVal pgQual As String, ByVal pgIns As String, ByVal pgYear As Integer, ByVal appStatus As String, ByVal appNotes As String, ByVal appDate As String, ByVal ielts As Double, ByVal engRemarks As String, ByVal coeDate As DateTime, ByVal coeRemarks As String, ByVal offerDate As DateTime, ByVal offerRemarks As String, ByVal recAddDate As DateTime, ByVal prefCommDate As DateTime, ByVal notes As String, ByVal modBy As String) As Integer
 Try
 SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("connectionString"), "MCS_ProspectiveStudentInsert", agentId, tripId, institution, nationality, title, dob, sex, elicos, course1, course2, fname, mname, lname, osStud, osAdd, osPhone, osMobile, osFax, osSuburb, osPostcode, osCity, osCountry, add, phone, mobile, fax, suburb, postcode, city, country, email, ugQual, ugIns, ugYear, seconQual, seconIns, seconYear, pgQual, pgIns, pgYear, appStatus, appNotes, appDate, ielts, engRemarks, coeDate, coeRemarks, offerDate, offerRemarks, recAddDate, prefCommDate, notes, modBy)
 Catch ex As SqlException
 Return ex.Number
 End Try
 Return 0
 End Function

This is the function where I have passed the dates but i can't assign the System.Data.SqlTypes.Null to date variable.|||OK, I believe I have misled you. You should be using System.DBNull. Let us know if this works better for you.

Terri|||it says "DBNull is a type in system and can't be used as an expression."

at the moment i am using default value but i think that's not a good idea is it?|||thanx a lot for your time Terri.
i appreciate your willingness to help me.|||THe problem has NOTHING to do with SQL. See:

[code]
Dim commDate As DateTime

If tbCommDate.Text = "" Then

'commDate = New DateTime(1900, 1, 1)

'can't use this statement

commDate = System.Data.SqlTypes.SqlDateTime.Null
[/code]

NATURALLY blows. You can not assign SqlDateTime.Null to a DateTime.

You have to make the assignment "lower" - i.e. you pass the SqlDateTime directly into the relevant SQL parameter. You can not store it in another non-compatible time in the meantime.

For example, the EntityBroker - my O/R-Mapper - makes the switch statement directly when putting the value into the PARAMETER (or encoding the value for the SQL string). THEN it gets accepted (DBNull.Value, btw.). The way you do it earlier just results in an invalid cast.|||but i am using SqlHelper class. so i think i can't use it then. right?