/* 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