ch11.htm

好书《C++ Builder高级编程技术》

#include "InsertEdit.h"

#include 
"DMod1.h"

#pragma resource "*.dfm"

TInsertEditForm *InsertEditForm;

__fastcall TInsertEditForm::TInsertEditForm(TComponent* Owner)

  : TForm(Owner)

{

}

void __fastcall TInsertEditForm::bbInsertClick(TObject *Sender)

{

  
DMod->BookLookupTable->Insert();

}

void __fastcall TInsertEditForm::bbPostClick(TObject *Sender)

{

  DMod->BookLookupPost();

}

void TInsertEditForm::ShowEdit(AnsiString S)

{

  DMod->FindTitle(S);

  ShowModal();

  
DMod->BookLookupPost();

}

void TInsertEditForm::ShowInsert()

{

  DMod->BookLookupInsert();

  DMod->BookLookupTableTitle->AsString = "My New Book";

  ShowModal();

  DMod->BookLookupPost();

}

void __fastcall 
TInsertEditForm::CancelBtnClick(TObject *Sender)

{

  DMod->BookLookupCancel();

}

void __fastcall TInsertEditForm::DeleteBtnClick(TObject *Sender)

{

  if (MessageBox(Handle, "Delete?" , "Delete Dialog", MB_YESNO) == ID_YES)

    
DMod->BookLookupDelete();

}

void __fastcall TInsertEditForm::FormShow(TObject *Sender)

{

  TitleEdit->SetFocus();

}

</FONT></PRE>
<P>The Lookup program enables you to easily fill in the key fields of the <TT>Books</TT>
table by looking 
them up in the <TT>Authors</TT> table. To understand why this capability
is important, notice that the only way to tell which author is associated with which
book is by placing the appropriate author number in the <TT>AuthNo</TT> field of
the 
<TT>Book</TT> table. This is convenient from the point of view of the programmer
who wants to construct a well-made relational database. In particular, it saves space
by allowing the construction of one-to-many relationships. However, the user isn't

going to want to have to remember that Herman Melville is associated with the number
2, Jack Kerouac with the number x, and so on. The point of a lookup field is that
it lets you look up a list of authors in the author table, and then automatically

assigns the chosen author number to the <TT>AuthNo</TT> field in the <TT>Books</TT>
table.</P>
<P>This program uses two tables called, not surprisingly, <TT>Author.db</TT> and
<TT>Book.db</TT>. Both of these tables are found on the CD-ROM that 
accompanies this
book. Tables 11.1 and 11.2 show the schema for the tables. <BR>
<BR>
<B>Table 11.1. Author.db table structure. </B>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="81" ALIGN="LEFT"><B>Name</B></TD>
		<TD WIDTH="125" 
ALIGN="LEFT"><B>Type</B></TD>
		<TD WIDTH="60" ALIGN="LEFT"><B>Keyed</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="81" ALIGN="LEFT"><TT>AuthNo</TT></TD>
		<TD WIDTH="125" ALIGN="LEFT"><TT>AutoInc</TT></TD>
		<TD WIDTH="60" 
ALIGN="LEFT">Key</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="81" ALIGN="LEFT"><TT>First</TT></TD>
		<TD WIDTH="125" ALIGN="LEFT"><TT>Character(25)</TT></TD>
		<TD WIDTH="60" ALIGN="LEFT">N/A</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD 
WIDTH="81" ALIGN="LEFT"><TT>Last</TT></TD>
		<TD WIDTH="125" ALIGN="LEFT"><TT>Character(25)</TT></TD>
		<TD WIDTH="60" ALIGN="LEFT">N/A</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="81" ALIGN="LEFT"><TT>Dates</TT></TD>
		<TD WIDTH="125" 
ALIGN="LEFT"><TT>Character(25)</TT></TD>
		<TD WIDTH="60" ALIGN="LEFT">N/A</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="81" ALIGN="LEFT"><TT>BirthPlace</TT></TD>
		<TD WIDTH="125" ALIGN="LEFT"><TT>Character(25)</TT></TD>
		<TD WIDTH="60" 
ALIGN="LEFT">N/A</TD>
	</TR>
</TABLE>
<BR>
<BR>
<B>Table 11.2. Book.db table structure. </B>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="81" ALIGN="LEFT"><B>Name</B></TD>
		<TD WIDTH="126" ALIGN="LEFT"><B>Type</B></TD>
		<TD 
ALIGN="LEFT"><B>Keyed</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="81" ALIGN="LEFT"><TT>BookNo</TT></TD>
		<TD WIDTH="126" ALIGN="LEFT"><TT>AutoInc</TT></TD>
		<TD ALIGN="LEFT">Key</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD 
WIDTH="81" ALIGN="LEFT"><TT>AuthNo</TT></TD>
		<TD WIDTH="126" ALIGN="LEFT"><TT>LongInt</TT></TD>
		<TD ALIGN="LEFT">Foreign Key</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="81" ALIGN="LEFT"><TT>Title</TT></TD>
		<TD WIDTH="126" 
ALIGN="LEFT"><TT>Character (35)</TT></TD>
		<TD ALIGN="LEFT">N/A</TD>
	</TR>
</TABLE>


<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P><FONT COLOR="#000077"><B><BR>
	</B></FONT>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice the use of the 
<TT>AutoIncrement</TT>
	fields in the table definitions shown in Tables 11.1 and 11.2. These fields will
	automatically be filled in when the user adds a new record at runtime. For instance,
	when you add the first record to the <TT>Books</TT> table, 
it will automatically
	be given a <TT>BookNo</TT> of <TT>1</TT>. The second record will automatically be
	given a <TT>BookNo</TT> of <TT>2</TT>, and so on. <TT>AutoIncrement</TT> fields are
	read-only, and frequently there is no need to show them to 
the user at runtime. <BR>
	<BR>
	Furthermore, I use Referential Integrity to ensure that the <TT>AuthNo</TT> field
	properly binds to the <TT>Author</TT> table. In particular, it ensures that you cannot
	insert records into the <TT>Book</TT> table 
that are not properly related to the
	<TT>Authors</TT> table through the <TT>AuthNo</TT> field. Referential Integrity also
	ensures that the value of the <TT>AuthNo</TT> field is filled in automatically when
	you insert a new record into the 
<TT>Books</TT> table. To view the Referential Integrity,
	load the book table into the Database Desktop, choose Table | Info Structure, select
	Referential Integrity in the Table Properties, highlight the <TT>AuthNoRI</TT> rule,
	and press the Detail 
Info button. There is more on this subject in the next chapter,
	called &quot;Understanding Relational Databases.&quot; 
<HR>


</BLOCKQUOTE>

<P>There is little actual work required to construct this program. In particular,
look over the source code 
shown earlier, and you will see that the only significant
line of code in the whole program is the one for the <TT>OnCalcFields</TT> event.
Other than that, it's just a matter of manipulating the visual tools.</P>
<P>To get started, create a new 
application and add a data module to it. Set up the
<TT>Authors</TT> and <TT>Books</TT> tables on the data module. Bring up the Fields
Editor for both tables and create objects for all of their fields. Give the tables
and data sources appropriate 
names, such as <TT>AuthorTable</TT> and <TT>BookLookupTable</TT>,
as shown in Figure 11.7. Note that later on I will add a second in- stance of the
<TT>Book</TT> table to the program so that I can simultaneously perform a lookup
and a one-to-many.<BR>

<BR>
<A NAME="Heading21"></A><A HREF="11ebu07.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/11/11ebu07.jpg">FIGURE 11.7.</A><FONT COLOR="#000077">
</FONT><I>The <TT>TDataModule</TT> for the Lookup program.</I></P>
<P>Inside the <TT>Author</TT> table, create a calculated field called 
<TT>LastFirst</TT>.
To create the calculated field, first right-click the <TT>TTable</TT> object, and
then right-click the Fields Editor and select New from the menu. After creating the
calculated field, assign the following method to the 
<TT>OnCalcFields</TT> event:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TDMod::AuthorTableCalcFields(TDataSet *DataSet)

{

  AuthorTableFirstLast-&gt;AsString =

    AuthorTableFirst-&gt;AsString + &quot; &quot; + AuthorTableLast-&gt;AsString;

}


</FONT></PRE>
<P>This field will be the one that is looked up in the second table. The issue here
is that just looking up the last name of an author is not sufficient--you need to
look up both first and last names in order to be sure you are finding a 
unique author.
In other words, you can't tell Henry James from William James or Tom Wolfe from Thomas
Wolfe unless you have both the first and last name present. It would be wasteful
of disk space to permanently add a field to the table that combined 
the first and
last names, but you can create a temporary copy of that field with a calculated field.</P>
<P>Now that you have a calculated field in place, it is time to create a lookup field.
To get started, bring up the Fields Editor for the 
<TT>Book</TT> table. Right-click
it and create a new field called <TT>AuthorLookup</TT>. Set its <TT>Type</TT> to
<TT>String</TT> and its <TT>Field Type</TT> to <TT>Lookup</TT>. The <TT>KeyField</TT>
should be set to <TT>AuthNo</TT>, the 
<TT>Dataset</TT> to <TT>AuthorTable</TT>, the
<TT>Lookup Key</TT> to <TT>AuthNo</TT>, and the <TT>Result</TT> field to <TT>LastFirst</TT>.
Figure 11.8 shows how the New Field dialog should look when you are done. Notice
that you can also fill in this 
same information in the Object Inspector if you first
select the <TT>BookLookupTable</TT> object. (In other words, you could create a new
object and then close the Fields Editor without specifying any of its properties.
Later, you could select the 
object and designate its type, its lookup fields, and
so on.)<BR>
<BR>
<A NAME="Heading22"></A><A HREF="11ebu08.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/11/11ebu08.jpg">FIGURE 11.8.</A><FONT COLOR="#000077">
</FONT><I>Filling in the New Field dialog.</I></P>
<P>Go back to <TT>Form1</TT> and make 
sure the two <TT>TDBGrids</TT> are arranged
one above the other and are hooked up properly to the tables on the <TT>TDataModule</TT>.
Run the application.</P>
<P>The <TT>AuthorLookup</TT> field in the <TT>TDBGrid</TT> object associated with
the 
<TT>Books</TT> table is now a drop-down combo box. If you click it once, and
then drop down its list, you can then perform a lookup into the <TT>LastFirst</TT>
field of the <TT>Author</TT> table. This lookup will automatically fill in the 
<TT>AuthNo</TT>
field of the book table. You can use this lookup to insert a new author into a new
record or to change the author of an existing record.</P>
<P>Note that lookup fields give you two distinct benefits. They enable you to perform
a 
&quot;join&quot; between the <TT>Books</TT> table and the <TT>Authors</TT> table,
and they allow you to look up a reference in a drop-down list.</P>
<P>The implementation of this program found on the CD-ROM that accompanies this book
actually enables 
the user to perform the lookup on a second form. I implement things
that way because it is probably easiest from the user's perspective, and because
I want to support both a one-to-many relationship and a lookup between the <TT>Authors</TT>
and 
<TT>Books</TT> tables. However, if you just want to see how lookups work, then
you should follow the technique described previously.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The capability of 
having a drop-down
	list in a grid object comes for free in BCB, even when you are not doing lookups.
	Go back in design mode and open up the <TT>Columns</TT> property of a grid object.
	Add all the fields to the <TT>Columns</TT> list box. You can now 
select one of the
	fields, such as <TT>Title</TT>, and choose the PickList button in order to create
	a set of default values available for the field. The user can access these values
	at runtime by clicking the field and dropping down the combo box, 
per the lookup
	example discussed previously. This is the capability supported by the old <TT>TDBLookupList</TT>
	and <TT>TDBLookupCombo</TT> from the old Windows 3.1 days. 
<HR>


</BLOCKQUOTE>

<P>Besides the <TT>TDBGrid</TT> object, there are two 
other controls in BCB that
understand lookup fields. The first of these controls is shown on <TT>Form2</TT>
of the Lookup program found on the CD-ROM that accompanies this book. The <TT>TDBLookupComboBox</TT>
is the default control you will get if you 
drag and drop the <TT>AuthorLookup</TT>
field from the Fields Editor onto a form. If you perform the drag-and-drop operation,
the control will be hooked up automatically. If you want to hook it up manually,
just connect its <TT>DataSource</TT> to the 
<TT>dsBook</TT> object and its <TT>DataField</TT>
to the <TT>AuthorLookup</TT> field. There is also a <TT>TDBLookupListBox</TT>, which
works exactly the same way as the <TT>TDBLookupComboBox</TT>.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT 
COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Both the <TT>TDBLookupListBox</TT>
	and <TT>TDBLookupComboBox</TT> have fields that correspond to the ones you filled
	in with the New Field dialog shown in Figure 11.7. However, there is no need to fill
	in 
these fields a second time. Just hook up the <TT>DataSource</TT> and <TT>DataFields</TT>
	properties, and you are ready to go. 
<HR>


</BLOCKQUOTE>

<P>When you are working with the Lookup program found on the book's CD-ROM, you should
note that 
<TT>Form1</TT> does not contain a lookup. It's meant to help you scan through
all the available data so you can grok the significance of the lookup process. The
top part of the second form, called the <TT>InsertEditForm</TT>, is somewhat closer
to the 
type of display you would want to present to the user in a real program. However,
I have extended this form to include a <TT>TDBGrid</TT> object, just so you can see
how the lookup combo box is inserted automatically into the grid.</P>
<P>When working 
with the <TT>InsertEditForm</TT>, notice how easy it is to simply
type in a new book name, select an author in the combo box, and then perform a <TT>Post</TT>
by clicking the OK button. The process is very simple from the user's point of view.
In 
particular, a new <TT>BookNo</TT> is being assigned automatically by the <TT>AutoIncrement</TT>
field, and the new <TT>AuthNo</TT> is being filled in automatically by the lookup
process.</P>
<P>Here are the two ways to handle the data in 
<TT>InsertEditForm</TT>:

<UL>
	<LI><TT>InsertMode</TT>: A temporary book name appears in the <TT>TitleEdit</TT>
	control when you bring up the form in this mode. Type in the name of the book you
	want to record in the <TT>Title</TT> field, and then 
drop down the <TT>AuthorLookup</TT>
	combo to select an author.
	<P>
	<LI><TT>EditMode</TT>: There is usually data in both the <TT>Title</TT> and <TT>AuthorLookup</TT>
	fields when you bring up the mode in this form. The user can either edit the 
<TT>Title</TT>
	field, or associate the book with a new author. I call a method called <TT>FindTitle</TT>
	in the <TT>DMod</TT> module to make sure that the form shows the record the user
	wants to edit.
</UL>

<P>It's important to note that lookup 
controls probably would not be appropriate
for use with big datasets because drop-down controls aren't very handy for displaying
thousands of items. Even list boxes are fairly limited in these circumstances. You
would therefore use lookups mostly with 
smaller datasets.</P>
<P>It should also be pointed out that not being able to use both one-to-many relationships
and lookups between the same two tables is a significant inconvenience. However,
the Lookup example discussed in these sections and 
implemented on the book's CD-ROM
shows that the workaround is not that complex.