[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to create an HTML table from a SQL Database

Posted on 2010-03-26
6
Medium Priority
?
743 Views
Last Modified: 2013-11-08
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

0
Comment
Question by:megnin
6 Comments
 
LVL 33

Accepted Solution

by:
Todd Gerbert earned 800 total points
ID: 28746189
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
 
LVL 71

Assisted Solution

by:Éric Moreau
Éric Moreau earned 400 total points
ID: 28765333
0
 
LVL 1

Author Comment

by:megnin
ID: 28825816
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 6

Assisted Solution

by:dan_mason
dan_mason earned 800 total points
ID: 29005373
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
 
LVL 1

Author Comment

by:megnin
ID: 29012560
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
 
LVL 1

Author Closing Comment

by:megnin
ID: 31707816
Thany you very much for the excellent solutions.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question