CH Chee
|
| Posted: 02/18/2002, 7:59 PM |
|
Hi
System : Win2000/VFP Free Tables/IIS5
Previous Problem : Whenever I inserted/update a date field, I get a data type mismatch issue :
---Solution by CC Support :
The second way is to improve ToSQL() function in order to add 'Date' field type processing in order to surround date field with curl braces.
Create Custom common event under Site/Modules section. Obtain generated code and modify ToSQL() to get:
Function ToSQL(Value, sType)
Dim Param : Param = Value
if Param = "" then
ToSQL = "Null"
else
if sType = "Number" then
ToSQL = replace(CDbl(Param), ",", ".")
else if sType = "Date" then
ToSQL = "{^" & Param & "}"
else
ToSQL = "'" & Replace(Param, "'", "''") & "'"
end if
end if
end if
end function
---Solution by CC Support :
I did just that and the insert was OK except that the date fields were updated
with nothing and when I redisplay the newly inserted record, a date of say 7/29/2001 was displayed as 12:00AM. On checking to the DBF free table, the date field is empty ie " / / "
What am I doing wrong ?
Please assist
|
|
|
 |
Nicole
|
| Posted: 02/19/2002, 5:36 AM |
|
Hello,
while you get empty value in date fields it means that date most probably was in incorrect format (different to one that used in your db). Check it. May be date should be converted to proper db format before inserting to db.
|
|
|
|
CH Chee
|
| Posted: 02/19/2002, 9:36 AM |
|
Hi Nicole
You are right, the empty date fields are clearly caused by incorrect date format.
Checking to :
http://msdn.microsoft.com/library/default.asp?url=/libr...field_types.asp
the strict date types for VFP is AMERICAN format ie {^yyyy/mm/dd}
This is the result of my testing :
1. System date format - yyyy-mm-dd
2. I enter 2001/03/31 into a date field
Result : OK
3. When I redisplay for update the same date field, I get
03/31/2001 ie mm/dd/yyyy (within CC)
Note : I do not understand how it can be displayed as such when my system
date setting is yyyy-mm-dd
4. I amended the displayed 03/31/2001 as 2001/03/2001
Result : OK
5. Repeating Step 5, I did not do any amendment to the 03/31/2001 and press
update, I get the 12:00AM at the next re-display.
How can I force format to 'yyyy/mm/dd' irregardless of what the user keys in ?
|
|
|
|
CH Chee
|
| Posted: 02/19/2002, 6:33 PM |
|
Hi Nicole
CC support has referred me to Elena's article on date formating but
I'm still facing a type mismatch issue after attempting to use her solution.
The article by Elena is as follows :
------- Article by Elena---------
To achieve such results I've created custom function in Global Functions event under Modules section: my_date_format(old_date, format). The first parameter is date value, the second one is format number. The built in formats are:
1 - dd/mm/yyyy
2 - mm/dd/yy
3 - dd-mm-yyyy
You may improve the function code and add your own date formats.
Function gets the passed date value, splits it into three parts and puts day, month and year values into separate variables. Then depending on format number the date in new format is built.
The function could be used to convert date into different format before displaying it on the form in BeforeShow event of Grid or Record form (as shown in sample project), before inserting field to db in BeforeInsert or BeforeUpdate events:
ASP
flddatetime_field1 = my_date_format(flddatetime_field1, 3)
PHP
$flddate_assigned1 = my_date_format($flddate_assigned1, 1);
And before search is implemented in Open event of result Grid form:
ASP
sWhere = replace(sWhere, GetParam("date_field"), my_date_format(GetParam("date_field"), 2))
PHP
$sWhere = str_replace(get_param("date_field"), my_date_format(get_param("date_field"), 2));
ASP+MSSQL, Access solution:
function my_date_format(old_date, format)
Dim Res: Res = ""
d = DatePart("d", old_date)
m = DatePart("m", old_date)
y = DatePart("yyyy", old_date)
if d<>"" and m<>"" and y<>"" then
if d < 10 then
d = "0" & d
end if
if m < 10 then
m = "0" & m
end if
select case format
case 1
new_date = d & "/" & m & "/"& y
case 2
new_date = m & "/" & d & "/"& y
case 3
new_date = d & "-" & m & "-"& y
end select
else
new_date = ""
end if
Res = new_date
my_date_format = Res
end function
------- Article by Elena---------
Since I needed yyyy/mm/dd, I amended the fUnction to read as follows :-
function my_date_format(old_date, format)
Dim Res: Res = ""
d = DatePart("d", old_date)
m = DatePart("m", old_date)
y = DatePart("yyyy", old_date)
if d<>"" and m<>"" and y<>"" then
if d < 10 then
d = "0" & d
end if
if m < 10 then
m = "0" & m
end if
select case format
case 1
new_date = d & "/" & m & "/"& y
case 2
new_date = m & "/" & d & "/"& y
case 3
new_date = d & "-" & m & "-"& y
case 4
new_date = y & "/" & m & "/"& d
end select
else
new_date = ""
end if
Res = new_date
my_date_format = Res
end function
what else did I do ?
- Set Field Type as Date
- Improved TOSQL() as per your earlier suggestion
- used the revised my_date_format(old_date, format)
Result :
========
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'my_date_format'
/crm4ks/EditcustdatRecord.asp, line 751
Any suggestions ?
|
|
|
|
|
