delphi - Query a SQL Database & Edit the found data set -

i know question has been asked before can't manage mine going. set sql have 2 tables in instance using 1 called 'book'. has various columns ones want work called 'wr', 'customer', 'contact', 'model', 'sn', 'status', 'tech', 'wdone' , 'in'.

i want enter text editbox called edtwr , want button btnsearch search 'wr' column until has match (all of entries different). once has must write 'customer', 'contact', 'model', 'sn', 'status' labels, lets call them lblcustomer lblcontact lblmodel lblsn & lblstatus.

once person has verified that 'wr' want must enter text edit boxes , 1 memo called edttech, mmowdone , edtin , click on btnupdate. should update record.

i have 3 ado connections on called dtbout thats adoconnection1, tableout thats adotable , dataout thats adodataset. dataout's command text select * book if helps.

i can whole process work on access database no experience on sql need help. add code access database in case needed reference.

 procedure tfout.btnsearchclick(sender: tobject);  begin      dataout.filter := 'wr = ''' + 'wr ' + edtwr.text + '''';      dataout.filtered := true;      dataout.first;       lblcustomer.caption := 'customer: ' + dataout.fieldbyname('customer').asstring;      lblcontact.caption := 'contact: ' + dataout.fieldbyname('contact').asstring;       lblsn.caption := 'sn: ' + dataout.fieldbyname('sn').asstring;      lblmodel.caption := 'model: ' + dataout.fieldbyname('model').asstring;      lblstatus.caption := 'status: ' + dataout.fieldbyname('status').asstring;   procedure tfout.btnupdateclick(sender: tobject); begin    dataout.edit;    dataout.fieldbyname('tech').asstring := edttech.text;    dataout.fieldbyname('wdone').asstring := mmowdone.lines.gettext;    dataout.fieldbyname('in').asstring  := edtin.text;    dataout.post; end 

do need additional components on form me able in sql, need , how start. ive read lot of things , seems line need adoquery1 when comes adoquery1.sql part fall off wagon. have tried access way , can search try update "insufficient key column information updating or refreshing" error, witch have no idea how address.

if need state question otherwise, please explain how change make more clear , if need add in whole explanation or code, please inform me of what.

so isn't place database tutorials, i'm not going attempt 1 instead focus on 1 basic thing it's crucial understand , right in database design before begin write delphi db app. (i'm going talk in terms of sql server, not ms access.)

you mentioned getting error "insufficient key column information updating or refreshing" said had no idea how address.

a delphi dataset (of sort, not ado one) operates maintaining logical cursor which points @ 1 row in dataset. when open (non-empty) dataset, cursor pointing @ first row in dataset , can move cursor around using various tdataset methods such next & prior, first, last , moveby. some, not all, types of tdataset implement locate method enables go row matches criteria specify, other types, not. delphi's ado components do implement locate (btw, locate operates on rows you're retrieved server, it's not finding rows on server).

one of key ideas of sql-oriented tdatasets such tadoquery can leave automatically generate sql statements updates, deletes , inserts. not significant productivity aid, avoids coding errors , omissions when try yourself.

if observe ado doing stuff against ms sql server table using ss's profiler utility, well-designed database, you'll find quite nicely , efficiently provided database design follows 1 cardinal rule, namely there must way uniquely identify particular row in table. common way include in each table, first column, int(eger) id column, , define "primary key" of table. although there other methods generate suitable id value go in column, sql server has specific column type, 'identity' takes care of on server.

once table has such column, ado layer (which data-access layer provided windows dataset components such tadoquery sit upon) can automatically generate sql statements updates , deletes, e.g.

delete table1 table1id = 999 

and

update table1 set somecharfield = 'somevalue' table1id = 666 

and can leave adoquery pick id value newly-inserted row server.

one of helpful aspects of leaving sql generated automatically ensures sql affects single row , avoids affecting more rows intend.

once you've got key aspect of database design correct, you'll find delphi's tdataset descendants such tadoquery , db-aware components can deal simple database applications without having write sql statements @ update, insert or delete rows. usually, however, still need write sql statements retrieve rows want server using 'where' clause restrict rows retrieved sub-set of rows on server.

maybe next step should read on parameterized sql queries, reduce exposure "sql injection":

https://en.wikipedia.org/wiki/sql_injection

as it's best habit of writing sql queries using parameters. btw, sql injection isn't sql being intercepted , modified when it's sent on internet: there forms of injection malicious user knows they're doing can type in sql statements app "expects" them specify column value search criterion.