...

View Full Version : Output Recordset to HTML Table



jason_kelly
01-25-2012, 05:59 PM
Hi There,

I really need your help.

How could I amend the following code below to incorporate the fieldnames on the top before displaying the values of the recordset?

Ie.

[FIELDNAME1] [FIELDNAME2] [FIELDNAME3] [FIELDNAME4]
value1 value2 value3 value4



<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>

<script language="javascript" type="text/javascript">

function getsqltable() {

var strtable = ""
var cn = new ActiveXObject("ADODB.Connection")
var rs = new ActiveXObject("ADODB.Recordset")
var dbfile = "imts.mdb"
var dbPath = "F:/PROJECTS/IMTS PROJECT/V7/database/"

cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + dbPath + dbfile + "")

var mySQL = "SELECT * FROM tbl_imts WHERE [File Number] = 'A-2010-00301'"

rs.Open(mySQL, cn, 1, 3)

rs.MoveFirst

strtable='<table cellpadding=0 cellspacing=0 width=75%>';
while (!rs.eof)
{
strtable+='<tr>';
strtable+='<td>'+rs.fields(0)+'</td>' + '<td>'+rs.fields(1)+'</td>' + '<td>'+rs.fields(2)+'</td>' + '<td>'+rs.fields(3)+'</td>'
strtable+='</tr>';
rs.movenext;
}
strtable+='</table>';


rs.close;
cn.close;

document.getElementById('htmltable').innerHTML=strtable;
}
</script>


</head>

<body>

<input type="button" onclick="getsqltable();" value="Click Me"/>

<div id="htmltable"></div>

</body>

</html>



I've tried modifying the while code, but it puts it all on one line:



while (!rs.eof)
{
strtable+='<tr>';
strtable+='<td>'+rs.fields(0).Name+'</td>' + '<td>'+rs.fields(1).Name+'</td>' + '<td>'+rs.fields(2).Name+'</td>' + '<td>'+rs.fields(3).Name+'</td>'
strtable+='<td>'+rs.fields(0)+'</td>' + '<td>'+rs.fields(1)+'</td>' + '<td>'+rs.fields(2)+'</td>' + '<td>'+rs.fields(3)+'</td>'
strtable+='</tr>';
rs.movenext;
}


any ideas?

Much thanks and appreciation for everyones help.

Cheers and much thanks in advance,

Jay

Alternative
01-25-2012, 07:11 PM
Use "\n".



while (!rs.eof)
{
strtable+='<tr>';
strtable+='<td>'+rs.fields(0).Name+'</td>' + "\n" +
'<td>'+rs.fields(1).Name+'</td>' + "\n" +
'<td>'+rs.fields(2).Name+'</td>' + "\n" +
'<td>'+rs.fields(3).Name+'</td>' + "\n" +


Etc..

The spacing will only show up in the source code page.

jason_kelly
01-25-2012, 08:42 PM
Hi Alternative,

Thank you for your help.

It didnt not quite work, was still on the same line.

In VBA I have the following code below that writes the column headers, using the existing code, would it be possible to convert this from VBA to Javascript?



'GET THE FIELDNAMES
Print #fnum, "<table border=0 cellpadding=0 cellspacing=0 width=573 style=border-collapse: collapse;width:429pt>"
Print #fnum, "<tr height=17 style=" & Chr(34) & "height:12.75pt" & Chr(34) & ">"
num_fields = frmIMTS.rs.Fields.Count
For i = 0 To num_fields - 1
Print #fnum, "<td height=17 style=" & Chr(34) & "height:12.75pt; font-size: 10.0pt; color: windowtext; font-weight: 700; text-decoration: none; text-underline-style: none; font-family: Calibri; white-space: normal; font-style: normal; text-align: general; vertical-align: bottom; border-left: medium none; border-right: .5pt solid white; border-top: medium none; border-bottom: 1.5pt solid white; padding-left: 1px; padding-right: 1px; padding-top: 1px; background: #95B3D7 " & Chr(34) & ">"
Print #fnum, frmIMTS.rs.Fields(i).Name
Print #fnum, "</TD>"
Next i
Print #fnum, "</tr>"


Im not quite sure how to go about this?

Old Pedant
01-25-2012, 10:13 PM
strtable='<table cellpadding=0 cellspacing=0 width=75%>';

strtable += '<tr>\n';
for ( var c = 0; c < rs.fields.count; ++c )
{
strtable += '<td>' + rs.fields(c).name + '</td>\n' ;
}
strtable += '</tr>\n';

while (!rs.eof)
{
strtable += '<tr>\n';
for ( var c = 0; c < rs.fields.count; ++c )
{
strtable += '<td>' + rs.fields(c).value + '</td>\n' ;
}
strtable += '</tr>\n';
rs.movenext;
}
strtable+='</table>';



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum