1. Technology

Your suggestion is on its way!

An email with a link to:

http://delphi.about.com/od/database/l/aa112701a.htm

was emailed to:

Thanks for sharing About.com with others!

From ADO Query to HTML
How to export your data to HTML using Delphi and ADO. This is the first step in publishing your database on the Internet - see how to create a static HTML table from an ADO query.
 More of this Feature
• Projects CODE

Printer friendly versionPrinter friendly version
 Join the Discussion
"Post your views and comments to this chapter of the free Delphi database Programming Course"
Discuss!
 Related Resources
• free DB Course.TOC
• more Database articles
• Internet programming
 From Other Guides
• About HTML

In this chapter, of the free database Delphi ADO course, you are going to see how to easily create HTML pages based on database information. In particular, you'll see how to open a query from an MS Access database with Delphi and loop through the contents generating an HTML page for each row in a recordset.

Start a new application - this creates an empty Delphi form.
Add the following data components :

  • TAdoConnection (name: AdoConnection1)
  • TAdoQuery (name: AdoQuery1)
  • TDataSource (name: DataSource1)
  • TDBGrid (name: DBGrid1)
  • To display the generated HTML you'll need several more components to be dropped on a form.

  • TWebBrowser (name: WebBrowser1) - used to display the generated HTML.
  • TMemo (name: Memo1) - used to display the text of the generate HTML.
  • TButton (name: Button1) - used to open a query, create the HTML code and display it.
  • This is how your form should look at design time (in the middle of the form there is a WebBrowser component)

    at design time

    The first thing you need to do when working with a database in Delphi and ADO, is to set relations between data access components then open a database connection. Just to test your knowledge we'll link all DB related components from code (no need to use the Object Inspector). You place the code in the OnCreate event for the form (plus some extra code to pre-set other components):

    procedure TForm1.FormCreate(Sender: TObject);
    var ConStr: widestring;
    begin
    ConStr := 'Provider=Microsoft.Jet.OLEDB.4.0;'+
              'Data Source=C:\!gajba\About\aboutdelphi.mdb;'+
              'Persist Security Info=False';
    
    DBGrid1.DataSource := DataSource1;
    DataSource1.DataSet := ADOQuery1;
    ADOQuery1.Connection := ADOConnection1;
    ADOConnection1.ConnectionString := ConStr;
    ADOConnection1.LoginPrompt:=False;
    
    Edit1.Text:='SELECT * FROM [tablename]';
    Memo1.Text:='';
    end;
    

    The next step is to get the recordset by opening a query so we can get access to the data. The query text is in the Edit1 components Text property. The code should be placed in the OnClick event of a Button1 (as described below).

      ADOQuery1.SQL.Text:=Edit1.Text;
      ADOQuery1.Open;
    

    HTML code
    HTML is the most widely spread format for content on the Web. If your knowledge on HTML is low, please consider visiting About's HTML site. Here's a really quick info on html: in general, a HTML page consists of text, plain ASCII text. An HTML file can contain many, so called, tags which determine the style of the font, formating of the paragraph, ... You use special tags to create a table with rows and columns.

    After we've opened up a recordset, we loop through the records creating a table for each entry. Prior to generating a HTML table, we create the HTML header, title and body. We first loop through the field names to create a table header, then loop through the recordset to create and fill table rows.

    
    procedure TForm1.Button1Click(Sender: TObject);
    var
      shtml     : widestring;
      htmlfile  : TextFile;
      i         : integer;
      AvailableFields: set of  TFieldType;
    begin
    AvailableFields:=[ftWideString, ftDate,  ftFloat];
    //open query
    ADOQuery1.SQL.Text:=Edit1.Text;
    ADOQuery1.Open;
    
    // --> create a html page
    
    
    //html header shtml:= '<html> <head> <title>'; shtml:= shtml + Edit1.Text; shtml:= shtml + '</title></head>' + #13#10; shtml:= shtml + '<body>' + #13#10; shtml:= shtml + 'Table created from query: ' + Edit1.Text + '' + #13#10; //table header shtml:= shtml + '<table border="1" width="100%">' + #13#10; shtml:= shtml + '<tr>' + #13#10; for i:=0 to AdoQuery1.FieldCount-1 do begin if ADOQuery1.Fields[i].DataType in AvailableFields then begin shtml:= shtml + '<td>'; shtml:= shtml + '' + ADOQuery1.Fields[i].DisplayName + ''; shtml:= shtml + '</td>' + #13#10; end; end;{for} shtml:= shtml + '</tr>' + #13#10; //table body while not adoquery1.Eof do begin shtml:= shtml + '<tr>' + #13#10; for i:=0 to AdoQuery1.FieldCount-1 do begin if ADOQuery1.Fields[i].DataType in AvailableFields then begin shtml:= shtml + '<td>'; shtml:= shtml + ADOQuery1.Fields[i].AsString; shtml:= shtml + '</td>' + #13#10; end; end;{for} shtml:= shtml + '</tr>' + #13#10; ADOQuery1.Next; end;{while} shtml:= shtml + '</table>' + #13#10; shtml:= shtml + '</body></html>';

    Note: for the sake of simplicity, we'll allow only string, number and date fields to be converted and inserted in a html table. The AvailableFields variable is a set of TFieldTypes declared as: AvailableFields: set of TFieldType; in the VAR section and assigned like: AvailableFields:=[ftWideString, ftDate, ftFloat];

    Once you have the shtml string variable filled with the HTML, we simply show it in the Memo, save the html string to the disk (in the text file that has the applications name, extension is set to htm), and finally navigate to the saved file in a WebBrowser.

    // --> assign to memo
      Memo1.Text := shtml;
    
    // --> save in a htm file
     AssignFile(htmlfile, 
                ChangeFileExt(
                Application.ExeName,'.htm'));
     Rewrite(htmlfile);
     WriteLn(htmlfile, shtml);
     CloseFile(htmlfile);
    
    
    // --> browse to the file
     WebBrowser1.Navigate(ChangeFileExt(
         Application.ExeName,'.htm'));
     
    end;{Button1.OnClick}
    

    Now start the project, write some valid query string in the Edit component, like SELECT * FROM authors, click the Button1, and this is what you should get:

    query to html at run-time

    It is clear that you can easily configure this code to determine which fields to include and which not, how to sort the database, which filters to apply (you can use WHERE in your SQL statement), and set the HTML file look (Colors, Fonts, etc). Download the projects code and start exporting your data to HTML.

       The TCustomContentProducer components
    Another way of converting database query results to html pages is to use some of the descendants of the TCustomContentProducer component. The components (PageProducer, DataSetTableProducer, QueryTableProducer, ...) are located on the Internet page of the component palette - if your Delphi version includes them. We'll be looking at some of those components in the next chapter.

       To the next chapter
    If you need any kind of help so far, please post to the Delphi Programming Forum where all the questions are answered and beginners are treated as experts.

    DB Course Next Chapter >>
    >> Using ADO in Delphi 3 and 4 (before AdoExpress) - Chapter 21

    ©2014 About.com. All rights reserved.