How to create an HTML table from a SQL Database

On our public web page we have a "Board Membership" list page with about 60 Board Members listed with their title, organization, web page and board position.  We also have a "Staff Directory" that just lists 50 or so staff by name, job title and telephone number.

The lists are updated frequently and are in the form of simple HTML tables.  See formats below.  I currently update them by just editing the HTML tables in notepad and pasting the html into the appropriate content block.  Our web site is hosted by a third party and I can't use ASP.Net, only HTML.  I may be able to get away with some imbeded JavaScript, but I can only edit content between the <body></body> tags, the <head> is in a master page I don't have access to.

What I'd like to do is maintain the list of board members and info in a SQL table and the list of staff and phone numbers in another SQL table and generate the HTML table from the database.  That way I can provide a data input form for the secretary to update the lists and I can generate the table and post it very quickly.

I don't need to have the html table automatically posted to the web site I just need to generate an HTML table in a local text file from our local SQL table.  Then I can just copy it to our web site on the appropriate page.  Any method of generating a pain vanilla html table is fine.  If it requires  ASP.Net/VB/JavaScript/XML or whatever, as long as the output is a plain html table in a format like below.

Thanks!
Board Membership Table format (repeat 60 times):
                <tr>
                    <td>
                        <span style="font-weight:bold;">FirstName LastName</span><br />
                        <em>Title</em>
                    </td>
                    <td>
                        <a target="_blank" href="http://www.webpage.com"><strong>Organization</strong></a>
                    </td>
                    <td>
                        <strong>Position</strong>
                    </td>
                </tr>

Staff Directory Format (repeat 50 times):
                <tr>
                    <td><a href="mailto:Username@AlwaysSameDomain.com">&nbsp;FName LName</a><br />
                    Job Title</td>
                    <td>954.202.1234, x3056</td>
                </tr>

Open in new window

LVL 1
megninAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Todd GerbertIT ConsultantCommented:
If your goal is to keep things simple, I would consider writing a simple C# console application.  Then you could just run your program.exe and copy/paste the output of the program, or run "program.exe > list.htm"


If your site is hosted with a hosting company, you might want to inquire about using ASP.Net... and since you mentioned the <head> tag is in a master page I'm led to believe it's already running ASP.Net, and you may be able to embed C# code using a <script runat=server> tag, or inside <%%> code blocks.

using System;
using System.Data;
using System.Data.SqlClient;

public static class Program
{
  static void Main()
  {
    using (SqlConnection cn = new SqlConnection("Data Source=YourSqlServer;Initial Catalog=DatabaseName;User ID=sa;Password=secret"))
    {
      cn.Open();
      using (SqlCommand cm = cn.CreateCommand())
      {
        cm.CommandText = "SELECT * FROM TableName";
        using (SqlDataReader reader = cm.ExecuteReader())
        {
          while (reader.Read())
          {
            Console.WriteLine(String.Format(
              "<tr>\r\n\t<td>{0} {1}</td>\r\n\t<td>{2}</td>\r\n</tr>\r\n",
              reader["FirstName"], reader["LastName"], reader["Position"]));
          }
        }
      }
      cn.Close();
    }
  }
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megninAuthor Commented:
Nice!  I have two exciting looking options to play with tomorrow.
tgerbert, thank you.  Yes, my goal is to keep it as simple as possible.  Any more difficult than changing a row or two in an HTML table ever other month or so and I'd just keep updating what we have.  Your C# example looks like just the ticket.  I only work with VB for now, but it should be a simple matter to convert that to VB.
emoreau, that LINQ and XML Literals example looks a little more complicated, but it's already in VB, I want to learn to use LINQ anyway and it looks like that technique could be very useful now and in the future.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dan_masonCommented:
Here's a fairly simple of way of outputting to an HTML table, which would work regardless of the number of lines to be returned. I've worked most of your structure/formatting into this example.
/* Mock-up of a BoardMembers table/query */
CREATE TABLE #BoardMembers (Firstname varchar(40),LastName varchar(40),Title varchar(20),Org varchar(50),OrgURL varchar(100),BoardPosition varchar(80))
INSERT #BoardMembers(Firstname,LastName,Title,Org,OrgURL,BoardPosition)
VALUES ('Carl','Smith','Mr','Fitzright Shoes','http://www.fitzshoes.com','Non-executive Director')
INSERT #BoardMembers(Firstname,LastName,Title,Org,OrgURL,BoardPosition)
VALUES ('Wendy','Bailey','Miss','Second to None PLC','http://www.secondtonone.com','Treasurer')
INSERT #BoardMembers(Firstname,LastName,Title,Org,OrgURL,BoardPosition)
VALUES ('David','Stone','Mr','Arthur Crown INC','http://www.arthurcrown.com','Chairman')

DECLARE @XML XML,@HTMLtable varchar(max),@ExportCmd varchar(max)

/* Generate an HTML table containing the data */
SELECT @XML= (SELECT 2 as [@cellpadding],0 as [@cellspacing],1 as [@border]
--This returns the header
        ,(select th from 
			(select 'Name' th
			union all 
			SELECT 'Organization'
			union all
			SELECT 'Position') d --name of this alias is irrelevant but there has to be one
          for xml path(''),type) tr --tr here defines that the header row will be a tr element
--This returns the rows in the table
        ,(select (select Name+'<br />'+Title+''  for xml path('td'),type),
				(select '<a href="'+OrgURL+'">'+Org+'</a>' for xml path('td'),type),
				(select '<strong>'+Position +'</strong>'for xml path('td'),type)
            from -- derived table because we are grouping
                 (SELECT FirstName+' '+LastName AS Name,Title, OrgURL,Org,BoardPosition AS Position from #BoardMembers) data for xml path ('tr'),type)
                 for xml path('table'),type  )   -- wrap everything in a table tag   

SELECT @HTMLtable=CAST(@xml as varchar(max))

SELECT @HTMLtable=REPLACE(REPLACE(@HTMLtable,'&gt;','>'),'&lt;','<')
   -- Because we added in some elements within cells (xml nodes), the tags will have been encoded, e.g. <a> becomes &lt;a&gt; - This changes them back

/*Export to .htm file*/
SET @ExportCmd='EXEC xp_cmdshell ''bcp "SELECT '''''+@HTMLtable+'''''" queryout c:\temp\testtable.htm -S(local) -c -t -T  '''

EXEC (@ExportCmd)

/* Remove the temporary table */
DROP TABLE #BoardMembers

Open in new window

0
megninAuthor Commented:
Nice, Dan.  Thank you.  That solution looks very good as well.  I've been tied up today doing the typical Monday, urgent but unimportant tasks and haven't had a chance to play with these yet.  I've still got a few hours to go today so hopfully I can get started in a little bit...
0
megninAuthor Commented:
Thany you very much for the excellent solutions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.