Subject Re: Build a New Software
From Akshat Kapoor <akshat.kapoor@kapoorsons.in>
Date Sun, 13 Sep 2020 22:03:03 +0530
Newsgroups dbase.getting-started
Attachment(s) ado_sample.wfm

On 13.09.2020 19:26, Joshua Oyeladun wrote:
> I want to build a Microfinance software in Nigeria need help on best practices to incorporate before. Starting
 > I want to use mysql hitnrun technique because it was formerly built with dbf table but has lot of limitations
 > Please advice me on new techniques on SMS and email
 >


Good Evening Joshua,
I will prefer the newsgroups unless it is something that involves
security issues. Then I switch to email.

Since you are starting from scratch I would recommend using backend
rdbms like firebird, MariaDB, MySQL etc.

There are more rdbms out there but these are the ones usually discussed
on the newsgroups.

Opt in for latest version so that you have the best updates, these rdbms
are not easy to upgrade (They can be upgraded but you cannot just
install the latest version over the older one)

First analyse the table structures. If you want change then start from
scratch, if you want to upgrade then make another copy of your
development folder and convert 1 form at a time.

Do not use BDE/BDE alais etc, Use adodatabase objects.
In his books Ken has described connecting Firebird using BDE but it can
be done through adodatabase objects also.

Steps for conversion :
Step 1
The first step would be to build a suitable connection string. For that
you will have to google or ask the database provider.

The following lines executed in command window or a prg are sufficient
to check a connection string.

adodb = new adodatabase()

adodb.connectionstring = "Driver={MariaDB ODBC 3.1
Driver};Server=localhost;Database=database;UID=root;PWD=;Port=3306;option=71303680"
//Watch for word wrap in above line
adodb.active = true

If there is an error it will pop up. If not proceed further.
I am using MariaDB so all connection strings will be tuned to that.

Step 2
Check for existence of primary key in all your tables. Primary key have
to be unique so that each and every row can be identified for updates.
At times autoincrement values make excellent primary keys

Step 3
Convert your forms reports etc. (This will only work if your table
structures are the same and you are upgrading) Start from the simplest.

All the validation checks etc will remain the same so it makes sense
changing existing forms.

At times it is easier to use the code editor to make the changes.
At times it is easier to build a totally new form copying the validation
checks etc from old form.
There can be no hard and fast rule.

I have attached a simple form developed to show the use of hit and run
techniques, using non datalinked entryfields etc.

I hope the above steps help.


There is another step that should be involved if the number of forms
involved is large.
It is how to avoid hard coding of connection string in each and every form.
Connection strings are not changed frequently but if changed then it
will be a PITA to update all forms.
If this is the case then I would request use of datamodules.

Set up datamodules with the adodatabase objects, adoquery objects.
In your environment setting portion (most of the developers use one to
set date formats/ exact on off / procedure files etc) create a public
variable containing connection string (for eg _app.connectionstring).

Use this datamodule in the form instead of individual query objects.

When designing of the form is complete open the datamodule in code
editor and change the line for connection string to

connectionstring = _app.connectionstring

This will have one advantage and two disadvantage.
Advantage : Changing the connection string at one place will change the
connection string in all forms.

Disadvantage : You will have to execute the setenvoirment prg before you
open the form in designer. (It will have to be executed only once when
you open the ide and not before every modi form command)

Disadvantage 2 : connectionstring = _app.connectionstring needs to be
updated everytime you open the datamodule in designer.
Even otherwise it is much easier to change sql in editor than using the
inspector.

This is the approach I have been using for the past 2½ years The
advantages are much more than the disadvantages.

More points when I remember them or if you ask them.

Regards
Akshat



/*
This form has been built to demonstrate just the following
1. Use of onNavigate event to populate values of non datalinked entryfields
2. Use of canNavigate to check for modifications as beginedit() etc are not being used.
3. Use of ADO to access tables directly bypassing the BDE
4. Use of executesql() to make changes to tables.

Since this is not a production form hence validation checks and other appearance
related enhancements are missing.

There is just a demo connection string. Please make suitable alterations according to your system

Data being inserted has ben taken from the dbasesamples customer table.

Initially only the close and connect buttons would be available.
Once a connection is established then other buttons will function

I have attached two functions to the end of the form.
They will come in handy when you opt in for your own form.
Escaping is an important aspect in RDBMS
If we have to save George Washington's  as part of chracter field
and ' is also used to enclose strings then 'George Washington's' will create
confusion with data Engine so it is converted into 'George Washington\'s' so that
the data engine knows it is an apostrophe and not a string terminator.

There are no date fields in this nor are there any logical fields.
These require special handling either in dbase or in sql but more on them later on

If you face any issues on these then please post on newsgroups.
If you have dived into the world of ADO then I would recommend that you brush
up your sql skills. There are many tutorials availale on the Web

Simple Insert, select, delete, update are a must
Joins etc are optional extra.
If you know them it will make your task easier in some cases
*/
set exact on
** END HEADER -- do not remove this line
//
// Generated on 26-08-2020
//
parameter bModal
local f
f = new ado_sampleForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class ado_sampleForm of FORM
   with (this)
      onOpen = class::FORM_ONOPEN
      height = 26.0455
      left = -0.1429
      top = 0.0
      width = 184.7143
      text = "Ado Sample form"
   endwith

   this.ADODATABASE1 = new ADODATABASE(this)
   with (this.ADODATABASE1)
      left = 10.0
      top = 5.0
      width = 10.0
      height = 1.0
   endwith

   this.ADOQUERY1 = new ADOQUERY(this)
   with (this.ADOQUERY1)
      left = 32.0
      top = 5.0
      width = 8.0
      height = 1.0
      database = form.adodatabase1
      sql = "select * from customers"
   endwith

   with (this.ADOQUERY1.rowset)
      onNavigate = class::ROWSET_ONNAVIGATE
   endwith

   this.EF_CONNECT_STR = new ENTRYFIELD(this)
   with (this.EF_CONNECT_STR)
      height = 1.0
      left = 20.0
      top = 0.5
      width = 139.0
      value = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=dbasesample;UID=root;PWD=;Port=3306;option=71303680"
      maxLength = 250
   endwith

   this.TL_CONNECT_STR = new TEXTLABEL(this)
   with (this.TL_CONNECT_STR)
      height = 1.0
      left = 1.0
      top = 0.5
      width = 18.0
      text = "Connection String "
   endwith

   this.PB_CONNECT = new PUSHBUTTON(this)
   with (this.PB_CONNECT)
      onClick = class::PB_CONNECT_ONCLICK
      height = 1.0909
      left = 5.0
      top = 2.5
      width = 21.0
      text = "&Connect Database"
   endwith

   this.PB_INSERT = new PUSHBUTTON(this)
   with (this.PB_INSERT)
      when = {||form.adodatabase1.active}
      onClick = class::PB_INSERT_ONCLICK
      height = 1.0909
      left = 30.1429
      top = 2.5
      width = 21.0
      text = "&Reset Data"
   endwith

   this.PB_INSERT_ROW = new PUSHBUTTON(this)
   with (this.PB_INSERT_ROW)
      when = {||form.adodatabase1.active}
      onClick = class::PB_INSERT_ROW_ONCLICK
      height = 1.0909
      left = 55.2857
      top = 2.5
      width = 21.0
      text = "&Insert Row"
   endwith

   this.PB_DELETE_ROW = new PUSHBUTTON(this)
   with (this.PB_DELETE_ROW)
      when = {||form.adodatabase1.active}
      onClick = class::PB_DELETE_ROW_ONCLICK
      height = 1.0909
      left = 80.4286
      top = 2.5
      width = 21.0
      text = "&Delete row"
   endwith

   this.PB_ABANDON = new PUSHBUTTON(this)
   with (this.PB_ABANDON)
      when = {||form.adodatabase1.active}
      onClick = class::PB_ABANDON_ONCLICK
      height = 1.0909
      left = 105.5714
      top = 2.5
      width = 21.0
      text = "&Abandon"
   endwith

   this.PB_CLOSE = new PUSHBUTTON(this)
   with (this.PB_CLOSE)
      onClick = {||form.close()}
      height = 1.0909
      left = 155.8571
      top = 2.5
      width = 21.0
      text = "C&lose"
   endwith

   this.DATA_CONTAINER = new CONTAINER(this)
   with (this.DATA_CONTAINER)
      enabled = false
      left = 2.0
      top = 4.5
      width = 180.0
      height = 21.0
   endwith

   this.DATA_CONTAINER.GRID1 = new GRID(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.GRID1)
      allowEditing = false
      anchor = 3        // Left
      pageno = 0
      height = 20.7273
      left = 0.0
      top = 0.0
      width = 58.5714
   endwith

   this.DATA_CONTAINER.EF_COMPANY = new ENTRYFIELD(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.EF_COMPANY)
      height = 1.0
      left = 103.5714
      top = 0.8636
      width = 25.0
      value = "Entryfield1"
      maxLength = 40
      pageno = 0
   endwith

   this.DATA_CONTAINER.EF_FNAME = new ENTRYFIELD(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.EF_FNAME)
      height = 1.0
      left = 103.5714
      top = 2.8636
      width = 25.0
      value = "Entryfield1"
      maxLength = 15
      pageno = 0
   endwith

   this.DATA_CONTAINER.EF_LNAME = new ENTRYFIELD(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.EF_LNAME)
      height = 1.0
      left = 103.5714
      top = 4.8636
      width = 25.0
      value = "Entryfield1"
      maxLength = 15
      pageno = 0
   endwith

   this.DATA_CONTAINER.EF_CITY = new ENTRYFIELD(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.EF_CITY)
      height = 1.0
      left = 103.5714
      top = 6.8636
      width = 25.0
      value = "Entryfield1"
      maxLength = 20
      pageno = 0
   endwith

   this.DATA_CONTAINER.TL_COMPANY = new TEXTLABEL(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.TL_COMPANY)
      height = 1.0
      left = 63.5714
      top = 0.8636
      width = 28.0
      text = "Company"
      pageno = 0
   endwith

   this.DATA_CONTAINER.TL_FNAME = new TEXTLABEL(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.TL_FNAME)
      height = 1.0
      left = 63.5714
      top = 2.8636
      width = 28.0
      text = "First Name"
      pageno = 0
   endwith

   this.DATA_CONTAINER.TL_LNAME = new TEXTLABEL(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.TL_LNAME)
      height = 1.0
      left = 63.5714
      top = 4.8636
      width = 28.0
      text = "Last Name"
      pageno = 0
   endwith

   this.DATA_CONTAINER.TL_CITY = new TEXTLABEL(this.DATA_CONTAINER)
   with (this.DATA_CONTAINER.TL_CITY)
      height = 1.0
      left = 63.5714
      top = 6.8636
      width = 28.0
      text = "City"
      pageno = 0
   endwith

   this.PB_SAVE = new PUSHBUTTON(this)
   with (this.PB_SAVE)
      when = {||form.adodatabase1.active}
      onClick = class::PB_SAVE_ONCLICK
      height = 1.0909
      left = 130.7143
      top = 2.5
      width = 21.0
      text = "&Save"
   endwith

   this.rowset = this.adoquery1.rowset

   function PB_ABANDON_onClick()
      form.data_container.ef_company.value = form.adoquery1.rowset.fields["company"].value
      form.data_container.ef_lname.value = form.adoquery1.rowset.fields["lastname"].value
      form.data_container.ef_fname.value = form.adoquery1.rowset.fields["firstname"].value
      form.data_container.ef_city.value = form.adoquery1.rowset.fields["city"].value
      return

   function PB_CONNECT_onClick()
      form.adodatabase1.connectionstring = ltrim(rtrim(form.ef_connect_str.value))
      form.adodatabase1.active = true
      this.enabled = false
      if not form.adodatabase1.tableexists('customers')
         /* I could have placed the create code and sample data and sent the backup
            But I preferred to give a sample of how to create tables using code.
            And the use of tableexists()
            
            Also note the use of Autoinc. It is available in MariaDB and MySQL
            But not in firebird (I may be wrong). It is only to serve as a unique row id
            required while updating / deleting rows
         */
         cmd = [CREATE TABLE customers (]
         cmd+= [Customerid INT(11) NOT NULL AUTO_INCREMENT primary key        ,]
         cmd+= [company CHAR(40) NOT NULL DEFAULT '',]
         cmd+= [Lastname CHAR(15) NOT NULL DEFAULT '',]
         cmd+= [firstname CHAR(15) NOT NULL DEFAULT '',]
         cmd+= [Phone CHAR(20) NOT NULL DEFAULT '' ,]
         cmd+= [Address1 CHAR(30) NOT NULL DEFAULT '' ,]
         cmd+= [Address2 CHAR(30) NOT NULL DEFAULT '' ,]
         cmd+= [city CHAR(20) NULL DEFAULT NULL ,]
         cmd+= [State CHAR(2) NULL DEFAULT null,]
         cmd+= [Zip CHAR(10) NULL DEFAULT NULL)]        
//         ?cmd
         //Uncomment the above line to view the sql built and debug it if you are getting errors.
         form.adodatabase1.executesql(cmd)
      endif
      form.adoquery1.active = true
      form.data_container.enabled = true
      form.data_container.grid1.datalink = form.adoquery1.rowset
      form.adoquery1.rowset.first()
      form.adoquery1.rowset.canNavigate = form.ROWSET_CANNAVIGATE
      /*
      Please take note of the sequence.
      Since the entryfields do not have any value assigning the cannavigate will
      cause undue popup of Save Alert.
      Hence Cannavigat is assigned once values are there.
      
      In case of datalinked entryfields this is not required as the values are
      assigned from the rowset by dbase automatically.
      */
      return

   function PB_DELETE_ROW_onClick()
      if msgbox("Do you want to delete the current row","Confirm Deletion",4)=6
         cmd = [delete from customers where customerid = ] + form.adoquery1.rowset.fields["customerid"].value
         form.adodatabase1.executesql(cmd)
         form.adoquery1.requery()
         //It would be interesting to comment out the above line and delete the row
         //It will not disappear from rowset as the rowset has not been updated.
         //It will disappear at requery()
         form.adoquery1.rowset.canNavigate = null
         form.adoquery1.rowset.first()
         form.adoquery1.rowset.canNavigate = form.ROWSET_CANNAVIGATE
      endif
      return

   function PB_INSERT_ROW_onClick()
      if form.adoquery1.rowset.cannavigate()
         form.data_container.ef_company.value = "    "
         form.data_container.ef_fname.value = "    "
         form.data_container.ef_lname.value = "    "
         form.data_container.ef_city.value = "    "
         form.append = true
      endif
      return

   function PB_INSERT_onClick()
      form.adodatabase1.executesql('delete from customers where 1')
      cmd = [Insert into customers (company,lastname,firstname,phone,address1,address2,city,state,zip) values ]
      cmd+= [('Spire Corporation','B','Arlette','(801) 534-1231','Cms, Inc.','','Salt Lake City','SD','01730')]
      cmd+= [,('Storage Technology Corp.','Cardwell','Bobbye','860 694 2647','Bldg 20','','Groton','OH','80028')]
      cmd+= [,('Halliburton Company','Hutchins','Pamala','410-823-6700','Ste. 805','Suite 6000','Towson','MI','77010')]
      cmd+= [,('California Water Service Group','Flowers','Bethany','520 746 7549','2305 East 19th St.','Jalan Ang Cheng Ho','Tucson','NJ','95112')]
      cmd+= [,('The Rouse Company','Doran','Kecia','(606)849-8471','R. 1  Box 107','','Hillsboro','NY','21044')]
      cmd+= [,('HomeFed Corporation','Arrington','Shellie','440 777 4569','3969 West 227 St.','Suite 1250','Fairview Park','KY','92008')]
      cmd+= [,('John H. Harland Company','Belcher','Chong','770 314-3065','2134 Sara Ashley Way','Suite 500','Lithonia','NJ','30035')]
      cmd+= [,('Rogers Corporation','Flint','Christiane','651 686 0405','3195 Neil Armstrong Blvd.','','Eagan','DC','06263')]
      cmd+= [,('International Electronics','Davila','Ellie','609 522 2075','1901 Surf Ave.','Bldg 3310','North Wildwood','IL','02021')]
      cmd+= [,('Security Investments Grp.','Aponte','Rufina','360-753-7690','3625 93rd Ave. Sw','','Olympia','CA','08360')]
      cmd+= [,('Raytheon Company','Ransom','Tamar','415-564-4763','2114 28th Avenue','','San Francisco','MD','02451')]
      cmd+= [,('Norstan, Inc.','Jeffers','Vergie','206-767-4827','8440 18th Ave Sw','Suite # 7','Seattle','NJ','55343')]
      cmd+= [,('Deere & Company','Hull','Lucile','208 672 7230','8948 West Barnes St.','Suite D','Boise','OR','61265')]
      cmd+= [,('Donegal Group Inc.','Weeks','Bianca','270-441-6010','6330 Waid Circle','Pharmacy - 119','Paducah','CA','17547')]
      cmd+= [,('Frozen Food Express Ind.','Goff','Dionne','260 724 8946','1001 West Commerce Dr.','Interior 5','Decatur','TX','75247')]
      cmd+= [,('Moore-Handley, Inc.','Trotter','Ileana','941 351 5073','7518 42nd Court East','Suite F','Sarasota','OR','35124')]
      cmd+= [,('First Horizon National Corporation','Stanton','Lynnette','916 364 1000 x107','3090 Fite Circle','Unit F','Sacramento','CA','38103')]
      cmd+= [,('AEP Industries','Wilson','Maria','931 431 3153','2489 Fort Campbell Blvd.','Amsam-rd-ws-id Bldg 7804','Clarksville','TN','07606')]
      cmd+= [,('Paulson Capital Corp.','Block','Stephani','301-657-3600','8808 Twin Creek Court','','Potomac','CA','97204')]
      cmd+= [,('The St. Joe Company','Mckinnon','Zaida','(423) 636-6216','2245 Fall Creek Rd','','Russellville','MA','32202')]
      cmd+= [,('Miracor Diagnostics, Inc.','Rangel','Emilie','404-299-9373','4708 Elam Forrest Dr','Suite 103','Stone Mountain','OK','92122')]
      cmd+= [,('Columbia Laboratories','Mosley','Maude','480 807 0724','4630 East Grandview St.','Lot 280','Mesa','NE','07039')]
      cmd+= [,('Suffolk Bancorp','Blalock','Dennise','(440) 256-1411','9030 Blue Jay Lane','','Kirtland','GA','11901')]
      cmd+= [,('Inter-Tel, Inc.','Forrest','Phillis','813 875 1095','4048 West Kennedy Blvd.','Hannon Hall Rm 32','Tampa','FL','85281')]
      cmd+= [,('NL Industries, Inc.','Sadler','Daniella','620-364-4064','1550 Oxen Lane N.e.','Room C240','Burlington','MI','75240')]
      cmd+= [,('Environmental Power Corporation','Cabrera','Darcy','815 226-9225','9766 Olas Lane','P.o.box 810','Rockford','CA','03801')]
      cmd+= [,('INEI Corporation','Worley','Samatha','919-929-8608','1602 Lucy Lane','11.00','Carrboro','NJ','20785')]
      cmd+= [,('Seitel, Inc.','Ryder','Alethea','414-764-2659','1977 East Montana Avenue','','Oak Creek','UT','77043')]
      cmd+= [,('Taro Pharmaceutical Industries Ltd.','Priest','Gabriele','407-895-5111','2111 East Michigan St.','','Orlando','IN','10532')]
      cmd+= [,('Trinity Industries, Inc.','High','Ayana','405 736 2619','Bldg 3113 Ne Corner','','Tinker AFB','VA','75207')]
      cmd+= [,('AutoInfo, Inc.','Chapman','Lucy','602 952 8967','3116 East Ocotillo Rd.','Suite 400','Phoenix','MD','33487')]
      cmd+= [,('COMARCO, Inc.','Fischer','Elva','317-897-1818','1927 West Plantation Row','Residence Inn','Greenfield','DC','92618')]
      cmd+= [,('Ronson Corporation','Pickens','Lai','708-425-5527','4041 99th Place','','Oak Lawn','CO','08875')]
      cmd+= [,('Sonesta International Hotels Corporation','Willoughb','Mathilda','920-794-1422','2508 36th Place','','Two Rivers','CA','02116')]
      cmd+= [,('Milastar Corporation','Biggs','Jeniffer','410-464-1104','6201 Loch Raven Boulevard','Suite 200','Baltimore','VA','55426')]
      cmd+= [,('Harrah\'s Entertainment, Inc.','Parson','Lyndsay','727-784-9630','1575 Owls Retreat','','Tarpon Springs','VA','89119')]
//    Please take note of \ in above line it is there to escape additional '  so that it is treated as an apostrophe and not as a delimiter
      cmd+= [,('Wyeth','Freeman','Thelma','509 534 3992','1010 Lake Rd.','3-chome','Spokane','WI','07940')]
      cmd+= [,('Kinder Morgan, Inc.','Pineda','Brenna','202-333-7497','1680 31st Street Nw','Taman Sg Besi Indah','WASHINGTON','CA','77002')]
      cmd+= [,('TouchStone Software Corporation','Pringle','Elodia','806-698-0055','6031 76th St.','','Lubbock','AZ','01845')]
      cmd+= [,('ESNI, Inc.','Cavanaugh','Fredia','510-845-5600','2464 West St','','Berkeley','OH','06611')]
      cmd+= [,('Virco Manufacturing Corp.','Montalvo','Glendora','415 771 1924','1930 Polk St.','','San Francisco','OH','90501')]
      cmd+= [,('Parlex Corporation','Neff','Jacinta','402-493-3313','3805 Polk St','','Omaha','MI','01844')]
      cmd+= [,('Banknorth Group, Inc.','Abel','Kristan','800-325-8232','2112 15th Street Nw','','Rochester','NY','04112')]
      cmd+= [,('New Valley Corp.','Lorenz','Lera','303 232 5297','1370 Carr St','','Lakewood','NY','33131')]
      cmd+= [,('Escalade, Inc.','Mcneil','Louisa','(727) 528-8840','5107 16th St N','Unit F','St Petersburg','HI','47992')]
      cmd+= [,('Educational Development','Berg','Neva','619 574 1678','1609 Fort Stockton Dr.','1430 Broadway','San Diego','NY','74146')]
      cmd+= [,('Cuisine Solutions, Inc.','Amaya','Norman','817.834.7098','3012 Bert St. Apt #402','','Haltom City','CA','22312')]
      cmd+= [,('A. Schulman, Inc.','Ludwig','Paris','858 677 9560','5375 Mira Sorrento Pl.','','San Diego','CT','44333')]
      cmd+= [,('Parkvale Financial Corp.','Dow','Sade','417-847-2221','1501 Main St.','','Cassville','OR','15146')]
      cmd+= [,('Fidelity Federal Bancorp','Mcfarland','Selena','208 331 5667','5987 West State St.','Suite 400','Boise','DC','47708')]
      cmd+= [,('Identix Incorporated','Beatty','Tessie','(613) 834-0049','2981 Ford Street Extension','Bonair Industrial Center','Ogdensburg','ID','55343')]
      cmd+= [,('Trimble Navigation Limited','Linn','Jesenia','252 972 9772','1502 West Thomas St.','','Rocky Mount','AZ','94085')]
      cmd+= [,('United Systems Technology','Urban','Joye','(909) 869-3032','3801 West Temple Ave','','Pomona','UT','75234')]
      cmd+= [,('ABC Dispensing Tech.','Bryant','Norma','847-431-0422','1125 Plum Tree Lane','Baker Library 6','Palatine','MA','44305')]
      cmd+= [,('Newpark Resources, Inc.','Grayson','Jenni','925-279-0290','2420 Tice Valley Boulevard','Suite 405','Walnut Creek','MD','70002')]
      cmd+= [,('CNS, Inc.','Cannon','Latoya','5029682020.00','5611 Fern Valley Rd.','Suite 521','Louisville','FL','55344')]
      cmd+= [,('Giga-tronics, Incorporated','Langley','Sharlene','480 483 2700','8687 East Via De Ventura','Apt21e','Scottsdale','CA','94583')]
      cmd+= [,('BWC Financial Corp.','Stanley','Colleen','4068928600.00','5565 Hwy2 W.','Apt. B','Columbia Falls','NV','94596')]
      cmd+= [,('West Pharmaceutical Services','Quarles','Samara','410 664 8930`','4800 East Wabash Ave.','','Baltimore','NJ','19341')]
      cmd+= [,('Southwall Technologies','Whatley','Mickie','801-561-5320','8045 S  700 E   Ste 7','','Sandy','CA','94303')]
      cmd+= [,('Linear Technology Corp.','Marrero','Sabina','305-274-4660','8301 Sw 108 Street','Dept 1316','Miami','OK','95035')]
      cmd+= [,('Camera Platforms International, Inc.','Maxwell','Kendra','360-396-1022','Nuwc Dk 610 Dowell St.','Apt 320','Keyport','MI','91605')]
      cmd+= [,('Nortel Networks Corporation','Hooker','Melony','305-757-0602 x 117','9076 Nw 112 Street','19th Floor','Hialeah Gardens','MA','L6T 5')]
      cmd+= [,('A.G. Edwards, Inc.','Mcintyre','Eve','903-534-3276','P.o. Box 1411','Taman Sg Besi Indah','Tyler','TX','63103')]
      cmd+= [,('Science Dynamics Corporation','Sherrill','Lyla','(336)727-2638','P.o. Box 2511','','Winston-Salem','KS','08109')]
      cmd+= [,('Mine Safety Appliances','Kay','Altagracia','918-495-5943','4500 So 129th East Ave','Jalan Ang Cheng Ho','Tulsa','PR','15238')]
      cmd+= [,('Wendt-Bristol Health Serv','Jacques','Andrew','305 228 5817','1197 Sw 132nd Place Circle','','miami','FL','43215')]
      cmd+= [,('Security National Financial Corp','Trejo','Janey','605/866-4495','Hc 3 Box 38','','Reva','OH','84123')]
      cmd+= [,('Warrantech Corporation','Foss','Carline','(405) 720-8024','5741 Nw 86th St','','OKLAHOMA CITY','TX','76021')]
      cmd+= [,('Puget Energy, Inc.','Bergman','Nella','903-967-3413','1250 Fm 778','','Quitman','TX','98004')]
      cmd+= [,('Raven Industries, Inc.','Cho','Amada','352 376 3034','4624 Nw 17th Pl.','','Gainesville','IL','57117')]
      cmd+= [,('Meridian Resource Corp.','Patten','Stephane','305 663 2721','4708 Sw 67th Ave. St #l3','','Miami','NY','77077')]
      cmd+= [,('Scott\'s Liquid Gold Inc.','Nunn','Dani','(715) 720-1020','P.o. Box 814','','Chippewa Falls','NY','80239')]
      cmd+= [,('Siliconix Incorporated','Daigle','Page','301 614 6011','Code 920 Bldg 33','','Greenbelt','NY','95054')]
      cmd+= [,('Candela Corporation','Guerrero','Ernestine','404 366 7550','5486 Old Dixie Hwy.','Dept 200','Forest Park','CA','01778')]
      cmd+= [,('Outlook Group Corp.','Belanger','Stephaine','850-874-0801','7402 Ray Glenn Circle','','Panama City','TX','54956')]
      cmd+= [,('Bio-Logic Systems Corp.','Hale','Daisy','703 587-5335','9300 Lee Highway','Suite 101','Fairfax','MI','60060')]
      cmd+= [,('Bairnco Corporation','Brewer','Jo','(201) 906-1066','1099 Van Houten Avenue','Suite 616','Clifton','OR','32746')]
      cmd+= [,('Seneca Foods Corporation','Humphries','Kandace','209-966-6277','2536 Old Hwy','','Cathys Valley','CA','14505')]
      cmd+= [,('Lincoln Logs Ltd.','Mcclendon','Joseph','(928) 776-1881','2054 Old Kettle Drive','Suite A','Prescott','MD','12817')]
      cmd+= [,('Computer Horizons Corp.','Garrison','Consuelo','435 797 1660','9100 Old Main Hill','Suite 400','Logan','CA','07046')]
      cmd+= [,('Coral Gold Corp.','Trevino','Joni','818-768-2513','7924 Via Magdalena','Amsam-rd-ws-id Bldg 7804','Burbank','MN','V6C 1')]
      cmd+= [,('Sunrise Technologies International, Inc.','Watt','Particia','865-694-6470','8707 - 2 Old Colony Trail','','Knoxville','MA','94538')]
      cmd+= [,('MGP Ingredients, Inc.','Gipson','Lulu','323 442-1640','1420 San Pablo #b105','Unit 1501','los angeles','TX','66002')]
      cmd+= [,('Deb Shops, Inc.','Ayers','Margo','505-662-6904','1105 Los Pueblos','Suite #200','Los Alamos','PA','19114')]
      cmd+= [,('AMCORE Financial, Inc.','Anderson','Dorothy','(847) 835-8040','1020 Oak Ridge Drive','Apt A','Glencoe','IL','61104')]
      cmd+= [,('Sun Bancorp, Inc.','Cottrell','Antonio','919-304-2803','4222 Elg Road','','Efland','NJ','17837')]
      cmd+= [,('PAR Technology Corp.','Randle','Velva','305-668-4276','7600 Red Road','','Coral Gables','TX','13413')]
      cmd+= [,('Liberty Homes, Inc.','Quintana','Idella','573-449-2646','1113 Fay Street','Dept 1316','Columbia','GA','46527')]
      cmd+= [,('Petroleum Development Corporation','Madrid','Gisele','707--678-4281','7800 Serpa Lane','','Dixon','PA','26330')]
      cmd+= [,('PacifiCare Health Systems','Engle','Maribeth','(404) 284-6995','1253 Celia Way','','Decatur','TX','90630')]
      cmd+= [,('Great American Financial','Kinsey','Regan','760-744-8300','1425 Linda Vista Drive','','San Marcos','FL','45202')]
      cmd+= [,('Mesa Air Group, Inc.','Blanco','Windy','719 638 1622','2555 Villa Loma','Hilton Head','Colorado Springs','CA','85008')]
      cmd+= [,('ProCyte Corporation','Bartley','Zula','805-239-9100','3025 Buena Vista Dr.','','Paso Robles','VA','98052')]
      cmd+= [,('CompuDyne Corporation','Schroeder','Dina','913-906-6000','2021 Massachusetts Avenue, Nw','Of 603 - Surco','Washington','CA','21076')]
      cmd+= [,('Interstate General Company L.P.','Mcgill','Jung','8106949390.00','8305 S. Saginaw Street','Apt 11-10','Grand Blanc','TX','20118')]
      cmd+= [,('Computer Network Technology','Shields','Rosetta','361-661-7000','1408 E. Main St.','Suite 400','Alice','CA','55442')]
      cmd+= [,('TELS Corporation','Dent','Chaya','619-725-7062','4100 Normal St.','','San Diego','VA','84003')]
      cmd+= [,('ABM Industries, Inc.','Fisher','Rosa','262 821 3555','1810 S. Calhoun Rd.','Allen-460','New Berlin','OH','94111')]
      cmd+= [,('CREDO Petroleum Corporation','Merritt','Kirsten','408-364-9200','6250 Tillamook Drive','2nd Fl','San Jose','NM','80202')]
      cmd+= [,('Ashland Inc.','Ryan','Carla','505.260.4770','4001 Indian School Road Ne','Unit F','Albuquerque','VA','41012')]
      form.adodatabase1.executesql(cmd)
      form.adoquery1.requery()
      //so that the rowset we have is updated
      return

   function PB_SAVE_onClick()
      //Ideally there should be a confirmation that he really wants to save
      //the changes but since this is just a demo hence skipping it.
      if form.append
         //Form is in append mode Insert row
         cmd = [insert into customers (company,firstname,lastname,city) values (']
         cmd+= escape_str(form.data_container.ef_company.value) + [',']
         cmd+= escape_str(form.data_container.ef_fname.value) + [',']
         cmd+= escape_str(form.data_container.ef_lname.value) + [',']
         cmd+= escape_str(form.data_container.ef_city.value) + [')]
         form.adodatabase1.executesql(cmd)
         form.adoquery1.requery()
         form.adoquery1.rowset.cannavigate = null
         form.adoquery1.rowset.last()
         form.pb_abandon_onclick()
         //Since there is no orderby in sql hence going to the last row is sufficient
         form.adoquery1.rowset.cannavigate = form.rowset_cannavigate
         //Setting cannavigate to null and then assigning it back is required
         //As the current data in the entryfields was not there in the rowset
      else
         //Form is in update mode update row
         cmd = [update customers set]
         cmd+= [ company = ']+escape_str(form.data_container.ef_company.value) + [',]
         cmd+= [ firstname = ']+escape_str(form.data_container.ef_fname.value) + [',]
         cmd+= [ lastname = ']+escape_str(form.data_container.ef_lname.value) + [',]
         cmd+= [ city = ']+escape_str(form.data_container.ef_city.value) + [']
         cmd+= [ where customerid =] + form.adoquery1.rowset.fields["customerid"].value
         row_id = form.adoquery1.rowset.fields["customerid"].value
         //Note the use of where clause in the above sql.
         //This is necessary so that only 1 row is updated and not all.
         form.adodatabase1.executesql(cmd)
         form.adoquery1.requery()
         //Ideally after requery rowset should reposition itself to the row on which it was there
         //Bookmarks are reset so the only way is to use applylocate
         form.adoquery1.rowset.applylocate('customerid ='+ row_id)
      endif
      return

   function form_onOpen()
      form.append = false
      //This will used to check while saving. If append mode then insert would be required
      //If not then update would be required.
      return

   function rowset_canNavigate()
      local retval
      retval = false
      if alltrim(this.fields["company"].value) = alltrim(this.parent.parent.data_container.ef_company.value) and ;
         alltrim(this.fields["firstname"].value) = alltrim(this.parent.parent.data_container.ef_fname.value) and ;
         alltrim(this.fields["lastname"].value) = alltrim(this.parent.parent.data_container.ef_lname.value) and ;
         alltrim(this.fields["city"].value) = alltrim(this.parent.parent.data_container.ef_city.value)
        
         retval = true
      else
         msgbox("You have unsaved changes either abandon them or save them before moving on to next row")
      endif
      return retval

   function rowset_onNavigate(type, nRows)
      this.parent.parent.data_container.ef_company.value = this.fields["company"].value
      this.parent.parent.data_container.ef_fname.value = this.fields["firstname"].value
      this.parent.parent.data_container.ef_lname.value = this.fields["lastname"].value
      this.parent.parent.data_container.ef_city.value = this.fields["city"].value
      return

endclass

function alltrim()
        parameters mstring
        local retval
        if mstring = null
                retval = ""
        else
                retval =ltrim(trim(" "+mstring))
        endif
   //This check for null is required as at times odbc driver returns Nulls where just spaces are there
        return retval

Function escape_str()
   parameter esc_str
   local ret_str
   /*This function adds slashes so that
   St's is converted to St\'s
   For further details please Google escaping sql strings
   */
   ret_str = esc_str
   if ['] $ esc_str or ["] $ esc_str or [\] $ esc_str or [$] $ esc_str
      for n = len(ret_str) to 1 step -1
        if substr(ret_str,n,1)$['\$"]
          ret_str = stuff(ret_str,n,0,'\')
        endif
      next
   endif
   return ret_str