Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Sep 2011
    Posts
    184
    Thanks
    140
    Thanked 0 Times in 0 Posts

    Ouput SQL Query to HTML Table

    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)

    Code:
    <!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>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    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!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    You *REALLY* want your table to look like *THAT*???

    Code:
    <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
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Here is how I would do it:
    Code:
    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
    Last edited by Old Pedant; 11-16-2012 at 01:51 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    But I guess if you really WANT all those <td>s in a single <tr> row you could just change it to:
    Code:
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Sep 2011
    Posts
    184
    Thanks
    140
    Thanked 0 Times in 0 Posts
    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

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Ahhh...yes, then you don't want to use getString().

    Okay:
    Code:
    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.
    Last edited by Old Pedant; 11-16-2012 at 02:23 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    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).
    Code:
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jason_kelly (11-16-2012)

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    The cute part about EITHER of those last two answers is that you can change the number of columns by just changing
    Code:
        for ( var td = 1; td <= 2; ++td )
    Just change 2 to the number of desired columns.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jason_kelly (11-16-2012)

  • #10
    Regular Coder
    Join Date
    Sep 2011
    Posts
    184
    Thanks
    140
    Thanked 0 Times in 0 Posts
    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:

    Code:
    <!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>
    Last edited by jason_kelly; 11-16-2012 at 02:23 PM.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Yes, trs.MoveNext() was a typo.

    The NaN is there because of another typo:
    Code:
                             + "<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:
    Code:
                                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
    Code:
                                   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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    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/libr...(v=vs.85).aspx

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

    Change it to Count instead!
    Code:
                                var flds = [ ];
                                for ( var f = 0; f < rs.fields.Count; ++f )
                                {
                                   flds.push( rs.fields(f).Value );
                                }
                                rs.MoveNext();
                                tr.push( flds.join("<br/>") );
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •