To All:
Can anyone re-write the following code that comes with Alpha learning Xdialog.(c:\program files\a5v9\learing xdialog)
It is written to use ADO to the alphasports.mdb and I need it to use a table.
Charlie Crimmel
'Date Created: 19-Dec-2006 11:12:51 PM
'Last Updated: 05-Jan-2007 12:32:19 PM
'Created By : SELWYN
'Updated By : Selwyn
AlphaDAOForm()
function AlphaDAOForm as v ()
dim mode as c
dim flagRecordIsDirty as l = .f.
dim flag as l
dim cn as sql::connection
flag = cn.open("{A5API=Access,FileName='"+find_AlphaSports_MDB()+"',UserName='Admin'}")
if flag = .f. then
ui_msg_box("Error","Could not connect to database. " + cn.CallResult.text)
exit function
end if
dim sql_picklist as c
sql_picklist = "select concatenate('{Data=',customer_id,'}',lastname,', ', firstname) from customer order by lastname"
'Turn on portable sql - we are using the concatenate() function which is a portable sql function.
cn.PortableSQLEnabled = .t.
flag = cn.Execute(sql_picklist)
if flag = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
exit function
end if
dim customerList as c
customerList = cn.ResultSet.tostring()
dim searchCustomerId as c = ""
dim previousSearchCustomerId as c = ""
dim CurrentRecordPrimaryKey as n
dim vars as p
dim vars.CUSTOMER_ID as n
dim vars.FIRSTNAME as C
dim vars.LASTNAME as C
dim vars.COMPANY as C
dim vars.PHONE as C
dim vars.FAX as C
dim vars.BILL_ADDRESS1 as C
dim vars.BILL_ADDRESS2 as C
dim vars.BILL_CITY as C
dim vars.BILL_STATE_REGION as C
dim vars.BILL_POSTAL_CODE as C
dim vars.BILL_COUNTRY as C
dim vars.SHIP_ADDRESS1 as C
dim vars.SHIP_ADDRESS2 as C
dim vars.SHIP_CITY as C
dim vars.SHIP_STATE_REGION as C
dim vars.SHIP_POSTAL_CODE as C
dim vars.SHIP_COUNTRY as C
dim vars.SHIP_SAME as l
dim vars.EMAIL as C
dim origVars as p
dim flagRecordFound as l = .f.
dlg_body = <<%dlg%
{wrap=100}
This Xdialog shows how to use AlphaDAO to update a SQL database. This example updates the sample Alphasports.mdb database \(an Access database) that ships with Alpha Five. The priciples demonstrated in this script would apply to any SQL database.;
{lf};
{removecomments}
'this command causes all lines that start with an apostrophe to be remove.
Customer to edit: [%O={@@}%.40searchCustomerId^=customerList!findCustomer] <Add New Record!newRecord>;
{lf};
{blueframe=1,1:Customer Information}
{region100}
'Set the enable flag for this field to .f. because it is an auto-increment field.
Customer Id | [.20vars.customer_id?.f..and.flagRecordFound];
&First name, Last name: | [.27vars.firstname!setDirty?flagRecordFound] [1.59"vars.lastname!setDirty?flagRecordFound];
&Company: | [.60vars.company!setDirty?flagRecordFound];
&Phone: | [.30vars.phone!setDirty?flagRecordFound];
Fa&x: | [.30vars.fax!setDirty?flagRecordFound];
&E-mail: | [.60vars.EMAIL!setDirty?flagRecordFound];
{lf};
{color=light blue on win3d}
{line=2,0:Billing address};
{color=}
&Address: | [.60vars.Bill_address1!setDirty?flagRecordFound];
| [.30vars.Bill_address2!setDirty?flagRecordFound];
Cit&y, State Zip: | [.25vars.bill_city!setDirty?flagRecordFound] [.10vars.bill_state_region!setDirty?flagRecordFound] [.15vars.bill_postal_code!setDirty?flagRecordFound];
{lf};
'Shipping address - disable all controls if the shipping address is the same as the billing address.
{color=light blue on win3d}
{line=2,0:Shipping address};
{color=}
| (vars.SHIP_SAME!setDirty?flagRecordFound) &Shipping address same as billing address?;
{lf};
A&ddress: | [.60vars.Ship_address1!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)];
| [.30vars.Ship_address2!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)];
Ci&ty, State Zip: | [.25vars.ship_city!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)] [.10vars.ship_state_region!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)] [.15vars.ship_postal_code!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)];
{lf};
{endregion100};
{lf};
{region200}
<Save Record!save?flagRecordFound.and.flagRecordIsDirty> <Delete Record!delete?flagRecordFound.and.mode="Change"> <Undo Edits!undoEdits?flagRecordIsDirty>;
{endregion200};
%dlg%
dim dlg_event as c
dlg_event = <<%code%
if a_dlg_button = "newRecord" then
a_dlg_button = ""
newRecord(local_variables())
end if
if a_dlg_button = "delete" then
a_dlg_button = ""
dim q as n
q = ui_msg_box("Warning","Are you sure you want to delete this record?",UI_YES_NO)
if q = ui_yes_selected then
deleteRecord(local_variables())
end if
end if
if a_dlg_button = "setDirty" then
a_dlg_button = ""
flagRecordIsDirty = .t.
end if
if a_dlg_button = "undoEdits" then
a_dlg_button = ""
dim q as n
q = ui_msg_box("Notice","Are you sure you want to undo edits to this record?",UI_YES_NO)
if q = ui_yes_selected then
'restore the original values in the record - these are stored in the 'origVars' dot variable.
property_recurse_assign(vars,origVars)
'after an undoEdits, the record is no longer considered to be 'dirty'
flagRecordIsDirty = .f.
end if
end if
if a_dlg_button = "findCustomer" then
a_dlg_button = ""
findRecord(local_variables())
end if
if a_dlg_button = "save" then
a_dlg_button = ""
if mode = "Change" then
saveChanges(local_variables())
if (origVars.lastname <> vars.lastname) .or. (origVars.firstname <> vars.firstname) then
'user edited firstname or lastname, so must repopulate the picklist
cn.Execute(sql_picklist)
customerList = cn.ResultSet.tostring()
end if
else if mode = "New" then
saveNewRecord(local_variables())
end if
flagRecordIsDirty = .f.
'save the current values in the field to the 'origVars' dot variable in case the user later wants to do an 'undoEdits'
property_recurse_assign(origVars,vars)
end if
%code%
dim dlg_title as c
dlg_title = "Add/Edit Customer"
ui_dlg_box(dlg_title,dlg_body,dlg_event)
cn.close()
end function
function findRecord as v (lv as p)
with lv
if flagRecordIsDirty = .t. then
dim q as n
q = ui_msg_box("Notice","Record has not been saved. Are you sure you want to lose your edits?",UI_YES_NO_CANCEL)
if q = ui_no_selected .or. q = ui_cancel_selected then
searchCustomerId = previousSearchCustomerId
exit function
end if
end if
'store the current record primary key in the 'previousSearchCustomerId' field so that later
'if the user navigates off the current record, and it is dirty, we can go back to the dirty record.
previousSearchCustomerId = searchCustomerId
mode = "change"
flagRecordFound = .f.
dim sqlFind as c
sqlFind = "select * from customer where customer_id = :whatCustomer"
dim arg as sql::arguments
CurrentRecordPrimaryKey = convert_type(searchCustomerId,"N")
arg.set("whatCustomer",CurrentRecordPrimaryKey)
flag = cn.Execute(sqlFind,arg)
if flag = .f. then
ui_msg_box("Error",cn.CallResult.text)
exit function
end if
flagRecordFound = .t.
flagRecordIsDirty = .f.
dim rs as sql::resultset
rs = cn.ResultSet
vars.CUSTOMER_ID = rs.data("CUSTOMER_ID")
vars.FIRSTNAME = rs.data("FIRSTNAME")
vars.LASTNAME = rs.data("LASTNAME")
vars.COMPANY = rs.data("COMPANY")
vars.PHONE = rs.data("PHONE")
vars.FAX = rs.data("FAX")
vars.BILL_ADDRESS1 = rs.data("BILL_ADDRESS1")
vars.BILL_ADDRESS2 = rs.data("BILL_ADDRESS2")
vars.BILL_CITY = rs.data("BILL_CITY")
vars.BILL_STATE_REGION = rs.data("BILL_STATE_REGION")
vars.BILL_POSTAL_CODE = rs.data("BILL_POSTAL_CODE")
vars.BILL_COUNTRY = rs.data("BILL_COUNTRY")
vars.SHIP_ADDRESS1 = rs.data("SHIP_ADDRESS1")
vars.SHIP_ADDRESS2 = rs.data("SHIP_ADDRESS2")
vars.SHIP_CITY = rs.data("SHIP_CITY")
vars.SHIP_STATE_REGION = rs.data("SHIP_STATE_REGION")
vars.SHIP_POSTAL_CODE = rs.data("SHIP_POSTAL_CODE")
vars.SHIP_COUNTRY = rs.data("SHIP_COUNTRY")
vars.SHIP_SAME = rs.data("SHIP_SAME")
vars.EMAIL = rs.data("EMAIL")
property_recurse_assign(origVars,vars)
end with
end function
function deleteRecord as v (lv as p)
with lv
dim sqlDelete as c
sqlFind = "delete from customer where customer_id = :whatCustomer"
dim arg as sql::arguments
CurrentRecordPrimaryKey = convert_type(searchCustomerId,"N")
arg.set("whatCustomer",CurrentRecordPrimaryKey)
flag = cn.Execute(sqlFind,arg)
if flag = .f. then
ui_msg_box("Error - could not delete record",cn.CallResult.text)
exit function
end if
flagRecordFound = .f.
'repopulate the drop down combo box
cn.Execute(sql_picklist)
customerList = cn.ResultSet.tostring()
'This causes the drop down combo box to show a null selection
searchCustomerId = ""
'clear out the variables displayed in the Xdialog
clearVariables(local_variables())
end with
end function
function saveNewRecord as v (lv as p)
with lv
dim sqlInsert as c
sqlInsert = <<%txt%
INSERT INTO Customer (
FIRSTNAME,
LASTNAME,
COMPANY,
PHONE,
FAX,
BILL_ADDRESS1,
BILL_ADDRESS2,
BILL_CITY,
BILL_STATE_REGION,
BILL_POSTAL_CODE,
BILL_COUNTRY,
SHIP_ADDRESS1,
SHIP_ADDRESS2,
SHIP_CITY,
SHIP_STATE_REGION,
SHIP_POSTAL_CODE,
SHIP_COUNTRY,
SHIP_SAME,
EMAIL
)
VALUES
(
:new_FIRSTNAME,
:new_LASTNAME,
:new_COMPANY,
:new_PHONE,
:new_FAX,
:new_BILL_ADDRESS1,
:new_BILL_ADDRESS2,
:new_BILL_CITY,
:new_BILL_STATE_REGION,
:new_BILL_POSTAL_CODE,
:new_BILL_COUNTRY,
:new_SHIP_ADDRESS1,
:new_SHIP_ADDRESS2,
:new_SHIP_CITY,
:new_SHIP_STATE_REGION,
:new_SHIP_POSTAL_CODE,
:new_SHIP_COUNTRY,
:new_SHIP_SAME,
:new_EMAIL
)
%txt%
delete args
dim args as sql::arguments
'set arguments to the value of the variables shown in the Xdialog
args.set("New_FIRSTNAME",vars.FIRSTNAME)
args.set("New_LASTNAME",vars.LASTNAME)
args.set("New_COMPANY",vars.COMPANY)
args.set("New_PHONE",vars.PHONE)
args.set("New_FAX",vars.FAX)
args.set("New_BILL_ADDRESS1",vars.BILL_ADDRESS1)
args.set("New_BILL_ADDRESS2",vars.BILL_ADDRESS2)
args.set("New_BILL_CITY",vars.BILL_CITY)
args.set("New_BILL_STATE_REGION",vars.BILL_STATE_REGION)
args.set("New_BILL_POSTAL_CODE",vars.BILL_POSTAL_CODE)
args.set("New_BILL_COUNTRY",vars.BILL_COUNTRY)
args.set("New_SHIP_ADDRESS1",vars.SHIP_ADDRESS1)
args.set("New_SHIP_ADDRESS2",vars.SHIP_ADDRESS2)
args.set("New_SHIP_CITY",vars.SHIP_CITY)
args.set("New_SHIP_STATE_REGION",vars.SHIP_STATE_REGION)
args.set("New_SHIP_POSTAL_CODE",vars.SHIP_POSTAL_CODE)
args.set("New_SHIP_COUNTRY",vars.SHIP_COUNTRY)
args.set("New_SHIP_SAME",vars.SHIP_SAME)
args.set("New_EMAIL",vars.EMAIL)
flag = cn.Execute(sqlInsert,args)
if flag = .f. then
ui_msg_box("Error","Record was not inserted. " + crlf()+ cn.CallResult.text)
exit function
else
'The customer_id field is an auto-increment field.
'We now need to find out what value the SQL Database assigned to this field when it added the new record.
'The .LastInsertedIdentity property of the connection object's 'CallResult' object has this information.
new_customerId = cn.CallResult.LastInsertedIdentity
end if
ui_msg_box("Notice","Records added: " + cn.AffectedRows())
'repopulate the drop down combo box
cn.Execute(sql_picklist)
customerList = cn.ResultSet.tostring()
'set the value of the combo box variable so that it shows the new record just added as the selected record
' 'searchCustomerId' is a character field so we 'new_customerId' to a character value by appending it to a null string.
searchCustomerId = "" + new_customerId
'After a save, the record is not dirty.
flagRecordIsDirty = .f.
'repopulate the Xdialog - this will update the display to show any default values that were assigned by the
'SQL database engine when the new record was added. It will also update the display of the customer_id field to
'show the actual value of the auto-increment field that was assigned when the record was added.
findRecord(lv)
end with
end function
function saveChanges as v (lv as p)
with lv
dim sqlUpdate as c
sqlUpdate = <<%txt%
UPDATE Customer SET
FIRSTNAME = :new_FIRSTNAME,
LASTNAME = :new_LASTNAME,
COMPANY = :new_COMPANY,
PHONE = :new_PHONE,
FAX = :new_FAX,
BILL_ADDRESS1 = :new_BILL_ADDRESS1,
BILL_ADDRESS2 = :new_BILL_ADDRESS2,
BILL_CITY = :new_BILL_CITY,
BILL_STATE_REGION = :new_BILL_STATE_REGION,
BILL_POSTAL_CODE = :new_BILL_POSTAL_CODE,
BILL_COUNTRY = :new_BILL_COUNTRY,
SHIP_ADDRESS1 = :new_SHIP_ADDRESS1,
SHIP_ADDRESS2 = :new_SHIP_ADDRESS2,
SHIP_CITY = :new_SHIP_CITY,
SHIP_STATE_REGION = :new_SHIP_STATE_REGION,
SHIP_POSTAL_CODE = :new_SHIP_POSTAL_CODE,
SHIP_COUNTRY = :new_SHIP_COUNTRY,
SHIP_SAME = :new_SHIP_SAME,
EMAIL = :new_EMAIL
WHERE
CUSTOMER_ID = :old_CUSTOMER_ID
%txt%
delete args
dim args as sql::arguments
args.set("old_customer_id",CurrentRecordPrimaryKey)
'set arguments to the value of the variables shown in the Xdialog
args.set("New_FIRSTNAME",vars.FIRSTNAME)
args.set("New_LASTNAME",vars.LASTNAME)
args.set("New_COMPANY",vars.COMPANY)
args.set("New_PHONE",vars.PHONE)
args.set("New_FAX",vars.FAX)
args.set("New_BILL_ADDRESS1",vars.BILL_ADDRESS1)
args.set("New_BILL_ADDRESS2",vars.BILL_ADDRESS2)
args.set("New_BILL_CITY",vars.BILL_CITY)
args.set("New_BILL_STATE_REGION",vars.BILL_STATE_REGION)
args.set("New_BILL_POSTAL_CODE",vars.BILL_POSTAL_CODE)
args.set("New_BILL_COUNTRY",vars.BILL_COUNTRY)
args.set("New_SHIP_ADDRESS1",vars.SHIP_ADDRESS1)
args.set("New_SHIP_ADDRESS2",vars.SHIP_ADDRESS2)
args.set("New_SHIP_CITY",vars.SHIP_CITY)
args.set("New_SHIP_STATE_REGION",vars.SHIP_STATE_REGION)
args.set("New_SHIP_POSTAL_CODE",vars.SHIP_POSTAL_CODE)
args.set("New_SHIP_COUNTRY",vars.SHIP_COUNTRY)
args.set("New_SHIP_SAME",vars.SHIP_SAME)
args.set("New_EMAIL",vars.EMAIL)
flag = cn.Execute(sqlUpdate,args)
if flag = .f. then
ui_msg_box("Error","Record was not updated. " + crlf()+ cn.CallResult.text)
exit function
end if
if cn.AffectedRows() = 0 then
ui_msg_box("Error","Record to update could not be found. Record was not updated.")
exit function
end if
ui_msg_box("Notice","Records updated: " + cn.AffectedRows())
end with
end function
function clearVariables as v (lv as p)
with lv
vars.CUSTOMER_ID = 0
vars.FIRSTNAME = ""
vars.LASTNAME = ""
vars.COMPANY = ""
vars.PHONE = ""
vars.FAX = ""
vars.BILL_ADDRESS1 = ""
vars.BILL_ADDRESS2 = ""
vars.BILL_CITY = ""
vars.BILL_STATE_REGION = ""
vars.BILL_POSTAL_CODE = ""
vars.BILL_COUNTRY = ""
vars.SHIP_ADDRESS1 = ""
vars.SHIP_ADDRESS2 = ""
vars.SHIP_CITY = ""
vars.SHIP_STATE_REGION = ""
vars.SHIP_POSTAL_CODE = ""
vars.SHIP_COUNTRY = ""
vars.SHIP_SAME = .f.
vars.EMAIL = ""
end with
end function
function newRecord as v (lv as p)
with lv
if flagRecordIsDirty = .t. then
dim q as n
q = ui_msg_box("Notice","Record has not been saved. Are you sure you want to lose your edits?",UI_YES_NO_CANCEL)
if q = ui_no_selected .or. q = ui_cancel_selected then
searchCustomerId = previousSearchCustomerId
exit function
end if
end if
flagRecordIsDirty = .f.
'need to set flagRecordFound to .t. so that the edit boxes on the dialog are enabled
flagRecordFound = .t.
mode = "New"
'set the combo box to show a null value
searchCustomerId = ""
clearVariables(local_variables())
'store original values in the 'origVars' dot variable in case the user wants to do an 'undoEdits' on the new record
'(which will get back to a blank new record).
property_recurse_assign(origVars,vars)
end with
end function
Can anyone re-write the following code that comes with Alpha learning Xdialog.(c:\program files\a5v9\learing xdialog)
It is written to use ADO to the alphasports.mdb and I need it to use a table.
Charlie Crimmel
'Date Created: 19-Dec-2006 11:12:51 PM
'Last Updated: 05-Jan-2007 12:32:19 PM
'Created By : SELWYN
'Updated By : Selwyn
AlphaDAOForm()
function AlphaDAOForm as v ()
dim mode as c
dim flagRecordIsDirty as l = .f.
dim flag as l
dim cn as sql::connection
flag = cn.open("{A5API=Access,FileName='"+find_AlphaSports_MDB()+"',UserName='Admin'}")
if flag = .f. then
ui_msg_box("Error","Could not connect to database. " + cn.CallResult.text)
exit function
end if
dim sql_picklist as c
sql_picklist = "select concatenate('{Data=',customer_id,'}',lastname,', ', firstname) from customer order by lastname"
'Turn on portable sql - we are using the concatenate() function which is a portable sql function.
cn.PortableSQLEnabled = .t.
flag = cn.Execute(sql_picklist)
if flag = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
exit function
end if
dim customerList as c
customerList = cn.ResultSet.tostring()
dim searchCustomerId as c = ""
dim previousSearchCustomerId as c = ""
dim CurrentRecordPrimaryKey as n
dim vars as p
dim vars.CUSTOMER_ID as n
dim vars.FIRSTNAME as C
dim vars.LASTNAME as C
dim vars.COMPANY as C
dim vars.PHONE as C
dim vars.FAX as C
dim vars.BILL_ADDRESS1 as C
dim vars.BILL_ADDRESS2 as C
dim vars.BILL_CITY as C
dim vars.BILL_STATE_REGION as C
dim vars.BILL_POSTAL_CODE as C
dim vars.BILL_COUNTRY as C
dim vars.SHIP_ADDRESS1 as C
dim vars.SHIP_ADDRESS2 as C
dim vars.SHIP_CITY as C
dim vars.SHIP_STATE_REGION as C
dim vars.SHIP_POSTAL_CODE as C
dim vars.SHIP_COUNTRY as C
dim vars.SHIP_SAME as l
dim vars.EMAIL as C
dim origVars as p
dim flagRecordFound as l = .f.
dlg_body = <<%dlg%
{wrap=100}
This Xdialog shows how to use AlphaDAO to update a SQL database. This example updates the sample Alphasports.mdb database \(an Access database) that ships with Alpha Five. The priciples demonstrated in this script would apply to any SQL database.;
{lf};
{removecomments}
'this command causes all lines that start with an apostrophe to be remove.
Customer to edit: [%O={@@}%.40searchCustomerId^=customerList!findCustomer] <Add New Record!newRecord>;
{lf};
{blueframe=1,1:Customer Information}
{region100}
'Set the enable flag for this field to .f. because it is an auto-increment field.
Customer Id | [.20vars.customer_id?.f..and.flagRecordFound];
&First name, Last name: | [.27vars.firstname!setDirty?flagRecordFound] [1.59"vars.lastname!setDirty?flagRecordFound];
&Company: | [.60vars.company!setDirty?flagRecordFound];
&Phone: | [.30vars.phone!setDirty?flagRecordFound];
Fa&x: | [.30vars.fax!setDirty?flagRecordFound];
&E-mail: | [.60vars.EMAIL!setDirty?flagRecordFound];
{lf};
{color=light blue on win3d}
{line=2,0:Billing address};
{color=}
&Address: | [.60vars.Bill_address1!setDirty?flagRecordFound];
| [.30vars.Bill_address2!setDirty?flagRecordFound];
Cit&y, State Zip: | [.25vars.bill_city!setDirty?flagRecordFound] [.10vars.bill_state_region!setDirty?flagRecordFound] [.15vars.bill_postal_code!setDirty?flagRecordFound];
{lf};
'Shipping address - disable all controls if the shipping address is the same as the billing address.
{color=light blue on win3d}
{line=2,0:Shipping address};
{color=}
| (vars.SHIP_SAME!setDirty?flagRecordFound) &Shipping address same as billing address?;
{lf};
A&ddress: | [.60vars.Ship_address1!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)];
| [.30vars.Ship_address2!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)];
Ci&ty, State Zip: | [.25vars.ship_city!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)] [.10vars.ship_state_region!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)] [.15vars.ship_postal_code!setDirty?flagRecordFound.and.(vars.SHIP_SAME=.f.)];
{lf};
{endregion100};
{lf};
{region200}
<Save Record!save?flagRecordFound.and.flagRecordIsDirty> <Delete Record!delete?flagRecordFound.and.mode="Change"> <Undo Edits!undoEdits?flagRecordIsDirty>;
{endregion200};
%dlg%
dim dlg_event as c
dlg_event = <<%code%
if a_dlg_button = "newRecord" then
a_dlg_button = ""
newRecord(local_variables())
end if
if a_dlg_button = "delete" then
a_dlg_button = ""
dim q as n
q = ui_msg_box("Warning","Are you sure you want to delete this record?",UI_YES_NO)
if q = ui_yes_selected then
deleteRecord(local_variables())
end if
end if
if a_dlg_button = "setDirty" then
a_dlg_button = ""
flagRecordIsDirty = .t.
end if
if a_dlg_button = "undoEdits" then
a_dlg_button = ""
dim q as n
q = ui_msg_box("Notice","Are you sure you want to undo edits to this record?",UI_YES_NO)
if q = ui_yes_selected then
'restore the original values in the record - these are stored in the 'origVars' dot variable.
property_recurse_assign(vars,origVars)
'after an undoEdits, the record is no longer considered to be 'dirty'
flagRecordIsDirty = .f.
end if
end if
if a_dlg_button = "findCustomer" then
a_dlg_button = ""
findRecord(local_variables())
end if
if a_dlg_button = "save" then
a_dlg_button = ""
if mode = "Change" then
saveChanges(local_variables())
if (origVars.lastname <> vars.lastname) .or. (origVars.firstname <> vars.firstname) then
'user edited firstname or lastname, so must repopulate the picklist
cn.Execute(sql_picklist)
customerList = cn.ResultSet.tostring()
end if
else if mode = "New" then
saveNewRecord(local_variables())
end if
flagRecordIsDirty = .f.
'save the current values in the field to the 'origVars' dot variable in case the user later wants to do an 'undoEdits'
property_recurse_assign(origVars,vars)
end if
%code%
dim dlg_title as c
dlg_title = "Add/Edit Customer"
ui_dlg_box(dlg_title,dlg_body,dlg_event)
cn.close()
end function
function findRecord as v (lv as p)
with lv
if flagRecordIsDirty = .t. then
dim q as n
q = ui_msg_box("Notice","Record has not been saved. Are you sure you want to lose your edits?",UI_YES_NO_CANCEL)
if q = ui_no_selected .or. q = ui_cancel_selected then
searchCustomerId = previousSearchCustomerId
exit function
end if
end if
'store the current record primary key in the 'previousSearchCustomerId' field so that later
'if the user navigates off the current record, and it is dirty, we can go back to the dirty record.
previousSearchCustomerId = searchCustomerId
mode = "change"
flagRecordFound = .f.
dim sqlFind as c
sqlFind = "select * from customer where customer_id = :whatCustomer"
dim arg as sql::arguments
CurrentRecordPrimaryKey = convert_type(searchCustomerId,"N")
arg.set("whatCustomer",CurrentRecordPrimaryKey)
flag = cn.Execute(sqlFind,arg)
if flag = .f. then
ui_msg_box("Error",cn.CallResult.text)
exit function
end if
flagRecordFound = .t.
flagRecordIsDirty = .f.
dim rs as sql::resultset
rs = cn.ResultSet
vars.CUSTOMER_ID = rs.data("CUSTOMER_ID")
vars.FIRSTNAME = rs.data("FIRSTNAME")
vars.LASTNAME = rs.data("LASTNAME")
vars.COMPANY = rs.data("COMPANY")
vars.PHONE = rs.data("PHONE")
vars.FAX = rs.data("FAX")
vars.BILL_ADDRESS1 = rs.data("BILL_ADDRESS1")
vars.BILL_ADDRESS2 = rs.data("BILL_ADDRESS2")
vars.BILL_CITY = rs.data("BILL_CITY")
vars.BILL_STATE_REGION = rs.data("BILL_STATE_REGION")
vars.BILL_POSTAL_CODE = rs.data("BILL_POSTAL_CODE")
vars.BILL_COUNTRY = rs.data("BILL_COUNTRY")
vars.SHIP_ADDRESS1 = rs.data("SHIP_ADDRESS1")
vars.SHIP_ADDRESS2 = rs.data("SHIP_ADDRESS2")
vars.SHIP_CITY = rs.data("SHIP_CITY")
vars.SHIP_STATE_REGION = rs.data("SHIP_STATE_REGION")
vars.SHIP_POSTAL_CODE = rs.data("SHIP_POSTAL_CODE")
vars.SHIP_COUNTRY = rs.data("SHIP_COUNTRY")
vars.SHIP_SAME = rs.data("SHIP_SAME")
vars.EMAIL = rs.data("EMAIL")
property_recurse_assign(origVars,vars)
end with
end function
function deleteRecord as v (lv as p)
with lv
dim sqlDelete as c
sqlFind = "delete from customer where customer_id = :whatCustomer"
dim arg as sql::arguments
CurrentRecordPrimaryKey = convert_type(searchCustomerId,"N")
arg.set("whatCustomer",CurrentRecordPrimaryKey)
flag = cn.Execute(sqlFind,arg)
if flag = .f. then
ui_msg_box("Error - could not delete record",cn.CallResult.text)
exit function
end if
flagRecordFound = .f.
'repopulate the drop down combo box
cn.Execute(sql_picklist)
customerList = cn.ResultSet.tostring()
'This causes the drop down combo box to show a null selection
searchCustomerId = ""
'clear out the variables displayed in the Xdialog
clearVariables(local_variables())
end with
end function
function saveNewRecord as v (lv as p)
with lv
dim sqlInsert as c
sqlInsert = <<%txt%
INSERT INTO Customer (
FIRSTNAME,
LASTNAME,
COMPANY,
PHONE,
FAX,
BILL_ADDRESS1,
BILL_ADDRESS2,
BILL_CITY,
BILL_STATE_REGION,
BILL_POSTAL_CODE,
BILL_COUNTRY,
SHIP_ADDRESS1,
SHIP_ADDRESS2,
SHIP_CITY,
SHIP_STATE_REGION,
SHIP_POSTAL_CODE,
SHIP_COUNTRY,
SHIP_SAME,
)
VALUES
(
:new_FIRSTNAME,
:new_LASTNAME,
:new_COMPANY,
:new_PHONE,
:new_FAX,
:new_BILL_ADDRESS1,
:new_BILL_ADDRESS2,
:new_BILL_CITY,
:new_BILL_STATE_REGION,
:new_BILL_POSTAL_CODE,
:new_BILL_COUNTRY,
:new_SHIP_ADDRESS1,
:new_SHIP_ADDRESS2,
:new_SHIP_CITY,
:new_SHIP_STATE_REGION,
:new_SHIP_POSTAL_CODE,
:new_SHIP_COUNTRY,
:new_SHIP_SAME,
:new_EMAIL
)
%txt%
delete args
dim args as sql::arguments
'set arguments to the value of the variables shown in the Xdialog
args.set("New_FIRSTNAME",vars.FIRSTNAME)
args.set("New_LASTNAME",vars.LASTNAME)
args.set("New_COMPANY",vars.COMPANY)
args.set("New_PHONE",vars.PHONE)
args.set("New_FAX",vars.FAX)
args.set("New_BILL_ADDRESS1",vars.BILL_ADDRESS1)
args.set("New_BILL_ADDRESS2",vars.BILL_ADDRESS2)
args.set("New_BILL_CITY",vars.BILL_CITY)
args.set("New_BILL_STATE_REGION",vars.BILL_STATE_REGION)
args.set("New_BILL_POSTAL_CODE",vars.BILL_POSTAL_CODE)
args.set("New_BILL_COUNTRY",vars.BILL_COUNTRY)
args.set("New_SHIP_ADDRESS1",vars.SHIP_ADDRESS1)
args.set("New_SHIP_ADDRESS2",vars.SHIP_ADDRESS2)
args.set("New_SHIP_CITY",vars.SHIP_CITY)
args.set("New_SHIP_STATE_REGION",vars.SHIP_STATE_REGION)
args.set("New_SHIP_POSTAL_CODE",vars.SHIP_POSTAL_CODE)
args.set("New_SHIP_COUNTRY",vars.SHIP_COUNTRY)
args.set("New_SHIP_SAME",vars.SHIP_SAME)
args.set("New_EMAIL",vars.EMAIL)
flag = cn.Execute(sqlInsert,args)
if flag = .f. then
ui_msg_box("Error","Record was not inserted. " + crlf()+ cn.CallResult.text)
exit function
else
'The customer_id field is an auto-increment field.
'We now need to find out what value the SQL Database assigned to this field when it added the new record.
'The .LastInsertedIdentity property of the connection object's 'CallResult' object has this information.
new_customerId = cn.CallResult.LastInsertedIdentity
end if
ui_msg_box("Notice","Records added: " + cn.AffectedRows())
'repopulate the drop down combo box
cn.Execute(sql_picklist)
customerList = cn.ResultSet.tostring()
'set the value of the combo box variable so that it shows the new record just added as the selected record
' 'searchCustomerId' is a character field so we 'new_customerId' to a character value by appending it to a null string.
searchCustomerId = "" + new_customerId
'After a save, the record is not dirty.
flagRecordIsDirty = .f.
'repopulate the Xdialog - this will update the display to show any default values that were assigned by the
'SQL database engine when the new record was added. It will also update the display of the customer_id field to
'show the actual value of the auto-increment field that was assigned when the record was added.
findRecord(lv)
end with
end function
function saveChanges as v (lv as p)
with lv
dim sqlUpdate as c
sqlUpdate = <<%txt%
UPDATE Customer SET
FIRSTNAME = :new_FIRSTNAME,
LASTNAME = :new_LASTNAME,
COMPANY = :new_COMPANY,
PHONE = :new_PHONE,
FAX = :new_FAX,
BILL_ADDRESS1 = :new_BILL_ADDRESS1,
BILL_ADDRESS2 = :new_BILL_ADDRESS2,
BILL_CITY = :new_BILL_CITY,
BILL_STATE_REGION = :new_BILL_STATE_REGION,
BILL_POSTAL_CODE = :new_BILL_POSTAL_CODE,
BILL_COUNTRY = :new_BILL_COUNTRY,
SHIP_ADDRESS1 = :new_SHIP_ADDRESS1,
SHIP_ADDRESS2 = :new_SHIP_ADDRESS2,
SHIP_CITY = :new_SHIP_CITY,
SHIP_STATE_REGION = :new_SHIP_STATE_REGION,
SHIP_POSTAL_CODE = :new_SHIP_POSTAL_CODE,
SHIP_COUNTRY = :new_SHIP_COUNTRY,
SHIP_SAME = :new_SHIP_SAME,
EMAIL = :new_EMAIL
WHERE
CUSTOMER_ID = :old_CUSTOMER_ID
%txt%
delete args
dim args as sql::arguments
args.set("old_customer_id",CurrentRecordPrimaryKey)
'set arguments to the value of the variables shown in the Xdialog
args.set("New_FIRSTNAME",vars.FIRSTNAME)
args.set("New_LASTNAME",vars.LASTNAME)
args.set("New_COMPANY",vars.COMPANY)
args.set("New_PHONE",vars.PHONE)
args.set("New_FAX",vars.FAX)
args.set("New_BILL_ADDRESS1",vars.BILL_ADDRESS1)
args.set("New_BILL_ADDRESS2",vars.BILL_ADDRESS2)
args.set("New_BILL_CITY",vars.BILL_CITY)
args.set("New_BILL_STATE_REGION",vars.BILL_STATE_REGION)
args.set("New_BILL_POSTAL_CODE",vars.BILL_POSTAL_CODE)
args.set("New_BILL_COUNTRY",vars.BILL_COUNTRY)
args.set("New_SHIP_ADDRESS1",vars.SHIP_ADDRESS1)
args.set("New_SHIP_ADDRESS2",vars.SHIP_ADDRESS2)
args.set("New_SHIP_CITY",vars.SHIP_CITY)
args.set("New_SHIP_STATE_REGION",vars.SHIP_STATE_REGION)
args.set("New_SHIP_POSTAL_CODE",vars.SHIP_POSTAL_CODE)
args.set("New_SHIP_COUNTRY",vars.SHIP_COUNTRY)
args.set("New_SHIP_SAME",vars.SHIP_SAME)
args.set("New_EMAIL",vars.EMAIL)
flag = cn.Execute(sqlUpdate,args)
if flag = .f. then
ui_msg_box("Error","Record was not updated. " + crlf()+ cn.CallResult.text)
exit function
end if
if cn.AffectedRows() = 0 then
ui_msg_box("Error","Record to update could not be found. Record was not updated.")
exit function
end if
ui_msg_box("Notice","Records updated: " + cn.AffectedRows())
end with
end function
function clearVariables as v (lv as p)
with lv
vars.CUSTOMER_ID = 0
vars.FIRSTNAME = ""
vars.LASTNAME = ""
vars.COMPANY = ""
vars.PHONE = ""
vars.FAX = ""
vars.BILL_ADDRESS1 = ""
vars.BILL_ADDRESS2 = ""
vars.BILL_CITY = ""
vars.BILL_STATE_REGION = ""
vars.BILL_POSTAL_CODE = ""
vars.BILL_COUNTRY = ""
vars.SHIP_ADDRESS1 = ""
vars.SHIP_ADDRESS2 = ""
vars.SHIP_CITY = ""
vars.SHIP_STATE_REGION = ""
vars.SHIP_POSTAL_CODE = ""
vars.SHIP_COUNTRY = ""
vars.SHIP_SAME = .f.
vars.EMAIL = ""
end with
end function
function newRecord as v (lv as p)
with lv
if flagRecordIsDirty = .t. then
dim q as n
q = ui_msg_box("Notice","Record has not been saved. Are you sure you want to lose your edits?",UI_YES_NO_CANCEL)
if q = ui_no_selected .or. q = ui_cancel_selected then
searchCustomerId = previousSearchCustomerId
exit function
end if
end if
flagRecordIsDirty = .f.
'need to set flagRecordFound to .t. so that the edit boxes on the dialog are enabled
flagRecordFound = .t.
mode = "New"
'set the combo box to show a null value
searchCustomerId = ""
clearVariables(local_variables())
'store original values in the 'origVars' dot variable in case the user wants to do an 'undoEdits' on the new record
'(which will get back to a blank new record).
property_recurse_assign(origVars,vars)
end with
end function
Comment