Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse a database to build a Web page
Keywordsdatabase, ADO, Web, HTML
CategoriesUtilities, Database
 
Open the output HTML file and write HTML header information into it using the Print statement. Write a TABLE statement to begin the table and use the TR and TH tags to write the table's column headers.

Connect to the database and execute a query that selects the desired records. For each record, write TR and TD tags to display the record. In this program, the first cell displays the book's title and picture inside a link to the book's Web page. The ISBN entries are marked NOWRAP so the Web page doesn't wrap their entries across multiple lines.

After displaying each all of the records, close the TABLE tag, finish the page, and close the database connection.

Note how this example uses Resume CloseUp to ensure that everything gets closed if there is an error.

 
' Create the HTML table representation.
Private Sub cmdConvert_Click()
Dim fnum As Integer
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim num_fields As Integer
Dim i As Integer
Dim num_processed As Integer

    On Error GoTo MiscError

    ' Open the output file.
    fnum = FreeFile
    Open txtHTMLFile.Text For Output As fnum

    ' Write the HTML header information.
    Print #fnum, "<HTML>"
    Print #fnum, "<HEAD>"
    Print #fnum, "<TITLE>This is the title</TITLE>"
    Print #fnum, "</HEAD>"

    Print #fnum, ""
    Print #fnum, "<BODY TEXT=""#000000"" " & _
        "BGCOLOR=""#CCCCCC"">"
    Print #fnum, "<H1>Book Data</H1>"

    ' Start the HTML table.
    Print #fnum, "<TABLE WIDTH=""100%"" CELLPADDING=""2"" " & _
        "CELLSPACING=""2"" BGCOLOR=""#00C0FF"" " & _
        "BORDER=""1"">"

    ' Write the headers Title, ISBN, and Description.
    Print #fnum, "  <TR>"
    Print #fnum, "    <TD BGCOLOR=""FFFF00"">Title</TD>"
    Print #fnum, "    <TD BGCOLOR=""FFFF00"">ISBN</TD>"
    Print #fnum, "    <TD " & _
        "BGCOLOR=""FFFF00"">Description</TD>"
    Print #fnum, "  </TR>"

    ' Open the database.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & txtDatabase.Text & ";" & _
        "Persist Security Info=False"
    conn.Open

    ' Open the recordset.
    Set rs = conn.Execute( _
        "SELECT * FROM Books ORDER BY Title", , adCmdText)

    ' Process the records.
    Do While Not rs.EOF
        ' Start a new row for this record.
        num_processed = num_processed + 1
        Print #fnum, "  <TR>"

        ' Put the title and picture in the first cell.
        Print #fnum, "    <TD>"
        Print #fnum, "      <A HREF=""" & _
            rs.Fields("URL").Value & """>"
        Print #fnum, "        <IMG SRC=""" & _
            rs.Fields("Picture").Value & """ " & _
            "ALIGN=""Left"">"
        Print #fnum, "        " & rs.Fields("Title").Value
        Print #fnum, "      </A>"
        Print #fnum, "    </TD>"

        ' Display the ISBN and Description.
        Print #fnum, "    <TD NOWRAP>" & _
            rs.Fields("ISBN").Value & "</TD>"
        Print #fnum, "    <TD>" & _
            rs.Fields("Description").Value & "</TD>"
        Print #fnum, "  </TR>"

        rs.MoveNext
    Loop

    ' Finish the table.
    Print #fnum, "</TABLE>"
    Print #fnum, "<P>"
    Print #fnum, "<H3>" & _
        Format$(num_processed) & _
        " records displayed.</H3>"
    Print #fnum, "<HR COLOR=C000C0>"
    Print #fnum, "Thanks to <A " & _
        "HREF=http://www.vb-helper.com>VB Helper</A>."

    Print #fnum, "</BODY>"
    Print #fnum, "</HTML>"

    ' Close the file and database.
CloseUp:
    On Error Resume Next
    rs.Close
    conn.Close
    Close fnum
    MsgBox "Processed " & _
        Format$(num_processed) & " records."

    Exit Sub

MiscError:
    MsgBox "Error " & Err.Number & _
        vbCrLf & Err.Description
    Resume CloseUp
End Sub
 
Depending on your needs, it may be easier for you to store the data in an XML file and then use XSL to build the Web page when it is needed. For information on this and other uses of XML, see my book Visual Basic .NET and XML.
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated