Null Values

[dpatfield66]

I've seen some postings on Null Values, but they don't seem to help me.

How can I pass a NULL value to an SQL Table Date Field, if I'm using a Masked Text Box to store the value in the forms?

In other words, if the text of this box is "", how can I get the Insert method to accept as an argument (DBNull)?

Remember, my paramater wants a DateValue. If the string that comes from the Masked Text Box is a valid date, then the parameter will work, but if it's "", the error is: "Cannot convert "" to Date" In this case I just want make the paramater NULL, but DBNull will not be accepted in the paramater. I cannot set any variables to NULL, except I believe the OBJECT type, and when I do that, I get a cast error on the paramater.

Ex:

dim objDate as Object
If Me.txtADate.Text = "" Then
objDate = System.DBNull
Else
objDate = Me.txtADate.Text
End If

But when I say obixAdmitTableAdapter.Insert(objDate), I get the cast error. Because the argument wants a date value.

There is some behind-the-scenes code that I've updated for the Insert Method but everytime I come back at a later date, the code has disappeared and the original code is there. I tried saving and then closing the app, and then re-opening, and the code is there, but just today, for some reason it's wiped out again. Not sure what I do that erases it.

 

[dpatfield66]

Thanks for the reply.
I tried this and still got 1/1/1900 in my date field in SQL table.

 

[TechGnome]

Time simply cannot not exist... it has to be "Something"....
If you insert a Null value into a DateTime field in SQL Server, it actualy inserts 12/31/1899 00:00:00.000 in to it. This is the equivelent to -1 in time. If you insert "" into it, then that translates into 0, which becomes 1/1/1900 00:00:00.000 in the table.

There's not much you can do about that. But unless you are dealing with data that can legititmately have those values, you can work around it in your code/queries. It's sucky, I know but it has to do with the way it's stored in the DB, and I don't see that changing any time soon.

-tg

 

[jimbo1969]

Here is another posting about a similar issue. This was helpful for us, hope it helps:

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

I found it better in a SP vs in vb.net anyway.

 

[cjard]

erm, are you sure youyre using the right nulls?

i mean, did you try setting it to System.Data.SqlTypes.SqlDateTime.Null ?

Or did you try calling SetXXXNull on the row?

i.e. myNewDataRow.SetBIRTH_DATENull()


for more info just press F2 and type the word NULL into the search box.. hit go, and if your project is anything like mine, you'll get hundreds of results for methods and properties like IsXXXNull and SetXXXNull

 

[DavidT_macktool]

ComponentOne C1DateEdit control allows you to set it to null.
If it is bound to a field in a dataset 'null' will be written to the Database, in my case SQL Server2000.

C1DateEdit2.Value = DBNull.Value

I have used this to clear out a ClosingDate from a Dataset when we reopen a job.
ComponentOne Date edit control is the only one I have found to behave like this.

 

[cjard]

http://www.codeproject.com/cs/miscctrl/Nullable_DateTimePicker.asp ?

extension to the standard control that basically allows Null as a minvalue - should be fully compatible with MS's control.. but the thing is.. i want to know if you set such a DTP to null,and then want to pick a value.. where does it start from? if it starts from 1899 or some such low year thats a lot of scrolling to get to today..

as noted before, the standard DTP allows a checkbox too, so a simple:

If myDTP.Checked Then
  myNewDataRow.MY_DATE = myDTP.Value
Else
  myNewDataRow.SetMY_DATENull()
Endif

 

[RTaulbee]

I just joined this forum today and it's funny to read about someone having the same 'issue' that I've been dealing with over the last few days.

I did this:

If String.IsNullOrEmpty(txtNotificationDate.Text) Then
            NewDealRow.DealNotificationDate = System.Data.SqlTypes.SqlDateTime.Null
        Else
            NewDealRow.DealNotificationDate = txtNotificationDate.Text
End If

That will insert a #1/1/1900# in the table (unfortunately). So on an output (such as a report) I do this in the SQL call:

 CASE TicketAllocationDate WHEN '1/1/1900' THEN NULL ELSE TicketAllocationDate

on each Date output so the report doesn't show #1/1/1900#

Of course, if the date is showing on a form, same thing.

If Value = #1/1/1900# then
     txtbox.text = string.empty
end if

It's a nuisance...especially when SQL will allow you to put a null in a date time field field using Ctrl-0 anyway.

 

[RTaulbee]

erm, are you sure youyre using the right nulls?

i mean, did you try setting it to System.Data.SqlTypes.SqlDateTime.Null ?

Or did you try calling SetXXXNull on the row?

i.e. myNewDataRow.SetBIRTH_DATENull()


for more info just press F2 and type the word NULL into the search box.. hit go, and if your project is anything like mine, you'll get hundreds of results for methods and properties like IsXXXNull and SetXXXNull

reading back through the thread, This reply just made me very happy. I changed the code to set the datatable.field to null if the user didnt input a date and the adapter inserts an actual NULL into the date field on SQL using this

 NewRow.SetFacilityMaturityDateNull()

. Hallelujah.

Thanks cjard.

 

[dpatfield66]

Ok, I'm reading through all this, and I'd like to sort it out.

Here's what's happening.
I'm using a TableAdapter.InsertMain() method, where all the paramaters are inside the ().

Everywhere there's a date field in question that I want to transfer over to SQL, I've placed a function in the ().

Ex:
FormatDateMasks(txtAMDOB, False) Where txtAMDOB is the masked textbox mask = 00/00/00. And False is distinguishing the maskedtextbox as a date versus a time mask = 00:00.

In either case, the function looks like this:

[COLOR=#0000ff]Function[/COLOR] FormatDateMasks([COLOR=#0000ff]ByVal[/COLOR] objControl [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Object[/COLOR], [COLOR=#0000ff]ByVal[/COLOR] blnTime [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Boolean[/COLOR]) [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String
[/COLOR][COLOR=#0000ff]Dim[/COLOR] objDate [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Date
[/COLOR][COLOR=#008000]'Dim sqldatenull As SqlTypes.SqlDateTime
[/COLOR][COLOR=#0000ff]If[/COLOR] objControl.Text = [COLOR=#800000]" / /"[/COLOR] [COLOR=#0000ff]Or[/COLOR] objControl.Text = [COLOR=#800000]" :"[/COLOR] [COLOR=#0000ff]Or[/COLOR] objControl.Text = [COLOR=#800000]""[/COLOR] [COLOR=#0000ff]Or[/COLOR] IsDBNull(objControl.Text) [COLOR=#0000ff]Then
[/COLOR]objDate = [COLOR=#800000]"1/1/1900"
[/COLOR][COLOR=#0000ff]Else
[/COLOR][COLOR=#0000ff]If[/COLOR] blnTime = [COLOR=#0000ff]True[/COLOR] [COLOR=#0000ff]Then
[/COLOR]objDate = [COLOR=#800000]"1/1/1900 "[/COLOR] & objControl.Text
[COLOR=#0000ff]Else
[/COLOR]objDate = objControl.Text
[COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]If
[/COLOR][COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]If
[/COLOR][COLOR=#0000ff]Return[/COLOR] objDate

Ok, if I DON'T set the bad stuff to 1/1/1900, I get issues with the times where the data is not between a specified date range, and is considered overflow...that's ONE issue.

I also tried to use your example and set objDate to SqlDateTime, or whatever it was you said to do, and that didn't work...still got 1/1/1900.

Also, please remember that the InsertMain() method is doing some stuff behind the scenes...I would have LOVED to edit there, because I was able to get the date value to be null, under certain conditions, but for some reason, if I do anything with the formsdataset.xsd (modify a query, create a new one, etc...), the code in the InsertMain() goes back to its default.

The REAL transformation is occuring with some paramaters set by the InsertMain() method...I'll give you one example each of a date and a time:

[COLOR=#0000ff][COLOR=#0000ff]If[/COLOR] (AAdmitDate.HasValue = [COLOR=#0000ff]true[/COLOR]) [COLOR=#0000ff]Then
[/COLOR]command.Parameters(95).Value = [COLOR=#0000ff]CType[/COLOR](AAdmitDate.Value,[COLOR=#0000ff]Date[/COLOR])
[COLOR=#0000ff]Else
[/COLOR]command.Parameters(95).Value = System.DBNull.Value
[COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]If[/COLOR]
[COLOR=#0000ff]
[/COLOR][COLOR=#0000ff]If[/COLOR] (AAdmitTime.HasValue = [COLOR=#0000ff]true[/COLOR]) [COLOR=#0000ff]Then
[/COLOR]command.Parameters(96).Value = [COLOR=#0000ff]CType[/COLOR](AAdmitTime.Value,[COLOR=#0000ff]Date[/COLOR])
[COLOR=#0000ff]Else
[/COLOR]command.Parameters(96).Value = System.DBNull.Value
[COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]If
================================================
[/COLOR][/COLOR][COLOR=black]

[/COLOR]
I would LOVE to edit this stuff, because all I had to do was say, if AAdmitTime = "", then command.Parameter(96).Value = System.DBNullValue

But as I mentioned above, this always seems to default back to what you see. arrrrrrrggh!

So, bearing in mind the stuff you wrote about, and these command.Parameter values, is there something I can do in my FormatDateMask function to get this working?

<SORRY FOR THE LONG WRITEUP>

 

[dpatfield66]

I'm not sure how the

NewRow.##### can be fit into my InsertMain() statement.
Any thoughts? Please read my previous email above this one.

 

[RTaulbee]

MSN instant message me at "MaestroSC" It will be good to have some fellow programmers to throw ideas around with and maybe we can get your problem solved by chatting.

 

[dpatfield66]

Another issue I'm getting when I use:

Function FormatDateMasks(ByVal objControl As Object, ByVal blnTime As Boolean) As Object
Dim objDate As Object
Dim sqldatenull As SqlTypes.SqlDateTime
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null

If objControl.Text = " / /" Or objControl.Text = " :" Or objControl.Text = "" Or IsDBNull(objControl.Text) Then
objDate = sqldatenull
Else
objDate = objControl.Text
End If
'End If
Return objDate
==========================================

This function returns a Date, and If I set objDate as Date, I still get the 1/1/1900.

If I set objDate as an Object, and change the function to return an object, I get a Cast Type error, because the paramater is looking for a date.

 

[dpatfield66]

<<<Error 1 Value of type 'System.Data.SqlTypes.SqlDateTime' cannot be converted to 'System.Nullable(Of Date)'. >>>

This is what I get when I try to set the function to SQLTypes.SqlDateTime, instead of Date.

I'm getting incompatible types. Apparantly my parameters are looking for System.Nullabel(Of Date) types, and the sql thing isn't working.

This is frustrating.

 

[dpatfield66]

I've got MaestroSC in my MSN, but it's offline.
I'll wait for you to sign on, when you're available.

 

[dpatfield66]

Ok, until I can figure out this issue, I've created a temp updatequery that takes any of the dates in SQL that read 1/1/1900 and puts them back to NULL.

Ex: UPDATE [dbo].[OBAdmit]
SET MDOB = Replace(MDOB,'1/1/1900',NULL), and so on...

It works fine.

Now here's the catch...how can I do this for my times?

2 situations... when the time is null, I can do like above, but when it's got a time, I get 1/1/1900 2:00:00 PM for example.

How can I JUST get rid of the 1/1/1900 (in SQL!!!)? I already know how to get rid of it in my .NET forms and reporting, but I'd really like to get it out of the SQL table.

Remember, I need to get rid of 1/1/1900 when the time is null an only shows 1/1/1900 AND I need to just get rid of the 1/1/1900 when there's a valid time in there.