[2005] Save DBNULL

[shakti5385]

Hi all
How to save dbnull in the sql server database

basically i have datetype column in table and on the form i have chacked dtpicker

if the dtpicker is chacked then it save date other wise it save DBNull

[shakti5385]

any idea
It Saving 1 Jan 1900 in the database when the dtpicker is not checked!!

[GaryMazzone]

I use the method discussed in this thread:
http://www.vbforums.com/showthread.p...79#post2881879

Just change the # to single qoutes ( ' )

[techgnome]

YEs... that's what databases do.... a bit odd that it's 1 Jan 1900 and not 31 Dec 1899 though.... but that's how NULL dates are stored in most databases.


-tg

[GaryMazzone]

Why? I can save NULL to a datetime field in a database with out any problems?

[apolizoi]

If you want to do it through VB.Net try this

MyDataTable.Rows(i)("YourDateFieldName") = DBNull.Value

If you want to do it through SQL try this :

YourDateFieldName= NULL

[bmahler]

If you use a parameterized query you can set the parameter value to DBNull.Value

This is my ExecuteQuery Function
it accepts Parameters in the form of @param,Value
If Value is empty it sets the parameter value to DBNull.Value

    Public Function ExecuteQuery(ByVal SQL As String, ByVal ParamArray Parameters As String()) As Boolean
        Dim con As New SqlClient.SqlConnection(ConnString)
        Dim oCmd As New SqlClient.SqlCommand(SQL, con)

        If Not Parameters Is Nothing Then
            Dim split As String()
            For Each p As String In Parameters
                Dim delimeter As Char() = {","c}
                split = p.Split(delimeter, 2)
                If split(1) = String.Empty Then
                    oCmd.Parameters.AddWithValue(split(0), DBNull.Value)
                Else
                    oCmd.Parameters.AddWithValue(split(0), split(1))
                End If
            Next
        End If
        Try
            con.Open()
            If oCmd.ExecuteNonQuery() > 0 Then
                ExecuteQuery = True
            Else
                ExecuteQuery = False
            End If
        Catch ex As Exception
            ErrorMessage = ex.Message
            ExecuteQuery = False
        Finally
            If Not oCmd Is Nothing Then
                oCmd.Dispose()
            End If
        End Try
        Return ExecuteQuery
    End Function 'Parameter Query

[penagate]

Of course, your column must be declared as nullable in the first place.

[techgnome]

I didn't say you COULDN'T save a NULL value.... in every database I've ever stuffed a NULL value into a DateTime field ends up being 12/31/1899 0:00:00.000

it's led to some problems during data extractions.

-tg

[GaryMazzone]

I don't seem to get that back. When I post a null to the database the field is null. On return I test for NULL and process accordingly.