Database - Update from Data Entry/Query form
Melvin Cox
melcox at hotmail.com
Sun Nov 23 21:05:18 EST 2003
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 tSQLQuery
if 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 if
put " 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 if
revSetSQLOfQuery "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
More information about the use-livecode
mailing list