Sign in Join
Wiki copytemporary

TSqlite3 Master Detail Example: what changed

Revision 2 to 3. Removed lines have a minus sign; added lines have a plus sign.

OldNewLine
11TSqlite3 Master Detail Example
22
33## About Demo
44
55This demo is a working example of how to use the [[SQLite]] TSqlite3 dataset component in a master detail relationship.
66
77> **Note:** this component is not part of the general SQLDB database components. SQLDB provides TSQLite3Connection that match other databases connectors. You are strongly recommended to look into using TSQLite3Connection because of the ease of switching to other databases; only use TSQlite3 if it is really needed.
88
99See [[MasterDetail]] for instructions on how to implement master/detail relationships using standard sqldb (e.g. sqlite3) components.
1010
1111## Author
1212
1313David Stewart .. davesimplewear at yahoo dot com
1414
1515## Components Used
1616
1717- TSqlite3
1818- Standard Lazarus database components
1919
2020## Licence
2121
2222- Free to use as you will
2323
2424## Download
2525
2626The TSQLite3 example can be downloaded from [The Lazarus -ccr sf download location](http://sourceforge.net/projects/lazarus-ccr/files/Demos%20and%20examples/). Also from [David's Freeware](http://www.users.on.net/~dave.stewart/index.html).
2727
2828### Screen shots
2929
3030![](/wiki-images/b/b8/Screenshot-Master_Detail_Example_-_SQLLite3.png)
3131
3232![](/wiki-images/8/82/Screenshot-Master_Detail_Example_-_SQLLite3-1.png)
3333
3434![](/wiki-images/7/71/Screenshot-Master_Detail_Example_-_SQLLite3-2.png)
3535
3636![](/wiki-images/1/14/Screenshot-Select_Customer_Name.png)
3737
3838![](/wiki-images/2/2e/Screenshot-Select_Item_Number.png)
3939
4040This screenshot shows the table setting for master detail
4141
4242![](/wiki-images/e/e6/Screenshot.png)
4343
4444### Example program code
4545
4646``` pascal
4747unit uMain;
4848
4949{$mode objfpc}{$H+}
5050
5151interface
5252
5353uses
5454 Classes, SysUtils, db, sqlite3ds, FileUtil, LResources, Forms, Controls,
5555 Graphics, Dialogs, ComCtrls, ExtCtrls, Menus, DbCtrls, StdCtrls, DBGrids;
5656
5757type
5858
5959 { TfMain }
6060
6161 TfMain = class(TForm)
6262 btnSelCust: TButton;
6363 btnAddSale: TButton;
6464 btnSaveEntry: TButton;
6565 btnDelEntry: TButton;
6666 DBNavigator3: TDBNavigator;
6767 dsCust: TDatasource;
6868 dsSales: TDatasource;
6969 dsStock: TDatasource;
7070 DBEdit1: TDBEdit;
7171 DBEdit10: TDBEdit;
7272 DBEdit12: TDBEdit;
7373 DBEdit13: TDBEdit;
7474 DBEdit14: TDBEdit;
7575 DBEdit15: TDBEdit;
7676 DBEdit3: TDBEdit;
7777 DBEdit4: TDBEdit;
7878 DBEdit5: TDBEdit;
7979 DBEdit6: TDBEdit;
8080 DBEdit7: TDBEdit;
8181 DBEdit8: TDBEdit;
8282 DBEdit9: TDBEdit;
8383 dgSales: TDBGrid;
8484 DBNavigator1: TDBNavigator;
8585 DBNavigator2: TDBNavigator;
8686 Label1: TLabel;
8787 Label10: TLabel;
8888 Label11: TLabel;
8989 Label2: TLabel;
9090 Label3: TLabel;
9191 Label4: TLabel;
9292 Label5: TLabel;
9393 Label7: TLabel;
9494 Label8: TLabel;
9595 Label9: TLabel;
9696 miClose: TMenuItem;
9797 miFile: TMenuItem;
9898 mmMain: TMainMenu;
9999 nbMain: TNotebook;
100100 Panel1: TPanel;
101101 pnlSales: TPanel;
102102 pnlStock: TPanel;
103103 pnlCustomer: TPanel;
104104 pnlSelectCust: TPanel;
105105 pStock: TPage;
106106 pCustomer: TPage;
107107 pSales: TPage;
108108 sbMain: TStatusBar;
109109 TCustAddr: TStringField;
110110 TCustCustName: TStringField;
111111 TCustcustState: TStringField;
112112 TCustID: TAutoIncField;
113113 TCustpostCode: TStringField;
114114 TCustSuburb: TStringField;
115115 TSalescustID: TLongintField;
116116 TSalesID: TAutoIncField;
117117 TSalesitem: TStringField;
118118 TSalesitemNum: TStringField;
119119 TSalesprice: TFloatField;
120120 TSalessaleDate: TDateField;
121121 TSalesshipDate: TDateField;
122122 TStock: TSqlite3Dataset;
123123 TSales: TSqlite3Dataset;
124124 TCust: TSqlite3Dataset;
125125 TStockID: TAutoIncField;
126126 TStockitem: TStringField;
127127 TStockitemNum: TStringField;
128128 TStockprice: TFloatField;
129129 procedure btnAddSaleClick(Sender: TObject);
130130 procedure btnDelEntryClick(Sender: TObject);
131131 procedure btnSaveEntryClick(Sender: TObject);
132132 procedure btnSelCustClick(Sender: TObject);
133133 procedure dgSalesEditButtonClick(Sender: TObject);
134134 procedure FormCloseQuery(Sender: TObject; var CanClose: boolean);
135135 procedure FormCreate(Sender: TObject);
136136 procedure FormDestroy(Sender: TObject);
137137 procedure FormShow(Sender: TObject);
138138 procedure miCloseClick(Sender: TObject);
139139 private
140140 { private declarations }
141141 public
142142 { public declarations }
143143 end;
144144
145145var
146146 fMain: TfMain;
147147
148148implementation
149149uses uCust, uSales;
150150{ TfMain }
151151
152152procedure TfMain.miCloseClick(Sender: TObject);
153153begin
154154 Close;
155155end;
156156
157157procedure TfMain.FormCloseQuery(Sender: TObject; var CanClose: boolean);
158158begin
159159 CanClose := MessageDlg('Are You Sure ?',mtConfirmation,[mbYes,mbNo],0)=mrYes;
160160end;
161161
162162procedure TfMain.FormCreate(Sender: TObject);
163163var
164164n:integer;
165165c:TComponent;
166166FName:string;
167167begin
168168 fName := ExtractFilePath(ParamStr(0)) +'data/md.db3';
169169for n := 0 to ComponentCount -1 do
170170 begin
171171 c := Components[n];
172172 if c is TSqlite3Dataset then
173173 TSqlite3Dataset(c).FileName:= fName;
174174 end;
175175 for n := 0 to ComponentCount -1 do
176176 begin
177177 c := Components[n];
178178 if c is TSqlite3Dataset then
179179 TSqlite3Dataset(c).Open;
180180 end;
181181end;
182182
183183procedure TfMain.FormDestroy(Sender: TObject);
184184var
185185n:integer;
186186c:TComponent;
187187begin
188188 for n := 0 to ComponentCount -1 do
189189 begin
190190 c := Components[n];
191191 if c is TSqlite3Dataset then
192192 TSqlite3Dataset(c).Close;
193193 end;
194194end;
195195
196196procedure TfMain.FormShow(Sender: TObject);
197197begin
198198 nbMain.PageIndex:=0;
199199end;
200200
201201procedure TfMain.dgSalesEditButtonClick(Sender: TObject);
202202begin
203203 if SearchDlg.ShowModalParts =mrOk then
204204 begin
205205 TSales.Edit;
206206 TSalesItemNum.Value := SearchDlg.PartNum;
207207 TSalesItem.Value := TStockitem.Value;
208208 TSalesPRICE.Value:= TStockPrice.Value;
209209 end;
210210end;
211211
212212procedure TfMain.btnSelCustClick(Sender: TObject);
213213begin
214214 custDlg.CustName := TCustCUSTNAME.Value ;
215215 if CustDlg.ShowModalCust =mrOk then
216216 begin
217217 TCust.Edit;
218218 TCustCUSTNAME.Value := custDlg.CustName;
219219 end;
220220end;
221221
222222procedure TfMain.btnAddSaleClick(Sender: TObject);
223223begin
224224 TSales.Append;
225225end;
226226
227227procedure TfMain.btnDelEntryClick(Sender: TObject);
228228begin
229229 TSales.Delete;
230230end;
231231
232232procedure TfMain.btnSaveEntryClick(Sender: TObject);
233233begin
234234 TSales.ApplyUpdates;
235235end;
236236
237237initialization
238238 {$I uMain.lrs}
239239
240240end.
241241```
242242
243243uMain.lfm (edited)
244244
245-``` pascal
245+```lfm
246246object fMain: TfMain
247247 Left = 395
248248 Height = 332
249249 Top = 218
250250 Width = 534
251251 ActiveControl = nbMain
252252 BorderIcons = [biSystemMenu, biMinimize]
253253 BorderStyle = bsSingle
254254 Caption = 'Master Detail Example - SQLLite3'
255255 ClientHeight = 305
256256 ClientWidth = 534
257257 Font.Height = -13
258258 Font.Name = 'Sans'
259259 Menu = mmMain
260260 OnCloseQuery = FormCloseQuery
261261 OnCreate = FormCreate
262262 OnDestroy = FormDestroy
263263 OnShow = FormShow
264264 Position = poScreenCenter
265265 LCLVersion = '0.9.27'
266266 object sbMain: TStatusBar
267267 Left = 0
268268 Height = 19
269269 Top = 286
270270 Width = 534
271271 AutoHint = True
272272 Panels = <>
273273 end
274274 object nbMain: TNotebook
275275 Left = 0
276276 Height = 286
277277 Top = 0
278278 Width = 534
279279 Align = alClient
280280 PageIndex = 0
281281 TabOrder = 1
282282 object pSales: TPage
283283 Caption = 'Sales'
284284 ClientWidth = 532
285285 ClientHeight = 259
286286 object Label3: TLabel
287287 Left = 0
288288 Height = 18
289289 Top = 241
290290 Width = 532
291291 Align = alBottom
292292 Caption = 'Select Customer First, then click in item number field to select item, then save'
293293 ParentColor = False
294294 end
295295 object pnlSelectCust: TPanel
296296 Left = 15
297297 Height = 216
298298 Top = 14
299299 Width = 232
300300 BevelInner = bvLowered
301301 BevelWidth = 2
302302 ClientHeight = 216
303303 ClientWidth = 232
304304 TabOrder = 0
305305 object Label1: TLabel
306306 Left = 4
307307 Height = 18
308308 Top = 4
309309 Width = 224
310310 Align = alTop
311311 Alignment = taCenter
312312 Caption = 'Customer Detail'
313313 Font.Height = -13
314314 Font.Name = 'Sans'
315315 Font.Style = [fsBold, fsItalic]
316316 ParentColor = False
317317 ParentFont = False
318318 end
319319 object Label2: TLabel
320320 Left = 24
321321 Height = 18
322322 Top = 31
323323 Width = 73
324324 Caption = 'First Name'
325325 Font.Height = -13
326326 Font.Name = 'Sans'
327327 Font.Style = [fsItalic]
328328 ParentColor = False
329329 ParentFont = False
330330 end
331331 object DBEdit1: TDBEdit
332332 Left = 24
333333 Height = 23
334334 Hint = 'Cust name'
335335 Top = 48
336336 Width = 184
337337 DataField = 'CustName'
338338 DataSource = dsCust
339339 ReadOnly = True
340340 MaxLength = 8192
341341 ParentShowHint = False
342342 ShowHint = True
343343 TabOrder = 0
344344 end
345345 object DBEdit3: TDBEdit
346346 Left = 24
347347 Height = 23
348348 Hint = 'Address'
349349 Top = 72
350350 Width = 184
351351 DataField = 'Addr'
352352 DataSource = dsCust
353353 ReadOnly = True
354354 MaxLength = 8192
355355 ParentShowHint = False
356356 ShowHint = True
357357 TabOrder = 1
358358 end
359359 object DBEdit4: TDBEdit
360360 Left = 24
361361 Height = 23
362362 Hint = 'Suburb/Town'
363363 Top = 96
364364 Width = 184
365365 DataField = 'Suburb'
366366 DataSource = dsCust
367367 ReadOnly = True
368368 MaxLength = 8192
369369 ParentShowHint = False
370370 ShowHint = True
371371 TabOrder = 2
372372 end
373373 object DBEdit5: TDBEdit
374374 Left = 24
375375 Height = 23
376376 Hint = 'Postal Code'
377377 Top = 120
378378 Width = 80
379379 DataField = 'postCode'
380380 DataSource = dsCust
381381 ReadOnly = True
382382 MaxLength = 8192
383383 ParentShowHint = False
384384 ShowHint = True
385385 TabOrder = 3
386386 end
387387 object DBEdit6: TDBEdit
388388 Left = 128
389389 Height = 23
390390 Hint = 'State'
391391 Top = 119
392392 Width = 80
393393 DataField = 'custState'
394394 DataSource = dsCust
395395 ReadOnly = True
396396 CharCase = ecUppercase
397397 MaxLength = 8192
398398 ParentShowHint = False
399399 ShowHint = True
400400 TabOrder = 4
401401 end
402402 object btnSelCust: TButton
403403 Left = 24
404404 Height = 25
405405 Hint = 'Click to select a Customer'
406406 Top = 176
407407 Width = 184
408408 Caption = 'Select Customer'
409409 OnClick = btnSelCustClick
410410 ParentShowHint = False
411411 ShowHint = True
412412 TabOrder = 5
413413 end
414414 object DBNavigator3: TDBNavigator
415415 Left = 30
416416 Height = 22
417417 Top = 150
418418 Width = 170
419419 BevelOuter = bvNone
420420 ClientHeight = 22
421421 ClientWidth = 170
422422 DataSource = dsCust
423423 TabOrder = 6
424424 VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast]
425425 end
426426 end
427427 object pnlSales: TPanel
428428 Left = 255
429429 Height = 216
430430 Top = 14
431431 Width = 260
432432 BevelInner = bvLowered
433433 BevelWidth = 2
434434 ClientHeight = 216
435435 ClientWidth = 260
436436 TabOrder = 1
437437 object Label4: TLabel
438438 Left = 4
439439 Height = 18
440440 Top = 4
441441 Width = 252
442442 Align = alTop
443443 Alignment = taCenter
444444 Caption = 'Sales Detail'
445445 Font.Height = -13
446446 Font.Name = 'Sans'
447447 Font.Style = [fsBold, fsItalic]
448448 ParentColor = False
449449 ParentFont = False
450450 end
451451 object btnAddSale: TButton
452452 Left = 16
453453 Height = 25
454454 Hint = 'Add Another Sale'
455455 Top = 176
456456 Width = 72
457457 Caption = 'Add Sale'
458458 OnClick = btnAddSaleClick
459459 ParentShowHint = False
460460 ShowHint = True
461461 TabOrder = 0
462462 end
463463 object Panel1: TPanel
464464 Left = 4
465465 Height = 13
466466 Top = 22
467467 Width = 252
468468 Align = alTop
469469 BevelOuter = bvNone
470470 TabOrder = 1
471471 end
472472 object dgSales: TDBGrid
473473 Left = 4
474474 Height = 125
475475 Hint = 'Click Item Number Button to Select Item'
476476 Top = 35
477477 Width = 252
478478 Align = alTop
479479 Columns = <
480480 item
481481 ButtonStyle = cbsEllipsis
482482 Title.Caption = 'item Number'
483483 Width = 100
484484 FieldName = 'itemNum'
485485 end
486486 item
487487 Width = 150
488488 FieldName = 'item'
489489 end
490490 item
491491 FieldName = 'price'
492492 end
493493 item
494494 Title.Caption = 'sale Date'
495495 FieldName = 'saleDate'
496496 end
497497 item
498498 Title.Caption = 'ship Date'
499499 FieldName = 'shipDate'
500500 end>
501501 DataSource = dsSales
502502 ShowHint = True
503503 TabOrder = 2
504504 TitleFont.Height = -13
505505 TitleFont.Name = 'Sans'
506506 OnEditButtonClick = dgSalesEditButtonClick
507507 end
508508 object btnSaveEntry: TButton
509509 Left = 95
510510 Height = 25
511511 Hint = 'Save Entry'
512512 Top = 176
513513 Width = 51
514514 Caption = 'Save'
515515 OnClick = btnSaveEntryClick
516516 ParentShowHint = False
517517 ShowHint = True
518518 TabOrder = 3
519519 end
520520 object btnDelEntry: TButton
521521 Left = 168
522522 Height = 25
523523 Hint = 'Delete Entry'
524524 Top = 176
525525 Width = 75
526526 Caption = 'Delete'
527527 OnClick = btnDelEntryClick
528528 ParentShowHint = False
529529 ShowHint = True
530530 TabOrder = 4
531531 end
532532 end
533533 end
534534 object pCustomer: TPage
535535 Caption = 'Customer'
536536 ClientWidth = 532
537537 ClientHeight = 259
538538 object pnlCustomer: TPanel
539539 Left = 95
540540 Height = 194
541541 Top = 30
542542 Width = 339
543543 BevelInner = bvLowered
544544 BevelWidth = 2
545545 ClientHeight = 194
546546 ClientWidth = 339
547547 TabOrder = 0
548548 object Label5: TLabel
549549 Left = 72
550550 Height = 18
551551 Top = 31
552552 Width = 73
553553 Caption = 'First Name'
554554 Font.Height = -13
555555 Font.Name = 'Sans'
556556 Font.Style = [fsItalic]
557557 ParentColor = False
558558 ParentFont = False
559559 end
560560 object Label7: TLabel
561561 Left = 4
562562 Height = 18
563563 Top = 4
564564 Width = 331
565565 Align = alTop
566566 Alignment = taCenter
567567 Caption = 'Customer Entry'
568568 Font.Height = -13
569569 Font.Name = 'Sans'
570570 Font.Style = [fsBold, fsItalic]
571571 ParentColor = False
572572 ParentFont = False
573573 end
574574 object DBEdit10: TDBEdit
575575 Left = 72
576576 Height = 23
577577 Hint = 'Cust name'
578578 Top = 48
579579 Width = 184
580580 DataField = 'CustName'
581581 DataSource = dsCust
582582 MaxLength = 8192
583583 ParentShowHint = False
584584 ShowHint = True
585585 TabOrder = 0
586586 end
587587 object DBEdit12: TDBEdit
588588 Left = 72
589589 Height = 23
590590 Hint = 'Address'
591591 Top = 72
592592 Width = 184
593593 DataField = 'Addr'
594594 DataSource = dsCust
595595 MaxLength = 8192
596596 ParentShowHint = False
597597 ShowHint = True
598598 TabOrder = 1
599599 end
600600 object DBEdit13: TDBEdit
601601 Left = 72
602602 Height = 23
603603 Hint = 'Suburb/Town'
604604 Top = 96
605605 Width = 184
606606 DataField = 'Suburb'
607607 DataSource = dsCust
608608 MaxLength = 8192
609609 ParentShowHint = False
610610 ShowHint = True
611611 TabOrder = 2
612612 end
613613 object DBEdit14: TDBEdit
614614 Left = 72
615615 Height = 23
616616 Hint = 'Postal Code'
617617 Top = 120
618618 Width = 80
619619 DataField = 'postCode'
620620 DataSource = dsCust
621621 MaxLength = 8192
622622 ParentShowHint = False
623623 ShowHint = True
624624 TabOrder = 3
625625 end
626626 object DBEdit15: TDBEdit
627627 Left = 176
628628 Height = 23
629629 Hint = 'State'
630630 Top = 119
631631 Width = 80
632632 DataField = 'custState'
633633 DataSource = dsCust
634634 CharCase = ecUppercase
635635 MaxLength = 8192
636636 ParentShowHint = False
637637 ShowHint = True
638638 TabOrder = 4
639639 end
640640 object DBNavigator2: TDBNavigator
641641 Left = 45
642642 Height = 25
643643 Top = 154
644644 Width = 241
645645 BevelOuter = bvNone
646646 ClientHeight = 25
647647 ClientWidth = 241
648648 DataSource = dsCust
649649 TabOrder = 5
650650 end
651651 end
652652 end
653653 object pStock: TPage
654654 Caption = 'Stock'
655655 ClientWidth = 532
656656 ClientHeight = 259
657657 object pnlStock: TPanel
658658 Left = 95
659659 Height = 218
660660 Top = 22
661661 Width = 339
662662 BevelInner = bvLowered
663663 BevelWidth = 2
664664 ClientHeight = 218
665665 ClientWidth = 339
666666 TabOrder = 0
667667 object Label8: TLabel
668668 Left = 4
669669 Height = 18
670670 Top = 4
671671 Width = 331
672672 Align = alTop
673673 Alignment = taCenter
674674 Caption = 'Stock Entry'
675675 Font.Height = -13
676676 Font.Name = 'Sans'
677677 Font.Style = [fsBold, fsItalic]
678678 ParentColor = False
679679 ParentFont = False
680680 end
681681 object Label9: TLabel
682682 Left = 75
683683 Height = 18
684684 Top = 24
685685 Width = 83
686686 Caption = 'Part Number'
687687 Font.Height = -13
688688 Font.Name = 'Sans'
689689 Font.Style = [fsItalic]
690690 ParentColor = False
691691 ParentFont = False
692692 end
693693 object Label10: TLabel
694694 Left = 75
695695 Height = 18
696696 Top = 72
697697 Width = 75
698698 Caption = 'Description'
699699 Font.Height = -13
700700 Font.Name = 'Sans'
701701 Font.Style = [fsItalic]
702702 ParentColor = False
703703 ParentFont = False
704704 end
705705 object Label11: TLabel
706706 Left = 75
707707 Height = 18
708708 Top = 128
709709 Width = 33
710710 Caption = 'Price'
711711 Font.Height = -13
712712 Font.Name = 'Sans'
713713 Font.Style = [fsItalic]
714714 ParentColor = False
715715 ParentFont = False
716716 end
717717 object DBEdit7: TDBEdit
718718 Left = 75
719719 Height = 23
720720 Top = 40
721721 Width = 181
722722 DataField = 'itemNum'
723723 DataSource = dsStock
724724 MaxLength = 8192
725725 ParentShowHint = False
726726 ShowHint = True
727727 TabOrder = 0
728728 end
729729 object DBEdit8: TDBEdit
730730 Left = 75
731731 Height = 23
732732 Top = 89
733733 Width = 181
734734 DataField = 'item'
735735 DataSource = dsStock
736736 MaxLength = 8192
737737 ParentShowHint = False
738738 ShowHint = True
739739 TabOrder = 1
740740 end
741741 object DBEdit9: TDBEdit
742742 Left = 75
743743 Height = 23
744744 Top = 145
745745 Width = 181
746746 DataField = 'price'
747747 DataSource = dsStock
748748 ParentShowHint = False
749749 ShowHint = True
750750 TabOrder = 2
751751 end
752752 object DBNavigator1: TDBNavigator
753753 Left = 48
754754 Height = 25
755755 Top = 183
756756 Width = 241
757757 BevelOuter = bvNone
758758 ClientHeight = 25
759759 ClientWidth = 241
760760 DataSource = dsStock
761761 ParentShowHint = False
762762 ShowHint = True
763763 TabOrder = 3
764764 end
765765 end
766766 end
767767 end
768768 object mmMain: TMainMenu
769769 left = 16
770770 top = 277
771771 object miFile: TMenuItem
772772 Caption = '&File'
773773 object miClose: TMenuItem
774774 Caption = '&Close'
775775 GlyphShowMode = gsmAlways
776776 Hint = 'Close Application'
777777 OnClick = miCloseClick
778778 end
779779 end
780780 end
781781 object dsCust: TDatasource
782782 DataSet = TCust
783783 left = 16
784784 top = 53
785785 end
786786 object dsSales: TDatasource
787787 DataSet = TSales
788788 left = 16
789789 top = 141
790790 end
791791 object dsStock: TDatasource
792792 DataSet = TStock
793793 left = 16
794794 top = 214
795795 end
796796 object TCust: TSqlite3Dataset
797797 AutoIncrementKey = True
798798 Options = []
799799 PrimaryKey = 'ID'
800800 SaveOnClose = True
801801 SaveOnRefetch = True
802802 SQL = 'Select * from cust;'
803803 TableName = 'cust'
804804 FieldDefs = <
805805 item
806806 Name = 'ID'
807807 DataType = ftAutoInc
808808 Precision = -1
809809 Size = 0
810810 end
811811 item
812812 Name = 'CustName'
813813 DataType = ftString
814814 Precision = -1
815815 Size = 8192
816816 end
817817 item
818818 Name = 'Addr'
819819 DataType = ftString
820820 Precision = -1
821821 Size = 8192
822822 end
823823 item
824824 Name = 'Suburb'
825825 DataType = ftString
826826 Precision = -1
827827 Size = 8192
828828 end
829829 item
830830 Name = 'postCode'
831831 DataType = ftString
832832 Precision = -1
833833 Size = 8192
834834 end
835835 item
836836 Name = 'custState'
837837 DataType = ftString
838838 Precision = -1
839839 Size = 8192
840840 end>
841841 left = 66
842842 top = 53
843843 object TCustID: TAutoIncField
844844 DisplayWidth = 10
845845 FieldKind = fkData
846846 FieldName = 'ID'
847847 Index = 0
848848 LookupCache = False
849849 ProviderFlags = [pfInUpdate, pfInWhere]
850850 ReadOnly = False
851851 Required = False
852852 end
853853 object TCustCustName: TStringField
854854 DisplayWidth = 8192
855855 FieldKind = fkData
856856 FieldName = 'CustName'
857857 Index = 1
858858 LookupCache = False
859859 ProviderFlags = [pfInUpdate, pfInWhere]
860860 ReadOnly = False
861861 Required = False
862862 Size = 8192
863863 end
864864 object TCustAddr: TStringField
865865 DisplayWidth = 8192
866866 FieldKind = fkData
867867 FieldName = 'Addr'
868868 Index = 2
869869 LookupCache = False
870870 ProviderFlags = [pfInUpdate, pfInWhere]
871871 ReadOnly = False
872872 Required = False
873873 Size = 8192
874874 end
875875 object TCustSuburb: TStringField
876876 DisplayWidth = 8192
877877 FieldKind = fkData
878878 FieldName = 'Suburb'
879879 Index = 3
880880 LookupCache = False
881881 ProviderFlags = [pfInUpdate, pfInWhere]
882882 ReadOnly = False
883883 Required = False
884884 Size = 8192
885885 end
886886 object TCustpostCode: TStringField
887887 DisplayWidth = 8192
888888 FieldKind = fkData
889889 FieldName = 'postCode'
890890 Index = 4
891891 LookupCache = False
892892 ProviderFlags = [pfInUpdate, pfInWhere]
893893 ReadOnly = False
894894 Required = False
895895 Size = 8192
896896 end
897897 object TCustcustState: TStringField
898898 DisplayWidth = 8192
899899 FieldKind = fkData
900900 FieldName = 'custState'
901901 Index = 5
902902 LookupCache = False
903903 ProviderFlags = [pfInUpdate, pfInWhere]
904904 ReadOnly = False
905905 Required = False
906906 Size = 8192
907907 end
908908 end
909909 object TSales: TSqlite3Dataset
910910 AutoIncrementKey = True
911911 IndexFieldNames = 'CustID'
912912 Options = []
913913 PrimaryKey = 'ID'
914914 SaveOnClose = True
915915 SaveOnRefetch = True
916916 SQL = 'Select * from sales;'
917917 TableName = 'sales'
918918 MasterSource = dsCust
919919 MasterFields = 'ID'
920920 FieldDefs = <
921921 item
922922 Name = 'itemNum'
923923 DataType = ftString
924924 Precision = -1
925925 Size = 8192
926926 end
927927 item
928928 Name = 'ID'
929929 DataType = ftAutoInc
930930 Precision = -1
931931 Size = 0
932932 end
933933 item
934934 Name = 'custID'
935935 DataType = ftInteger
936936 Precision = -1
937937 Size = 0
938938 end
939939 item
940940 Name = 'saleDate'
941941 DataType = ftDate
942942 Precision = -1
943943 Size = 0
944944 end
945945 item
946946 Name = 'shipDate'
947947 DataType = ftDate
948948 Precision = -1
949949 Size = 0
950950 end
951951 item
952952 Name = 'item'
953953 DataType = ftString
954954 Precision = -1
955955 Size = 8192
956956 end
957957 item
958958 Name = 'price'
959959 DataType = ftFloat
960960 Precision = -1
961961 Size = 0
962962 end>
963963 left = 66
964964 top = 141
965965 object TSalesitemNum: TStringField
966966 DisplayWidth = 8192
967967 FieldKind = fkData
968968 FieldName = 'itemNum'
969969 Index = 0
970970 LookupCache = False
971971 ProviderFlags = [pfInUpdate, pfInWhere]
972972 ReadOnly = False
973973 Required = False
974974 Size = 8192
975975 end
976976 object TSalesID: TAutoIncField
977977 DisplayWidth = 10
978978 FieldKind = fkData
979979 FieldName = 'ID'
980980 Index = 1
981981 LookupCache = False
982982 ProviderFlags = [pfInUpdate, pfInWhere]
983983 ReadOnly = False
984984 Required = False
985985 end
986986 object TSalescustID: TLongintField
987987 DisplayWidth = 10
988988 FieldKind = fkData
989989 FieldName = 'custID'
990990 Index = 2
991991 LookupCache = False
992992 ProviderFlags = [pfInUpdate, pfInWhere]
993993 ReadOnly = False
994994 Required = False
995995 end
996996 object TSalessaleDate: TDateField
997997 DisplayWidth = 10
998998 FieldKind = fkData
999999 FieldName = 'saleDate'
10001000 Index = 3
10011001 LookupCache = False
10021002 ProviderFlags = [pfInUpdate, pfInWhere]
10031003 ReadOnly = False
10041004 Required = False
10051005 end
10061006 object TSalesshipDate: TDateField
10071007 DisplayWidth = 10
10081008 FieldKind = fkData
10091009 FieldName = 'shipDate'
10101010 Index = 4
10111011 LookupCache = False
10121012 ProviderFlags = [pfInUpdate, pfInWhere]
10131013 ReadOnly = False
10141014 Required = False
10151015 end
10161016 object TSalesitem: TStringField
10171017 DisplayWidth = 8192
10181018 FieldKind = fkData
10191019 FieldName = 'item'
10201020 Index = 5
10211021 LookupCache = False
10221022 ProviderFlags = [pfInUpdate, pfInWhere]
10231023 ReadOnly = False
10241024 Required = False
10251025 Size = 8192
10261026 end
10271027 object TSalesprice: TFloatField
10281028 DisplayWidth = 10
10291029 FieldKind = fkData
10301030 FieldName = 'price'
10311031 Index = 6
10321032 LookupCache = False
10331033 ProviderFlags = [pfInUpdate, pfInWhere]
10341034 ReadOnly = False
10351035 Required = False
10361036 Currency = True
10371037 MaxValue = 0
10381038 MinValue = 0
10391039 Precision = -1
10401040 end
10411041 end
10421042 object TStock: TSqlite3Dataset
10431043 AutoIncrementKey = True
10441044 Options = []
10451045 PrimaryKey = 'ID'
10461046 SaveOnClose = True
10471047 SaveOnRefetch = True
10481048 SQL = 'Select * from stock;'
10491049 TableName = 'stock'
10501050 FieldDefs = <
10511051 item
10521052 Name = 'ID'
10531053 DataType = ftAutoInc
10541054 Precision = -1
10551055 Size = 0
10561056 end
10571057 item
10581058 Name = 'item'
10591059 DataType = ftString
10601060 Precision = -1
10611061 Size = 8192
10621062 end
10631063 item
10641064 Name = 'price'
10651065 DataType = ftFloat
10661066 Precision = -1
10671067 Size = 0
10681068 end
10691069 item
10701070 Name = 'itemNum'
10711071 DataType = ftString
10721072 Precision = -1
10731073 Size = 8192
10741074 end>
10751075 left = 66
10761076 top = 214
10771077 object TStockID: TAutoIncField
10781078 DisplayWidth = 10
10791079 FieldKind = fkData
10801080 FieldName = 'ID'
10811081 Index = 0
10821082 LookupCache = False
10831083 ProviderFlags = [pfInUpdate, pfInWhere]
10841084 ReadOnly = False
10851085 Required = False
10861086 end
10871087 object TStockitem: TStringField
10881088 DisplayWidth = 8192
10891089 FieldKind = fkData
10901090 FieldName = 'item'
10911091 Index = 1
10921092 LookupCache = False
10931093 ProviderFlags = [pfInUpdate, pfInWhere]
10941094 ReadOnly = False
10951095 Required = False
10961096 Size = 8192
10971097 end
10981098 object TStockprice: TFloatField
10991099 DisplayWidth = 10
11001100 FieldKind = fkData
11011101 FieldName = 'price'
11021102 Index = 2
11031103 LookupCache = False
11041104 ProviderFlags = [pfInUpdate, pfInWhere]
11051105 ReadOnly = False
11061106 Required = False
11071107 Currency = True
11081108 MaxValue = 0
11091109 MinValue = 0
11101110 Precision = -1
11111111 end
11121112 object TStockitemNum: TStringField
11131113 DisplayWidth = 8192
11141114 FieldKind = fkData
11151115 FieldName = 'itemNum'
11161116 Index = 3
11171117 LookupCache = False
11181118 ProviderFlags = [pfInUpdate, pfInWhere]
11191119 ReadOnly = False
11201120 Required = False
11211121 Size = 8192
11221122 end
11231123 end
11241124end
11251125```
11261126
11271127``` pascal
11281128unit uCust;
11291129
11301130{$mode objfpc}{$H+}
11311131
11321132interface
11331133
11341134uses
11351135 Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
11361136 StdCtrls, ExtCtrls, Buttons, DBGrids,DB;
11371137
11381138type
11391139
11401140 { TcustDlg }
11411141
11421142 TcustDlg = class(TForm)
11431143 cancelBtn: TButton;
11441144 dgCust: TDBGrid;
11451145 edSearch: TEdit;
11461146 Label1: TLabel;
11471147 okBtn: TButton;
11481148 pnlCust: TPanel;
11491149 sbSearch: TSpeedButton;
11501150 procedure dgCustDblClick(Sender: TObject);
11511151 procedure edSearchChange(Sender: TObject);
11521152 procedure sbSearchClick(Sender: TObject);
11531153 private
11541154 function GetCust: String;
11551155 procedure SetCust(const AValue: String);
11561156 { private declarations }
11571157 public
11581158 { public declarations }
11591159 property CustName: String Read GetCust Write SetCust;
11601160 function ShowModalCust:integer;
11611161 end;
11621162
11631163var
11641164 custDlg: TcustDlg;
11651165
11661166implementation
11671167uses uMain;
11681168{ TcustDlg }
11691169
11701170procedure TcustDlg.edSearchChange(Sender: TObject);
11711171begin
11721172 sbSearch.Enabled:=edSearch.Text<>'';
11731173end;
11741174
11751175procedure TcustDlg.dgCustDblClick(Sender: TObject);
11761176begin
11771177 ModalResult := mrOk;
11781178end;
11791179
11801180procedure TcustDlg.sbSearchClick(Sender: TObject);
11811181begin
11821182 if not fMain.TCust.Locate('CustName', edSearch.Text,[loCaseInsensitive, loPartialKey])
11831183 then
11841184 MessageDlg('No matching record found.', mtInformation, [mbOK], 0);
11851185 edSearch.Color:=clRed;
11861186end;
11871187
11881188function TcustDlg.GetCust: String;
11891189begin
11901190 Result := fMain.TCustCustName.Value;
11911191end;
11921192
11931193procedure TcustDlg.SetCust(const AValue: String);
11941194begin
11951195 fMain.TCust.Locate('CustName',AValue,[loPartialKey,loCaseInsensitive]);
11961196end;
11971197
11981198function TcustDlg.ShowModalCust: integer;
11991199begin
12001200 Caption:='Select Customer Name';
12011201 Result := ShowModal;
12021202end;
12031203
12041204initialization
12051205 {$I uCust.lrs}
12061206
12071207end.
12081208```
12091209
12101210``` pascal
12111211unit uSales;
12121212
12131213{$mode objfpc}{$H+}
12141214
12151215interface
12161216
12171217uses
12181218 Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
12191219 StdCtrls, ExtCtrls, Buttons, DBGrids, DB;
12201220
12211221type
12221222
12231223 { TsearchDlg }
12241224
12251225 TsearchDlg = class(TForm)
12261226 cancelBtn: TButton;
12271227 dgParts: TDBGrid;
12281228 edSearch: TEdit;
12291229 Label1: TLabel;
12301230 okBtn: TButton;
12311231 pnlParts: TPanel;
12321232 sbSearch: TSpeedButton;
12331233 procedure dgPartsDblClick(Sender: TObject);
12341234 procedure edSearchChange(Sender: TObject);
12351235 procedure sbSearchClick(Sender: TObject);
12361236 private
12371237 function GetPartNum: String;
12381238 procedure SetPartNum(const AValue: String);
12391239 { private declarations }
12401240 public
12411241 { public declarations }
12421242 property PartNum:String Read GetPartNum Write SetPartNum;
12431243 function ShowModalParts: Integer;
12441244 end;
12451245
12461246var
12471247 searchDlg: TsearchDlg;
12481248
12491249implementation
12501250uses uMain;
12511251{ TsearchDlg }
12521252
12531253procedure TsearchDlg.edSearchChange(Sender: TObject);
12541254begin
12551255 sbSearch.Enabled:=edSearch.Text<>'';
12561256end;
12571257
12581258procedure TsearchDlg.dgPartsDblClick(Sender: TObject);
12591259begin
12601260 ModalResult := mrOk;
12611261end;
12621262
12631263procedure TsearchDlg.sbSearchClick(Sender: TObject);
12641264begin
12651265 if not fMain.TStock.Locate('itemNum', edSearch.Text,[loCaseInsensitive, loPartialKey])
12661266 then
12671267 MessageDlg('No matching record found.', mtInformation, [mbOK], 0);
12681268 edSearch.Color:=clRed;
12691269end;
12701270
12711271function TsearchDlg.GetPartNum: String;
12721272begin
12731273 Result := fMain.TStockitemNum.Value;
12741274end;
12751275
12761276procedure TsearchDlg.SetPartNum(const AValue: String);
12771277begin
12781278 fMain.TStock.Locate('itemNum',AValue,[loPartialKey,loCaseInsensitive]);
12791279end;
12801280
12811281function TsearchDlg.ShowModalParts: Integer;
12821282begin
12831283 Caption:='Select Item Number';
12841284 Result := ShowModal;
12851285end;
12861286
12871287initialization
12881288 {$I uSales.lrs}
12891289
12901290end.
12911291```
12921292
12931293---
12941294*From the Free Pascal wiki, page "TSqlite3 Master Detail Example", as it stood on 2020-02-10 after a change by Andry, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).*

Back to history