18 July 2005

JScript in IE6 to read database

Inspired by this Kuro5hin article, I hacked up a slightly nicer JScript implementation to read database tables and generate HTML tables in a browser using DOM functions such as insertRow() and insertCell().

Try it out

If you want to try it out, first create an Excel workbook called Test.xls with a page named DataTest. In DataTest, create one column with a heading StringValue and another column with a heading NumericValue. Enter some test data in those columns, then save the file.

Next, copy the HTML code at the end of this article, change the DSN path appropriately and save it in a file.

Finally, load the HTML file in IE6. When you select the Test button, the data in your workbook should be displayed in the browser.

Notes

If you start Excel and the user interface doesn't initialize properly, the cause is usually the Excel automation server not exiting. Just start Windows Task Manager, and kill the errant Excel.exe process in the Processes tab.

I chose Excel instead of Access as the database because it is easier to test simple programs without having to create a table design.

Sample JScript and HTML to read database

<html>
  <head>
    <title>DBTest for IE6</title>
    <script type="text/javascript">
      function readDb(tableId) {
        var conn = new ActiveXObject("ADODB.Connection")
        var dsn = "Provider=Microsoft.Jet.OLEDB.4.0;"
        dsn += "Data Source=C:\\CVS_STUFF\\HTML\\Database\\Test.xls;"
        dsn += 'Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"'
        conn.Open(dsn)

        var rs = new ActiveXObject("ADODB.Recordset")
        rs.Open("[DataTest$]", conn)

        var outputTable = document.getElementById(tableId)
        if (!outputTable) {
          alert("Cannot find " + tableId)
          return
        }

        if (!rs.bof) {
          var rowIndex = 1
          rs.MoveFirst()
          while (!rs.eof) {
            var row = outputTable.insertRow(rowIndex)

            var c1 = row.insertCell(0)
            var data1 = rs.Fields('StringValue').value
            var text1 = document.createTextNode(data1)
            c1.appendChild(text1)

            var c2 = row.insertCell(1)
            var data2 = rs.Fields('NumericValue').value
            var text2 = document.createTextNode(data2)
            c2.appendChild(text2)

            ++rowIndex
            rs.MoveNext()
          }
        }

        rs.Close()
        conn.Close()
      }

    </script>
  </head>
  <body>
    <h1>DBTest for IE6</h1>
    <form action="javascript:readDb('testOut')">
      <input type="submit" value="Test"/>
    </form>

    <table id="testOut">
      <tr><th>StringValue</th><th>NumericValue</th></tr>
    </table>

  </body>
</html>