We can convert the music database we developed in Access to a Delphi application by using the facilities for ADO included in Delphi. ADO (ActiveX Data Objects) is Microsoft's system for connecting to databases from programming applications like Delphi and Visual Basic and is an extension of the earlier ODBC (Open Database Connectivity). ADO provides a series of drivers to connect to databases provided by a range of suppliers, including Access, SQL Server, Oracle and so on.
Most applications in Delphi involving databases will have more than one Delphi form. Applications will typically have a master form that includes all the options available to different levels of user.
The first form we shall build is the main form for the application, from which all the other forms may be reached. This features a main menu and, possibly, an image for decoration. It also features an ADO Connection object which is used by all the other forms in the application to connect to the Access database.
Add an exit button to the bottom right of the form.
Add a menu control from the Standard palette and add a 'Tables' section on the left side (you will develop this later).
Find the ADO palette and double click the Connection object. Find the ConnectionString property, click on the dieresis and build a connection to your database. The first step is to choose the Provider - Jet 4.0 OLE DB will do fine. Next locate the database and select it to add it to the connection.
The second form we shall build in Delphi is one to display CD data and to allow the user to enter new data and delete old data.
Setting Up the Form
Add a new form and set its Caption to 'CDs' and its Name to 'CDForm'. Add an Exit button and then save the form. Run the program and add the units as Delphi suggests. If you have problems later it may be due to not having the units in the 'uses' clause.
Switch to form 1 to edit the menu structure. Add an element to the main menu called 'CD Details', click it to add code and enter:
form1.visible:=false;
CDform.visible:=true;
Now return to the CDForm and set up the database access. Add an ADO form from the ADO palette, set its Connection property to the only one available (on Form 1) and choose the CDs table for the Table property. Rename this ADOTable object 'ADOCDsTable'.
Double click the ADO table icon, right click the small window that appears and choose the 'add all fields' option.
Select the fields in this window and drag them onto the form. Edit the fields for size. Set the Active property of the ADO Form icon to True and the data from the Access table should appear. Note that Delphi has automatically added a DataSource object.
Add a DB Navigator from the Data Controls palette and set its DataSource property to DataSource1.
Adding Combo Boxes For Label and Music Category
You will remember that the Access table and form had a combo box for the Label and Music Type (Category) fields but the Delphi form has not converted these, they are just plain text. To begin with we added the details of these fields on the fly but we later converted them to read the data from separate tables (check the Relationships window in Access, there should be two extra tables to the left of the CDs table). Using the Delphi form, a user might enter a label or type of music that is not in the file, which would cause a problem because the lookup from the CDs table requires that the label or music type exists in the respective tables. In order to prevent this from happening we need to convert the Label and Category fields on the Delphi form to combo boxes.
To do this add a combo box from the Standard palette to the form in Delphi. Now add a second ADOTable control from the ADO palette, set its Connection property and set the Table property to 'Label'. Rename this table 'ADOLabelsTable'.
Double click the ADOTable control, right click the small window and add all the fields (just two of them, Type and Comment). We will be using code to read the data values from the table into the combo box and there is no need to drag the fields onto the form. You must complete this step otherwise the ADOTable control cannot find the fields.
Next, double click the form to bring up the OnCreate method and add this code:
ADOLabelsTable.Open;
while not ADOLabelsTable.Eof do
begin
combobox1.Items.add(ADOLabelsTableLabelName.Value);
ADOLabelsTable.Next;
end;
ADOLabelsTable.Close;
This code opens the table and reads the data values into a combo box.
We now need to transfer the value in the combo box to the data field for Label that is on the form. Double click the combo box to bring up its OnChange method and add this code:
DBEdit5.Text:=ComboBox1.Text;
DBEdit5 is the name of the field assigned by Delphi when the ADO table was set up and the fields were dragged from the field list box. Set the Visible property of the DBEdit5 field to False so it cannot be seen when the form is being used.
Repeat these steps for the genre/type field. The code for the category (genre) combo box will be very similar to that for the label:
ADOGenreTable.Open;
while not ADOGenreTable.Eof do
begin
combobox2.Items.add(ADOGenreTableMusicType.Value);
//field name added to table name
ADOGenreTable.Next;
end;
ADOGenreTable.Close;
end;
You will also need to set the value of DBEdit6 in the ComboBoxOnChange procedure (double click the combo box):
DBEdit6.Text:=ComboBox2.Text;
Updating the Menu on the Main Form
In the Main Form open the main menu and add an item to the Tables section called 'Tracks'. Set the code for this option to close the main form and open the Tracks form:
mainform.Visible:=false;
trackdetails.Visible:=true;
If the list of properties and methods does not appear after the '.' prompt it is because the uses clause has not yet been included in the unit. You can either type this in directly now or type the word 'Visible' and let Delphi prompt you for the uses clause when you run the application.
Search Facility
To allow a user to search for a particular CD title we can add some additional controls. Add a Panel to the form in a convenient place and add a label, a combo box, an edit box and a button. Double click the form and add this code to the OnCreate procedure to read the contents of the CDs table into the combo box:
ADOCDsTable.Open;
while not ADOTable1.Eof do
begin
combobox3.Items.add(ADOCDsTableCDTitle.Value);
//field name added to table name
ADOCDsTable.Next;
end;
ADOCDsTable.Close;
ADOCDsTable.Open; //reopen table so CDs can be
seen
Double click the combo box and enter this code:
ADOCDsTable.Open;
ADOCDsTable.Locate('CDTitle',combobox3.Text,[]);
The Locate function finds the item in the specified table and field. We can repeat this action for the edit box, using the button to run the search:
procedure TCDDetails.Button3Click(Sender: TObject);
begin
ADOCDsTable.Open;
ADOCDsTable.Locate('CDTitle',edit1.Text,[]);
end;
Notice that no command to close the ADO table is included here (ADOCDsTable.Close) as this would close the table and remove all the data from view. Notice also that, when the program is run, the user can type into the combo box and narrow down the search more quickly than scrolling up and down the list - this facility is controlled by the AutoComplete property, which should be set to True.
The TADOQUERY Query Control
As an alternative we could search for the CDs released by a particular artist using a TADOQUERY control. With this we could allow free entry of the artist name into an Edit box or pre-load a combobox with a list of the artists in the database. We need to add a TADOQuery control to the form (from the ADO palette). We can then use a listbox to output the list of CDs:
procedure TCDForm.Button2Click(Sender: TObject);
var i:integer;
begin
listbox1.clear;
ADOQuery1.Parameters[0].Value:=edit1.Text;
//or ComboBox4.Text
ADOQuery1.Open;
ADOQuery1.Requery;
if ADOQuery1.RecordCount=0 then showmessage ('No records found')
else
begin
for i:= 1 to adoquery1.RecordCount do
//loop through recordset
begin
listbox1.items.add(ADOQuery1.Fields[1].value);
//extract title
field
adoquery1.Next;
end;
end;
end;
The ADOQuery requires some SQL - details here and here. The code for this example is:
SELECT cds.CDTitle, cds.Artist
FROM cds
WHERE cds.Artist =:partist
This is inserted into the SQL Edit box accessed from the SQL property of the TADOQUERY object. The 'partist' variable corresponds with the parameter in the line:
ADOQuery1.Parameters[0].Value:=edit1.Text; //or ComboBox4.Text
This line passes the value from the edit or combo box to the ADOQUERY control. The SQL code automatically sets 'partist' to be the parameter of the query so it can find matches for the value supplied in the CDs table specified. The list box is populated with values from the query results. The expression (ADOQuery1.Fields[1].value) refers to the second field in the SQL code ([0] would be the first field).
We shall now add a form to display track details and allow a user to add and delete data. Track details are best displayed with some accompanying data from the CDs table so we will use a more complex related structure.
Add an ADOTable object and set its Connection property to Connection1 and its Table property to the CDs table. Rename this object 'ADOCDsTable'. Double click the icon, right click in the small window and choose the first three fields from the table. Drag the CDTitle and Artist fields onto the form (there is no need to drag the serial number unless you particularly want it but it must be in the field list otherwise Delphi cannot see it and cannot make the link to the foreign key in the Tracks table). Add a DB Navigation object, setting its DataSource property to DataSource1.
Add a second ADO Table control and set its connection and its Table property to the TrackDetails table. Rename this ADOTracksTable.
From the DataAccess palette add a DataSource control and set its DataSet property to ADOCDsTable.
From the DataControls palette add a DBGrid control and set its DataSource property to DataSource2.
Add a second DBNavigator and set its DataSource property to DataSource2.
Set the Active property in both ADOTables to Active and the data should appear.
To show only those tracks that are on the CD displayed select the ADOTracksTable icon and set its MasterSource property to DataSource1. Click on the dieresis of the MasterFields property and add the two fields that are the primary key and the foreign key in the two tables (CDID and Serial Number). This should result in the display of only the tracks on a particular CD.
This form should allow the user to enter and edit customer details. It is a simple form based on the single table for the customer details.
This form will allow the user to enter orders for customers and it will be the most complex of the forms created. The form should allow choice of customer from a list box and choice of tracks from another list box (one benefit of the list box is that it has a Sorted property so the items appear in order). Here is some code to place data in the list boxes when the form is opened:
procedure TOrders.FormCreate(Sender: TObject);
begin
adoCustomersTable.Open;
while not adoCustomersTable.Eof do
begin
listbox1.Items.Add(adoCustomersTablelastname.Value);
adoCustomersLable.Next;
end;
adoTracksTable.Open;
while not adoTracksTable.Eof do
begin
listbox2.Items.Add(adoTracksTabletracktitle.Value);
adoTracksTable.Next;
end;
end;
Note that the last name and the track title may not be unique (they were not the primary keys). Solving this problem must wait until later.
Choosing a customer and a track should bring up the details of each record:
procedure TOrders.ListBox1Click(Sender: TObject);
begin
customer_selected:=listbox1.Itemindex;
adoCustomersTable.Locate('lastname',listbox1.Items[customer_selected],[]);
end;
As the tracks are chosen they should be shown in a separate list box. We will also need to show the artist name so we can retrieve this later.
procedure TOrders.ListBox2Click(Sender: TObject);
var itemselected:integer;
begin
itemselected:=listbox2.ItemIndex;
adoTrackstable.Locate('tracktitle',listbox2.Items[itemselected],[]);
listbox3.items.Add(listbox2.Items[itemselected]);
adotable5.Open; //find artist name
adotable5.Locate('serialnumber',adotable2cdid.Value,[]);
listbox4.Items.Add(adotable5artist.Value);
adotable5.close;
end;
It should be possible to remove the tracks and artists in case of an error. The code here is activated by clicking a button:
procedure TOrders.Button2Click(Sender: TObject);
var trackselected:integer;
begin
trackselected:=listbox3.ItemIndex;
listbox3.DeleteSelected;
listbox4.Items.Delete(trackselected);
end;
When an order is ready the details should be posted to the appropriate tables for orders and order lines and a copy of an invoice should be produced for the customer.
Processing an order will involve:
procedure TOrders.Button3Click(Sender: TObject);
var i:integer;
artist:string;
orderid:integer;
cost:currency; //add prices to find
invoice total
begin //procedure
cost:=0.00;
adotable3.Open;
with adotable3 do
begin
edit; //enable editing on recordset
insert; //create new record
fieldvalues['CustomerID']:=DBEdit1.Text;
fieldvalues['Date']:=Now;
post; //save new record - autonumbers
generated now
refresh;
orderid:=adotable3orderid.Value;
//need orderid for later
end;
This creates a new order. Now we process each item in the order list:
for i:=1 to listbox3.Items.Count do
begin
adotable4.open;
with adotable4 do
begin
adoTracksTable.Locate('tracktitle',listbox3.Items[i-1],[]);
price:=adoTracksTableprice.Value; //need this later
edit;
insert;
fieldvalues['OrderItem']:=listbox3.Items[i-1];
fieldvalues['Artist']:=listbox4.Items[i-1];
fieldvalues['OrderID']:=orderid;
fieldvalues['price']:=price;
cost:=cost+price; //cumulative
total
post;
refresh;
end;
adotable4.close;
end;
We need to find the total value of the order - declare a variable and add individual values to it inside the loop. The total value of the order need not be stored as it is a calculated value.
We also need to print out an invoice (two copies, just to make things difficult). This requires more techniques.
One technique to print invoices is to place labels on a form and set them to values on the order form - name, address, tracks, costs, etc. This new form can then be made visible and printed on an event such as closing the form. Change the colour of the form to white so it prints only the text on white paper.
with invoice do
begin
label3.Caption:=orders.dbedit3.Text;
label4.Caption:=orders.dbedit2.Text;
label6.Caption:=orders.dbedit4.Text;
label7.Caption:=orders.dbedit5.Text;
label8.Caption:=orders.dbedit6.Text;
label9.Caption:=orders.dbedit7.Text;
label11.Caption:=inttostr(orderid);
for i:=1 to listbox3.Items.Count do
memo1.Lines.Add(listbox3.Items[i-1]);
label14.Caption:='£'+currtostr(cost);
invoice.visible:=true;
end;
The ADOQuery control can be used to run SQL code against a database. Details here.
See the section on Rave Reports for Delphi 2005.
As an alternative you should be able to provide your own logic for searches using either a while not table.eof loop of the Locate method. In project work such as Terry's Turkeys you will probably have to write your own routines to produce reports.
Another technique is to use the report generator built into Delphi 6, using the QReport (Quick Report) toolbar palette. To create a report choose File/New/Other and double click on the Report icon. Add two Report Bands and set the second one to BandType Detail. Add QRLabels and QRTextBoxes to the Header and Detail bands.
To make a Quick Report do its job you will need an ADOQuery control (from the ADO palette). You will need to add some some SQL to the SQL property of the ADOQuery. One way to get this is to create the query you want in your Access database and then copy the SQL generated to the ADO query.
You will need to add your own WHERE clause to the SQL, with a colon in front of the variable name. When you close the SQL lines box Delphi should add the parameter to the parameters list. Check this by opening the Parameters property list. If the parameter is not present there may be an error in your program, but add it manually anyway by clicking on the yellow New button in the list's toolbar.
To activate the Quick Report you need to add code similar to that below to set parameters and execute the query.
Set the DataSet property of the report to ADOQuery1. You can add and execute more than one query on the form. In this case we use two:
custid:=strtoint(DBEdit1.Text);
with QuickReport6 do
begin
ADOQuery1.Parameters[0].Value:=custid;
//parameter from the form
ADOQuery1.Open;
ADOQuery1.Requery;
ADOQuery2.Parameters[0].Value:=orderid;
//parameter from the form
ADOQuery2.Open;
ADOQuery2.Requery;
Preview; //show the report
end;
listbox3.clear;
listbox4.Clear;
end; //procedure
The SQL for the ADOQuery control is:
SELECT Customers.*
FROM Customers
WHERE CustomerID = :pcustid;
With all fields selected any of these can be placed in the title area of the quick report form. 'custid' should appear in the list of parameters.
A second ADOQuery can be added to the form to output the ordered items.
SELECT Orderitems.*
FROM OrderItems
WHERE OrderID = :porderid;
With all fields available these can be placed in the detail section of the quick report form. Note that the DataSet property of the report form should be set to ADOQuery2 as this is the one that produces repeating data. If the DataSet property of the form is not set correctly only one item from the dataset will be displayed.
The last two instructions clear the list boxes used for storing ordered tracks and artists.