VFP 愛用者社區

[Darren]

請教各位高手

我用的是 vfp 9.0 sp2 和 mysql 5.0.37

我在mysql里建立了一個 table, 如下

CREATE TABLE `policy` (
`policy_id` int(10) unsigned NOT NULL auto_increment,
`ref_no` varchar(10) NOT NULL default '',
`effective_date` date NOT NULL default '0000-00-00',
`expire_date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`policy_id`),
KEY `idx_ref_no` (`ref_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

然後, 我在vfp的view建立了一個remote view ( 名字是view_policy ), 如下

SELECT Policy.policy_id, Policy.ref_no,;
Policy.effective_date, Policy.expire_date;
FROM ;
policy Policy;
WHERE Policy.ref_no = ( ?cRef_No );
ORDER BY Policy.ref_no

DBSetProp(ThisView,"View","SendUpdates",.F.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","ShareConnection",.F.)
DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","policy")
DBSetProp(ThisView,"View","WhereType",3)

DBSetProp(ThisView+".policy_id","Field","DataType","I")
DBSetProp(ThisView+".policy_id","Field","UpdateName","policy.policy_id")
DBSetProp(ThisView+".policy_id","Field","KeyField",.T.)
DBSetProp(ThisView+".policy_id","Field","Updatable",.F.)

DBSetProp(ThisView+".ref_no","Field","DataType","C(10)")
DBSetProp(ThisView+".ref_no","Field","UpdateName","policy.ref_no")
DBSetProp(ThisView+".ref_no","Field","KeyField",.F.)
DBSetProp(ThisView+".ref_no","Field","Updatable",.T.)

DBSetProp(ThisView+".effective_date","Field","DataType","D")
DBSetProp(ThisView+".effective_date","Field","UpdateName","policy.effective_date")
DBSetProp(ThisView+".effective_date","Field","KeyField",.F.)
DBSetProp(ThisView+".effective_date","Field","Updatable",.T.)

DBSetProp(ThisView+".expire_date","Field","DataType","D")
DBSetProp(ThisView+".expire_date","Field","UpdateName","policy.expire_date")
DBSetProp(ThisView+".expire_date","Field","KeyField",.F.)
DBSetProp(ThisView+".expire_date","Field","Updatable",.T.)

當我在vfp 增加一個新的一筆資料如下,

select view_policy
appe blan
cRef_No = '0001'
dEffective_Date = date()
dExpire_Date = date()

replace ref_no with cRef_No, effective_date with dEffective_Date, expire_date with dExpire_Date

CursorSetProp("SendUpdates",.T.,"view_policy")
TABLEUPDATE(.f.,.t.,"view_policy")
CursorSetProp("SendUpdates",.f.,"view_policy")

就沒問題, 但當我更改程式如下, 就出現問題了

select view_policy
appe blan
cRef_No = '0001'
dEffective_Date = ctod( "" )
dExpire_Date = ctod( "" )

replace ref_no with cRef_No, effective_date with dEffective_Date, expire_date with dExpire_Date

CursorSetProp("SendUpdates",.T.,"view_policy")
TABLEUPDATE(.f.,.t.,"view_policy")
CursorSetProp("SendUpdates",.f.,"view_policy")

就會出現 error 1526, incorrect date value for column effective_date
請問我如何可以 update '0000-00-00' 去 mysql date column?

[ckp6250]

試看看這樣行否?
dEffective_Date = "0000-00-00"
dExpire_Date = "0000-00-00"

[Darren]

我試了, 不可以, 請問你有方法可以讓 mysql 接受空的日期? 是不是一定要用null ?

[CCB2000]

不要在SQL數據庫中存儲空日期{//}。

PUBLIC m.p_sql_emptydate
m.p_sql_emptydate=DATE(1900,1,1)

REPL dEffective_Date WITH IIF(dEffective_Date={//}, m.p_sql_emptydate, dEffective_Date)

需要顯示日期時：
?IIF(dEffective_Date=m.p_sql_emptydate, {//}, dEffective_Date)

[Darren]

哈哈哈......說的也是, 為甚麼沒有想到

我一直在想方法在數據庫中存儲空日期, 一直在那裡轉
你的簡單的方法就是最好的方法, 謝謝 !!!!!!!!!!

[ckp6250]

我都是透過spt去操控mysql , 存入 '0000-00-00' 不會有問題
不去使用 remote view 和 mysql 溝通

[syntech]

我現在覺得,
最好的方法就是"不要用DATE型態". Orz

[aizz]