Dynamically Create Reports

Question:
Using Visual Basic 6.0, I created a reference to a HTMLDocument object. I built a basic HTML page template and displayed it within a Web browser control on a form. I’ve been able to manipulate certain elements on the form by assigning IDs in certain tags. For example, I have been writing data from a SQL Server 7.0 database to display in the HTML page. I am trying to produce a set of dynamic HTML reports to view the data in the database. The problem is that I want to dynamically create elements on another HTML page and not just write data to a template HTML page (using IDs). Because I don’t know how many rows will be returned by a recordset, I need to be able to dynamically create the report. For example, there could be 10 rows in a table element or 100. How can I do this using the HTMLDocument object?

Answer:

In this particular case, you’re trying to make the template do too much work. Rather than trying to specifically set values to an ID, you should look upon the report as an auto-generated table. You can take a couple of approaches to accomplish this task?you could generate the table through a VB routine that writes output directly into a DIV, or you could create a reference to a table object and generate the information via the DHTML object model.

The former is arguably the simpler of the two approaches, but is also significantly slower since the Web page has to parse and recreate the HTML based upon the initial input string. Let’s say that you had a SQL table called ReportData, and you wanted to output each field as one entry in the table. The first approach would look much like the outputTableA given here:

sub outputTable(rs as Recordset,divEl as IHTMLDIVElement)     dim buffer as String     dim fld as Field     buffer=""     buffer=buffer+""     rs.MoveFirst     for each fld in rs.Fields         buffer=buffer+""     next     buffer=buffer+""     while not rs.eof            buffer=buffer+""            for each fld in rs.Fields                   buffer=buffer+""            next            buffer=buffer+""            rs.moveNext     wend    buffer=buffer+"
"+fld.name+"
"+fld.value+"
" divEl. innerHTML=bufferend sub

You would pass as arguments a recordset object and a reference to a DIV element within the WebControl page. Here is the subroutine that calls the OutputTable function:

sub showMyTable()     dim doc as HTMLDocument     dim rs as Recordset     dim conn as Connection     dim targetEl as IHTMLDIVElement     set doc=webBrowser1.document     set conn=new Connection     conn.open myConnectionString  ' Obviously, this needs to be changed     set rs=new Recordset      rs.open "SELECT * FROM ReportItems",conn      set targetEl=doc.all("displayTable")      OutputTable rs,targetEl         rs.close      conn.closeend sub

This approach poses a few problems, however. The buffering can take time for a large number of records. What’s worse, when the string buffer is passed to the document, the document will need to render it all at once, causing your VB app to slow to a crawl while the table is rendering. For a large database, you may be better off using the document object model more fully, with the revised OutputTable:

sub outputTable(rs as Recordset,divEl as IHTMLDIVElement)     dim tbl as IHTMLTable     dim row as IHTMLTableRow     dim cell as IHTMLTableCell     dim fld as Field      set tbl=divEl.document.createElement("TABLE")     divEl.innerHTML=""     divEl.appendChild tbl     set row=tbl.insertRow      rs.MoveFirst     for each fld in rs.Fields         set cell=row.insertCell         cell.innerText=fld.name         cell.style.fontWeight="bold"         cell.style.textAlign="center"     next     while not rs.eof            set row=tbl.insertRow            for each fld in rs.Fields                   set cell=row.insertCell                   cell.innerText=fld.value            next            rs.moveNext     wendend sub

Because these code objects are actually in memory, they will refresh the page more quickly, are easier to debug, and make for slightly easier to follow code. (I spent nearly four hours last week trying to debug outputted HTML as in the first example, and finally found that I was inadvertently putting a space between the end tag slash and its name?one of the more difficult things to catch in a debug). Moreover, in IE5, you can set the table attributes to FIXED and take advantage of this new feature to output partial tables. You can’t do this with buffered HTML text.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a