...

View Full Version : Ouput SQL Query to HTML Table



jason_kelly
11-16-2012, 12:13 AM
Hello,

I need your help.

How can the code be modified below to dynanically generate the results of my SQL query to an HMTL table that would be similar to the example in the table below? (ie. 2 items per table row)



<!DOCTYPE html>
<html>

<head>

<script type="text/javascript">

function test() {

try {

alert("running function test...")

var cn = new ActiveXObject("ADODB.Connection")

var rs = new ActiveXObject("ADODB.Recordset")

var sql = "SELECT * FROM tbl_rssims"

var db = "G:\\AS\\Asf\\ASF\\RSSIMS\\db\\rssims.mdb"

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

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

var html = '<!DOCTYPE html>\n'
html += '<html>\n'
html += '<head>\n'
html += '<table style="border: none; table-layout: fixed; width: 100%; text-align: left;" cellpadding="0" cellspacing="0">\n'

//<!-- WRITE FIELD VALUES -->
while (!rs.eof) {
html += '<tr>\n';
for (var c = 0; c < rs.fields.count; ++c) {

//html += '<td>' + rs.fields(c).value + '</td>\n'
html += '<tr><td>' + rs.GetString(2, -1, '<br>', '</td><td>', '') + '</td></tr>';

}//end of for
html += '</tr>\n'
rs.movenext
}//end of while
html +=' </table>'

window.open('','').document.write(html)

rs.close
cn.close
}//end of try

catch(e) {

alert(e.description)

}

}//end of function
</script>

</head>

<body>
<b>Example:</b>
<table style="border: none; table-layout: fixed; width: 100%; text-align: left;" cellpadding="0" cellspacing="0">
<tr>
<td>Mr. Ronald McDonald<br>Chief Executive Officer<br>The Hudson Bay Corporation<br>123 Yahoo Street<br>Toronto, Ontario<br>Canada</td>
<td>Mr. Steve Marin<br>Chief Executive Officer<br>General Motors<br>456 Don Mills Street<br>Toronto, Ontario<br>Canada</td>
</tr>
</table>

<input onclick="test()" type="button" value="button" id="button">

</body>

</html>

Old Pedant
11-16-2012, 01:34 AM
Ummm...you are using getString *ALL* wrong.

getString gets *ALL* the records from the recordset into a string.

Yes, you can get only one record at a time but that's an ugly as crud way to do it!

Old Pedant
11-16-2012, 01:41 AM
You *REALLY* want your table to look like *THAT*???



<table style="border: none; table-layout: fixed; width: 100%; text-align: left;" cellpadding="0" cellspacing="0">
<tr>
<td>Mr. Ronald McDonald<br>Chief Executive Officer<br>The Hudson Bay Corporation<br>123 Yahoo Street<br>Toronto, Ontario<br>Canada</td>
<td>Mr. Steve Marin<br>Chief Executive Officer<br>General Motors<br>456 Don Mills Street<br>Toronto, Ontario<br>Canada</td>
</tr>
</table>

That will appear on the screen as


Mr. Ronald McDonald Mr. Steve Marin
Chief Executive Officer Chief Executive Officer
The Hudson Bay Corporation General Motors
123 Yahoo Street 456 Don Mills Street
Toronto, Ontario Toronto, Ontario
Canada Canada


That's really what you want?

What do you do if there are 40 records?

They will all be in one row and be much much wider than the screen.

Old Pedant
11-16-2012, 01:46 AM
Here is how I would do it:


function test() {
try {
alert("running function test...")
var cn = new ActiveXObject("ADODB.Connection")
var sql = "SELECT * FROM tbl_rssims"
var db = "G:\\AS\\Asf\\ASF\\RSSIMS\\db\\rssims.mdb"
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + db + "")

rs = cn.Execute(sql); // why did you need to lock the recordset for WRITING??
var prefix = "<tr>";
var midfix = "</td><td>";
var suffix = "</tr>\n";

var trs = rs.GetString(2,-1,midfix,suffix + prefix);
trs = prefix + trs.substring(0,trs.length - prefix.length);

rs.close
cn.close

var html = '<!DOCTYPE html>\n'
+ "<html><body>" +
+ '<table style="border: none; table-layout: fixed; width: 100%; text-align: left;" cellpadding="0" cellspacing="0">\n'
+ '<th><th>Name</th><th>Title</th><th>Company</th><th>Address</th><th>City, Province</th><th>Country</th></tr>\n'
+ trs
+ '</table></body></html>';
window.open('','').document.write(html)

}//end of try
catch(e) {
alert(e.description)
}
}//end of function

Old Pedant
11-16-2012, 01:51 AM
But I guess if you really WANT all those <td>s in a single <tr> row you could just change it to:


function test() {
try {
alert("running function test...")
var cn = new ActiveXObject("ADODB.Connection")
var sql = "SELECT * FROM tbl_rssims"
var db = "G:\\AS\\Asf\\ASF\\RSSIMS\\db\\rssims.mdb"
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + db + "")

rs = cn.Execute(sql); // why did you need to lock the recordset for WRITING??
var prefix = "<td>";
var midfix = "<br/>";
var suffix = "</td>\n";

var tds = rs.GetString(2,-1,midfix,suffix + prefix);
tds = prefix + tds.substring(0,tds.length - prefix.length);

rs.close
cn.close

var html = '<!DOCTYPE html>\n'
+ "<html><body>" +
+ '<table style="border: none; table-layout: fixed; width: 100%; text-align: left;" cellpadding="0" cellspacing="0">\n'
+ '<tr>' + tds + '</td>'
+ '</table></body></html>';
window.open('','').document.write(html)

}//end of try

catch(e) {

alert(e.description)

}

}//end of function

jason_kelly
11-16-2012, 01:57 AM
Thanks Old Pendant!

I am sure that were getting closer.

The code works, but it amalgamates all the records together in what looks like one huge paragraph,

Sorry I meant to remove the getstring, I did not want to use it.

The records should be displayed as a 2 column table with seperate rows

so on 1 row, it would contain a <td>rs(0)<br>rs(1)</td> in the left cell and then another record <td>rs(0)<br>rs(1)</td> in the right cell, then create another row and do the above until all records have been processed

and so fourth, so it would be like doing a microsoft word, document, splitting the text into 2 columns,

I appologize if I wasnt clear.

Thanks so much for your continued help.

Getting closer!

Jason

Old Pedant
11-16-2012, 02:11 AM
Ahhh...yes, then you don't want to use getString().

Okay:


function test() {
try {
alert("running function test...")
var cn = new ActiveXObject("ADODB.Connection")
var sql = "SELECT * FROM tbl_rssims"
var db = "G:\\AS\\Asf\\ASF\\RSSIMS\\db\\rssims.mdb"
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + db + "")

rs = cn.Execute(sql); // why did you need to lock the recordset for WRITING??
var trs = "";
while ( ! rs.EOF )
{
trs += "<tr>";
for ( var td = 1; td <= 2; ++td )
{
trs += "<td>";
if ( ! rs.EOF )
{
trs += rs.fields(0).value;
for ( var f = 1; f < rs.fields.length; ++f )
{
trs += "<br/>" + rs.fields(f).value;
}
trs.MoveNext();
}
trs += "</td>\n";
}
trs += "</tr>\n";
}

rs.close
cn.close

var html = '<!DOCTYPE html>\n'
+ "<html><body" +
+ '<table style="border: none; table-layout: fixed; width: 100%; text-align: left;" cellpadding="0" cellspacing="0">\n'
+ trs
+ '</table></body></html>';
window.open('','').document.write(html)

}//end of try

catch(e) {

alert(e.description)

}

}//end of function


EDIT: MoveNext() was in wrong place. Corrected.

Old Pedant
11-16-2012, 02:21 AM
This is just as untested as the others, but it would probably perform a little better and is more JavaScript-ish in nature (thanks to the array.push() can array.join() usages).


function test() {
try {
alert("running function test...")
var cn = new ActiveXObject("ADODB.Connection")
var sql = "SELECT * FROM tbl_rssims"
var db = "G:\\AS\\Asf\\ASF\\RSSIMS\\db\\rssims.mdb"
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + db + "")

rs = cn.Execute(sql); // why did you need to lock the recordset for WRITING??
var trs = [ ];
while ( ! rs.EOF )
{
var tr = [ ];
for ( var td = 1; td <= 2; ++td )
{
if ( ! rs.EOF )
{
var flds = [ ];
for ( var f = 0; f < rs.fields.length; ++f )
{
flds.push( rs.fields(f).value );
}
trs.MoveNext();
tr.push( flds.join("<br/>") );

} else {
tr.push( "&nbsp;" );
}
}
trs.push( "<td>" + tr.join("</td><td>") + "</td>\n" );
}

rs.close
cn.close

var html = '<!DOCTYPE html>\n'
+ "<html><body" +
+ '<table style="border: none; table-layout: fixed; width: 100%; text-align: left;" cellpadding="0" cellspacing="0">\n'
+ "<tr>" + trs.join("</tr><tr>") + "</tr>"
+ '</table></body></html>';
window.open('','').document.write(html)

}//end of try

catch(e) {

alert(e.description)

}

}//end of function

Old Pedant
11-16-2012, 02:25 AM
The cute part about EITHER of those last two answers is that you can change the number of columns by just changing

for ( var td = 1; td <= 2; ++td )

Just change 2 to the number of desired columns.

jason_kelly
11-16-2012, 01:46 PM
Thanks again for this Old Pendant. I am getting an error now though "Object Doesn't Support This Property or Method"

I don't see why it wouldn't work though. Everything looks in tact.

Jason

Edit: I took another look and changed: trs.MoveNext(); to rs.MoveNext(); and it worked, except that the resulting ouput is this:



<!DOCTYPE html>
<html><bodyNaN<tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td></td>
</tr><tr><td></td><td>&nbsp;</td>
</tr></table></body></html>

Old Pedant
11-16-2012, 08:53 PM
Yes, trs.MoveNext() was a typo.

The NaN is there because of another typo:


+ "<html><body" +

See? Missing the > after <body

I fixed it in post #5 but missed it in the others.

The one &nbsp; in there indicates that clearly the basic code is working. So for some reason we aren't getting anything in the <td>...</td> in normal cases.

Let's look at that code carefully:


var flds = [ ];
for ( var f = 0; f < rs.fields.length; ++f )
{
flds.push( rs.fields(f).value );
}
rs.MoveNext();
tr.push( flds.join("<br/>") );


It LOOKS right to me. Possible it should be

flds.push( rs.fields(f).Value ); // upper case V?

But at this point, it is time for you to start using a debugger.

Put a breakpoint on the tr.push( ) line there and inspect the contents of the flds variable. Does it contain all the fields from the record?

Old Pedant
11-16-2012, 08:55 PM
OH! I think I know!

I'm so used to using length for the size of a JS collection that I forgot that ADODB.Recordset.Fields isn't really a JS collection!

Look here:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms676763(v=vs.85).aspx

See it? There is NO length property on the Fields collection!

Change it to Count instead!


var flds = [ ];
for ( var f = 0; f < rs.fields.Count; ++f )
{
flds.push( rs.fields(f).Value );
}
rs.MoveNext();
tr.push( flds.join("<br/>") );



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum