I have successfully implemented a "Query By Example" call from a button on my data entry/query form.
I am, however, having problems implementing an Update (Modify) statement.
The script for the Update button uses global variables to hold the value of each field within a row at the time that row is fetched (via "Query", "Next" or "Previous"). These (saved) variables have a prefix of "s_" (ie. s_city, s_state, s_zip, etc.).
The script also uses local variables to hold the value of each field at the time the update is executed. These (current) variables have a prefix of "c_" (ie. c_city, c_state, c_zip, etc.).
Functionality within a "mouseUp" handler compares the "saved" value of each field to its "current" value, via a series of if/then/else statements and places the elements of a SQL Update statement into "tSQLQuery".
I have successfully tested the resulting syntax against my database, via isql.
However, when I attempt to execute modification of a field (or fields) from the form, the update fails - each and every time.
The triggering mechanism within my script is expressed as:
"revSetSQLOfQuery "PeopleUpdate", tSQLQuery"
I have also tested with the "revExecuteSQL" call.
Is there a better means of executing the update? How should I approach "Add" button functionality?
Your suggestions are most sincerely appreciated.
Melvin Cox
-----------------------------------Update_Script---------------------------------------
on mouseUp
-- declare 'saved' variables (global)
global s_pid,s_fname,s_mname,s_lname,s_address1,s_address2,s_city, \
s_state,s_zip,s_country,s_affiliation,s_title,s_phone,s_home_ph, \
s_fax,s_mobile,s_email,s_note,s_last_update,s_active-- declare 'current' variables (local)
global c_pid,c_fname,c_mname,c_lname,c_address1,c_address2,c_city, \
c_state,c_zip,c_country,c_affiliation,c_title,c_phone,c_home_ph, \
c_fax,c_mobile,c_email,c_note,c_last_update,c_active-- save values into (c)urrent variables
put field "pid" into c_pid put field "fname" into c_fname put field "mname" into c_mname put field "lname" into c_lname put field "address1" into c_address1 put field "address2" into c_address2 put field "city" into c_city put field "state" into c_state put field "zip" into c_zip put field "country" into c_country put field "affiliation" into c_affiliation put field "title" into c_title put field "phone" into c_phone put field "home_ph" into c_home_ph put field "fax" into c_fax put field "mobile" into c_mobile put field "email" into c_email put field "note" into c_note put field "last_update" into c_last_update put field "active" into c_active
-- Build SQL Statement
put "UPDATE people SET" \
into tSQLQueryif s_pid <> c_pid then
-- Prevent update of primary key
put "ERROR! The keyfield can't be modified. Use ADD to insert a new record!"
pass mouseUp
end if
if s_fname <> c_fname then put " fname = " after tSQLQuery put "'" & field "fname" & "'" after tSQLQuery end if
if s_mname <> c_mname
then
if s_fname <> c_fname
then
put "," after tSQLQuery
end if
put " mname = " after tSQLQuery
put "'" & field "mname" & "'" after tSQLQuery
end if if s_lname <> c_lname
then
if s_fname <> c_fname \
or s_mname <> c_mname
then
put "," after tSQLQuery
end if
put " lname = " after tSQLQuery
put "'" & field "lname" & "'" after tSQLQuery
end if if s_address1 <> c_address1
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname
then
put "," after tSQLQuery
end if
put " address1 = " after tSQLQuery
put "'" & field "address1" & "'" after tSQLQuery
end if if s_address2 <> c_address2
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1
then
put "," after tSQLQuery
end if
put " address2 = " after tSQLQuery
put "'" & field "address2" & "'" after tSQLQuery
end if if s_city <> c_city
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2
then
put "," after tSQLQuery
end if
put " city = " after tSQLQuery
put "'" & field "city" & "'" after tSQLQuery
end if if s_state <> c_state
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city
then
put "," after tSQLQuery
end if
put " state = " after tSQLQuery
put "'" & field "state" & "'" after tSQLQuery
end if if s_zip <> c_zip
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state
then
put "," after tSQLQuery
end if
put " zip = " after tSQLQuery
put "'" & field "zip" & "'" after tSQLQuery
end if if s_country <> c_country
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip
then
put "," after tSQLQuery
end if
put " country = " after tSQLQuery
put "'" & field "country" & "'" after tSQLQuery
end if if s_affiliation <> c_affiliation
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country
then
put "," after tSQLQuery
end if
put " affiliation = " after tSQLQuery
put "'" & field "affiliation" & "'" after tSQLQuery
end if if s_title <> c_title
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation
then
put "," after tSQLQuery
end if
put " title = " after tSQLQuery
put "'" & field "title" & "'" after tSQLQuery
end if if s_phone <> c_phone
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation \
or s_title <> c_title
then
put "," after tSQLQuery
end if
put " phone = " after tSQLQuery
put "'" & field "phone" & "'" after tSQLQuery
end if if s_home_ph <> c_home_ph
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation \
or s_title <> c_title \
or s_phone <> c_phone
then
put "," after tSQLQuery
end if
put " home_ph = " after tSQLQuery
put "'" & field "home_ph" & "'" after tSQLQuery
end if if s_fax <> c_fax
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation \
or s_title <> c_title \
or s_phone <> c_phone \
or s_home_ph <> c_home_ph
then
put "," after tSQLQuery
end if
put " fax = " after tSQLQuery
put "'" & field "fax" & "'" after tSQLQuery
end if if s_mobile <> c_mobile
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation \
or s_title <> c_title \
or s_phone <> c_phone \
or s_home_ph <> c_home_ph \
or s_fax <> c_fax
then
put "," after tSQLQuery
end if
put " mobile = " after tSQLQuery
put "'" & field "mobile" & "'" after tSQLQuery
end if if s_email <> c_email
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation \
or s_title <> c_title \
or s_phone <> c_phone \
or s_home_ph <> c_home_ph \
or s_fax <> c_fax \
or s_mobile <> c_mobile
then
put "," after tSQLQuery
end if
put " email = " after tSQLQuery
put "'" & field "email" & "'" after tSQLQuery
end if if s_note <> c_note
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation \
or s_title <> c_title \
or s_phone <> c_phone \
or s_home_ph <> c_home_ph \
or s_fax <> c_fax \
or s_mobile <> c_mobile \
or s_email <> c_email
then
put "," after tSQLQuery
end if
put " note = " after tSQLQuery
put "'" & field "note" & "'" after tSQLQuery
end if if s_last_update <> c_last_update
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation \
or s_title <> c_title \
or s_phone <> c_phone \
or s_home_ph <> c_home_ph \
or s_fax <> c_fax \
or s_mobile <> c_mobile \
or s_email <> c_email
then
put "," after tSQLQuery
end if
put " last_update = " after tSQLQuery
put "'" & field "last_update" & "'" after tSQLQuery
end if if s_active <> c_active
then
if s_fname <> c_fname \
or s_mname <> c_mname \
or s_lname <> c_lname \
or s_address1 <> c_address1 \
or s_address2 <> c_address2 \
or s_city <> c_city \
or s_state <> c_state \
or s_zip <> c_zip \
or s_country <> c_country \
or s_affiliation <> c_affiliation \
or s_title <> c_title \
or s_phone <> c_phone \
or s_home_ph <> c_home_ph \
or s_fax <> c_fax \
or s_mobile <> c_mobile \
or s_email <> c_email \
or s_last_update <> c_last_update
then
put "," after tSQLQuery
end if
put " active = " after tSQLQuery
put "'" & field "active" & "'" after tSQLQuery
end ifput " WHERE 0=0" after tSQLQuery put " and pid = " after tSQLQuery put ' & field "pid" & ' after tSQLQuery
-- Set tSQLQuery to empty if all fields are empty
if the length of field "pid" = 0 \
and the length of field "fname" = 0 \
and the length of field "mname" = 0 \
and the length of field "lname" = 0 \
and the length of field "address1" = 0 \
and the length of field "address2" = 0 \
and the length of field "city" = 0 \
and the length of field "state" = 0 \
and the length of field "zip" = 0 \
and the length of field "country" = 0 \
and the length of field "affiliation" = 0 \
and the length of field "title" = 0 \
and the length of field "phone" = 0 \
and the length of field "home_ph" = 0 \
and the length of field "fax" = 0 \
and the length of field "mobile" = 0 \
and the length of field "email" = 0 \
and the length of field "active" = 0
then
put empty into tSQLQuery
end ifrevSetSQLOfQuery "PeopleUpdate", tSQLQuery
-- Test for errors
if revDatabaseConnectResult (PeopleUpdate) is empty then put "Update Successful" else put "Sorry, Update Failed" end if
end mouseUp
_________________________________________________________________
Share holiday photos without swamping your Inbox. Get MSN Extra Storage now! http://join.msn.com/?PAGE=features/es
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
