>>This just seems to me to be cleaner and more maintainable:
>>
>>
TEXT TO m.lcSQL NOSHOW TEXTMERGE
>> UPDATE Employees
>> SET emp_firstname = ISNULL(?m.emp_firstname, emp_firstname)
>> ,emp_middlename = ISNULL(?m.emp_middlename, emp_middlename)
>> ,emp_lastname = ISNULL(?m.emp_lastname, emp_lastname)
>> ,emp_dob = ISNULL(?m.emp_dob, emp_dob)
>> ,emp_clifk = ISNULL(?m.emp_clifk, emp_clifk)
>> ,emp_number = ISNULL(?m.emp_number, emp_number)
>> ,emp_relfk = ISNULL(?m.emp_relfk, emp_relfk)
>> ,emp_empfk = ISNULL(?m.emp_empfk, emp_empfk)
>> ,emp_sexfk = ISNULL(?m.emp_sexfk, emp_sexfk)
>> ,emp_transfer = ISNULL(?m.emp_transfer, emp_transfer)
>> ,emp_email = ISNULL(?m.emp_email, emp_email)
>> ,emp_title = ISNULL(?m.emp_title, emp_title)
>> ,emp_pensionpolicy = ISNULL(?m.emp_pensionpolicy, emp_pensionpolicy)
>> ,emp_BIR = ISNULL(?m.emp_BIR, emp_BIR)
>> ,emp_NIS = ISNULL(?m.emp_NIS, emp_NIS)
>> ,emp_height = ISNULL(?m.emp_height, emp_height)
>> ,emp_weight = ISNULL(?m.emp_weight, emp_weight)
>> ,emp_phone = ISNULL(?m.emp_phone, emp_phone)
>> ,emp_employdate = ISNULL(?m.emp_employdate, emp_employdate)
>> ,emp_occupation = ISNULL(?m.emp_occupation, emp_occupation)
>> ,emp_maritalstatus = ISNULL(?m.emp_maritalstatus, emp_maritalstatus)
>> ,emp_address1 = ISNULL(?m.emp_address1, emp_address1)
>> ,emp_address2 = ISNULL(?m.emp_address2, emp_address2)
>> ,emp_address3 = ISNULL(?m.emp_address3 , emp_address3)
>> WHERE emp_pk = ?m.emp_pk
>> ENDTEXT
>>
>
>A different approach: the null is tested at the VFP side, reducing the need for redundant updates (in your statement, every column is always updated, no matter what).
>
>
> TEXT TO m.lcSQL NOSHOW TEXTMERGE
> UPDATE Employees
> SET <<IIF(ISNULL(m.emp_firstname),"","emp_firstname = ?m.emp_firstname")>>
> <<IIF(ISNULL(m.emp_middlename),"","emp_middlename = ?m.emp_middlename")>>
> <<IIF(ISNULL(m.emp_lastname),"","emp_lastname = ?m.emp_lastname")>>
> <<IIF(ISNULL(m.emp_dob),"","emp_dob = ?m.emp_dob")>>
> <<IIF(ISNULL(m.emp_clifk),"","emp_clifk = ?m.emp_clifk")>>
> <<IIF(ISNULL(m.emp_number),"","emp_number = ?m.emp_number")>>
> <<IIF(ISNULL(m.emp_relfk),"","emp_relfk = ?m.emp_relfk")>>
> <<IIF(ISNULL(m.emp_empfk),"","emp_empfk = ?m.emp_empfk")>>
> <<IIF(ISNULL(m.emp_sexfk),"","emp_sexfk = ?m.emp_sexfk")>>
> <<IIF(ISNULL(m.emp_transfer),"","emp_transfer = ?m.emp_transfer")>>
> <<IIF(ISNULL(m.emp_email),"","emp_email = ?m.emp_email")>>
> <<IIF(ISNULL(m.emp_title),"","emp_title = ?m.emp_title")>>
> <<IIF(ISNULL(m.emp_pensionpolicy),"","emp_pensionpolicy = ?m.emp_pensionpolicy,")>>
> <<IIF(ISNULL(m.emp_BIR),"","emp_BIR = ?m.emp_BIR")>>
> <<IIF(ISNULL(m.emp_NIS),"","emp_NIS = ?m.emp_NIS")>>
> <<IIF(ISNULL(m.emp_height),"","emp_height = ?m.emp_height")>>
> <<IIF(ISNULL(m.emp_weight),"","emp_weight = ?m.emp_weight")>>
> <<IIF(ISNULL(m.emp_phone),"","emp_phone = ?m.emp_phone")>>
> <<IIF(ISNULL(m.emp_employdate),"","emp_employdate = ?m.emp_employdate")>>
> <<IIF(ISNULL(m.emp_occupation),"","emp_occupation = ?m.emp_occupation")>>
> <<IIF(ISNULL(m.emp_maritalstatus),"","emp_maritalstatus = ?m.emp_maritalstatus")>>
> <<IIF(ISNULL(m.emp_address1),"","emp_address1 = ?m.emp_address1")>>
> <<IIF(ISNULL(m.emp_address2),"","emp_address2 = ?m.emp_address2")>>
> <<IIF(ISNULL(m.emp_address3),"emp_address3 = ?m.emp_address3")>>
> WHERE emp_pk = ?m.emp_pk
> ENDTEXT
>
> IF "SET ," $ m.lcSQL
> m.lcSQL = STRTRAN(m.lcSQL,"SET ),"",""SET ")
> ExecuteQuery(m.lcSQL)
> ENDIF
>
>
>(hope that I got it right, having no sample data to test...).
>
>Edit: the final test must have some tuning, because of newlines and tabs, but the general idea seems ok...
maybe less duplicated code and easier to maintain:
local cSQL, cField, aa, iField
text to cField noshow flags 1
emp_middlename
emp_lastname
emp_dob
emp_clifk
emp_number
emp_relfk
emp_empfk
emp_sexfk
emp_transfer
emp_email
emp_title
emp_pensionpolicy
emp_BIR
emp_NIS
emp_height
emp_weight
emp_phone
emp_employdate
emp_occupation
emp_maritalstatus
emp_address1
emp_address2
emp_address3
endtext
cSQL = ''
for iField = 1 to ALines(aa, m.cField, 5)
cField = m.aa[m.iField]
cSQL = m.cSQL + Iif(IsNull(Evaluate('m.' + m.cField)), '', ',' + m.cField + '=?m.' + m.cField)
endfor
result = Iif(empty(m.cSQL);
, .null.;
, executeQuery('UPDATE Employees SET ' + Ltrim(m.cSQL, ',') + ' WHERE emp_pk = ?m.emp_pk');
)
Thierry Nivelet
FoxinCloud
Give your VFP application a second life, web-based, in YOUR cloud
http://foxincloud.com/Never explain, never complain (Queen Elizabeth II)