Date/Time SQLServer and Null

[cyberbiker]

Before I go off with some goofy code to fix this, perhaps someone knows a better way.

I have a a fair number of date time fields (smalldatetime data type) in my SQL Server database. Null values are permitted.

On one form in my app I have 2 bound text boxes that are assigned the value of the Now() function when a check box is checked.

When I uncheck the check box I clear the value in the text box creating an empty string which (of course) causes the update to fail.

Requiring a date or time value for each record is not an option which is why Nulls are permitted.
Due to the nature of the app it is also very likely that these 2 fields will need returned to a null state.

Any ideas will be appreciated





Terry (cyberbiker)

 

[vb5prgrmr]

Check to see if the value exists and if not exclude it from your update/insert statement.

Good Luck

 

[cyberbiker]

I appreciate your answer very much. I think you confirmed what I was thinking.

I may not have been totally complete. A valid date/time may have been already saved to the database. Then later the date/time is deleted and I need to return the
value of the field to Null.


What I am thinking (and your answer seems to confirm this) Is that I should remove binding from at least the date/time fields and handle them manually.

something like:
begin Transaction
if isnull(Textbox.text) or textbox.text = "" then
db.execute "update table set datefield = null"
else
dbexecute "update table set datefield = " & textbox.text
end if
RS.Update
commit transaction





Terry (cyberbiker)

 

[cyberbiker]

Opps Order is reversed. RS.Update comes first

begin Transaction
rs.update
if isnull(Textbox.text) or textbox.text = "" then
db.execute "update table set datefield = null"
else
dbexecute "update table set datefield = " & textbox.text
end if
commit transaction


Terry (cyberbiker)

 

[LostInCode]

This is useless and can be removed:
>isnull(Textbox.text)


Try doing this:
Dim strValue As String

strValue = textbox.text
If Not IsDate(strValue) then
strValue = "Null"
Else
strValue = Format$(strValue, "'yyyy-mm-dd'&quot
End If

db.execute "update table set datefield = " & strValue

 

[cyberbiker]

Thank you LostInCode

When I first started programming VB I was forced to check for IsNull and empty string in text boxes by a "Brilliant" Programmer whom the owners felt walked on H2O.

It became a very bad habit of mine that I need to break now that I no longer work there. I thank you for reminding me that a vbtext box cannot contain a null value.

In my actual app, I do use a variable to hold the date value.

I used the actual value (and left off the where clause in the update statement) in an attempt to be more clear.

Terry (cyberbiker)

 

[LostInCode]

>In my actual app, I do use a variable to hold the date value.

You still need to format the date prior to updating the DB field.
The user could change their region settings for dates and then you will get wrong information into/from the DB.

If the user changes the date settings to dd/mm/yy and you take the user's input and put it into a date variable (or even just a string var) and pass this to the sql statement which expects a date in US format then this could happen:

User input: 06/01/2004 = 6 Jan 2004 (and not 1 June 2004).
But the dbms sees 1 June 2004 and returns/sets record fields to this value.

 

[cyberbiker]

Good point. Could you clear something up for me then that I may be confused on?

I set the format property at design time to date and selected the appropriate format.

I have been rereading the help menu (and experimenting) and think I have been mistaken on what I have read (this is actually my first app that could be used Outside the US)

I understood the format property operates the same as the format function and is locale aware.

However I think I overlooked a point

I set the format of the control using the property page (which is "by example&quot and think I need to change that since I now suspect that my format would be M/d/yyyy and not "short date"

Your suggestion (I think) could be changed to

strValue = strValue = Format$(strValue, "short date&quot
making the value locale aware.

Regardless, I think you may have saved me from quite a few hours looking for a bug. I need to experiment a bit further and be certain












Terry (cyberbiker)

 

[LostInCode]

The Date variable and format IS local aware for the client machine, and that is what I was saying (or trying to).

The problem is, the criteria passed to the dbms's query processor may NOT be local aware, and usually isn't.
It understands dates only in US or ISO format.

And this is correct so, because 5 workstations could be using the same server but each having it's region settings set differently, unless the server can identify the workstation and user settings and is set to interpet each individually. This usually only results in confusion and a big mess.

Therefore, you NEED to pass the dates to the dbms in US/ISO format, and the only way to do that, when the date is in a date variable, or entered by the user in some local non-US format, is to force the format as I have already shown (yyyy-mm-dd) or Format$(myDate, "mm\/dd\/yyyy&quot

Doing this:
Format$(strValue, "short date&quot
or this:
Format$(strValue, "mm/dd/yyyy&quot will not produce the correct date format if the local date format is not in US format. Worse yet, the date seperator could be a Dot.
This goes also for numbers and currency formats (mainly the decimal separator)

Try it. Change your region date format settings to DD/MM/YYYY and see what is meant.

Add two records with a text and a date field set the Text/dates to:
Record1: Field1=JUNE Field2=1 June 2004
Record1: Field1=JAN Field2=6 Jan 2004

and then run a Select Statement from VB with using this date in a date variable as criteria:
dtMyDateVar = CDate(#01/06/2004#)
SELECT * FROM MyTable WHERE myDateFld = '" & Format$(dtMyDateVar,"mm/dd\/yyyy&quot & "'"

Even though the date entered was & January, guess what? You get the one from 1 June.

Change the format of the date to what I mentioned and then run the query.
SELECT * FROM MyTable WHERE myDateFld = '" & Format$(dtMyDateVar,"mm\/dd\/yyyy&quot & "'"

Now it finds the correct record.

 

[cyberbiker]

Thank you very much.You cleared up a couple of points for me.

Although it is safe to assume that the app I am working on will never be sold outside the US or Canada, it would be quite possible for somebody the have their local settings set other than for the US.

I need to reevaluate my basic design as it pertains to date and times.





Terry (cyberbiker)