Date Insert problem

[SANCHIT SHARMA]

Dear friends,

I am landed to a troublesome area and need help. On my form as per requirement various Text boxes are there to capture Date information. It is not mandatory that all the time all date columns will be assigned value.This date information is conditional.

while inserting the infomation through Insert command, it is difficult to send date as null, if wesend as nothing it automatically picks some starting date. But we need null there instead of some fake value.

If we introduce any dummy date for that , in search options, possibility is very hogh to get wrong information , and it can put the entire effort in jeoparady.


Pls Help with some genuin way to overcome this situation

 

[Arg81]

If I understand what you are saying, you mean that when you open your form, all fields that have dates show the current date?
This is a default of the Windows DateTimePicker...

One solution (which is what I did) is to have a textbox bound to the date field in the database, and the dateTimePicker not bound. So this textbox will show any date coming from the DB.
If the database value is null, then the textbox will be blank.
I then created a small label with "Not Set!!" next to each datefield. I then added the code on the form startup:

me.dtpDateRequested.text = me.txtDateRequested.text 'sets the datetimepicker to the date on the text box bound to the database if there is a date to show, else it will obviously show the current date
If me.txtDateRequested.text = "" Then
me.lblRequestNotSet.visible = True
Else
me.lblRequestNotSet.visible = False

I then placed the textbox onto the dateTimePicker and set it so it was "sent to back", so it was basically hidden from the user.

I did this for all date fields, so the user was aware which dates have come out of the database, and which dates haven't been set.

Now, if you are talking about when you insert a row to the database, and you want the value to be null, then set some code up for your dateTimePicker's ValueChanged event.
To do this, simply double click the DTP on your form to go straight to the code.
Here add:

me.txtDateRequested.text = me.dtpDateRequested.text


Then, on any change to the date on the dateTimePicker, it will set that date to the textbox, which is then bound to your date field in the database.
If the user doesn't change the value on the dateTimePicker, then the textbox stays blank, so a null value goes to your database.

I've used DateRequested as an example - you would obviously change the code to the names of your components.

Hope that helps...
Luke