Hi all :wave:
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
Printable View
Hi all :wave:
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
any idea
It Saving 1 Jan 1900 in the database when the dtpicker is not checked!!
I use the method discussed in this thread:
http://www.vbforums.com/showthread.p...79#post2881879
Just change the # to single qoutes ( ' )
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
Why? I can save NULL to a datetime field in a database with out any problems?
If you want to do it through VB.Net try this
If you want to do it through SQL try this :Code:
MyDataTable.Rows(i)("YourDateFieldName") = DBNull.Value
YourDateFieldName= NULL
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
Code: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
Of course, your column must be declared as nullable in the first place.
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
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.