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.

 

[kulrom]

1st check that certain DB field allows NULL/NOTHING and then when it's enabled you can insert the empty string

 

[dpatfield66]

The sql datetime field is set to nullable and will take an empty string...I can hit ctrl 0 and <NULL> will show up in the field.

But for this Insert() Method, it's not taking the ""
I changed the paramater to String instead of date, and when it does all it's stuff behind the scenes, now I get 1/1/1900 instead of Null.

I forgot to mention that I changed the paramater argument to a String Value instead of a DateTime. But even then, I get the above.

I was able to alter the insert method (deeeeeep in code) to make the field NULL when the data row object, or whatever the *&% it was, was equal to "", but that code has wiped out again and been overridden with the original code. I assume whenever I make any changes to the formsDataSet.xsd in design view, but I'm not sure.

 

[kulrom]

then make a two QUERIES and use 'em respectively ... one when txtADate.Text is an empty string and another one when it is correct date value. At the 1st Query avoid inserting of certain field i.e.

if txtADate.Text = String.Empty then 
query1()
Else
query2()
End if

Ah ... and leave the parameter argument as dateTime

 

[dpatfield66]

I ALMOST got excited that this would work, but I've got like 20 different date fields.

 

[kulrom]

Just copy/paste entire code portion and remove the parameter for the date inside new procedure. Also, notice that you are not affect the performance cuz you still call only ONE query depending on result from the If statement.

 

[dpatfield66]

but I've got 20 different Dates, each with their own paramaters. You're not suggesting that I have one query (or insert method) for EACH date situation? What if 2 are empty? Depending on each one, I would have to code for each date right?

I would have to code upteem different queries for each possible scenario, right? AdmitDate is empty only...that's one query. AdmitDate and BirthDate are both empty, there's another one...Birthdate only empty ...another one....and so on...

 

[kulrom]

wow i would never use your application ... it is very very odd when an user has to fill soooo much date fields (it is simply annoying). Also if you think that certain fields are that much optional just like in your case then i would suggest to remove them (at least i would do that - remove not needed fields). Either prompt the users to fill each field or you must remove those fields cuz they will become useless (trust me noone will enter 20 dates ... i assure you)

 

[dpatfield66]

There isn't any code actually other than the insert() method itself, which is laden with parameters

Ex:

Insert(@Date1 as string,@Date2 as string, etc...)

Not to mention the OTHER fields that are going over.
Just an FYI, it used to be:

Insert(@Date1 as date, @Date2 as date, etc...) but that wasn't working.

I suppose I could initially Insert without any date parameters, and then immediately afterwards, UPDATE with a loop through each date.
--------------------------------------------
If txtDate <> "" then

Update(problem right here...update what?? Not the control? I need the sql field, but how would it know which field to update?)

End if

 

[dpatfield66]

In response to sooooo many date field, unfortunately it's a clinical prenatal form where there are onset dates, birth dates, gestational dates, and so on.

I WILL check with my boss, though, to see if I can force an entry into each date field, but I don't think so, because sometime a nurse will validate(save) the form entry as incomplete and come back to it to fill in dates.

 

[dpatfield66]

I can't help but think that there's an easy solution to this.

Because I don't understand all the behind-the-scenes stuff that goes into an insert method, I can't figure out the arguments and datatypes and all that. I feel like this one is right in front of my face, but I can't put my finger on it.

All that needs to happen is: Make the date field in sql NULL when the text field in my forms are empty.

The silly thing DID that when I hardcoded into the insert method, but it got wiped out...grrrr!

 

[kulrom]

Ok then ... it's up to you (i was only giving my suggestion) actually you can contact me via yahoo IM (do not hesitate) ???

 

[DavidT_macktool]

Ok, here is my suggestion. StringBuilder.
It sounds like you are going to need a series of 'IF' statements to check and see if a date is entered. While performing the 'IFs' you should be building your insert string. After you have checked all the dates your insert string will contain reference to only the dates that are present. Rather than trying to insert a NULL you would just exclude that date from the Insert string and effectively insert nothing/null.
Clear the string and you can start over for the next record.

 

[dpatfield66]

This sounds like what I'm going to try. I'm looking into this one now.

 

[jimbo1969]

try something like this instead, see how it works for ya:

dim objDate as Object
dim sqldatenull as SqlTypes.SqlDateTime

sqldatenull = SqlTypes.SqlDateTime.Null

If Me.txtADate.Text = "" Then
objDate = sqldatenull
Else
objDate = Me.txtADate.Text
End If

Hope that helps...