Developing from Chaos

Sunday Mar 28th 1999 by ServerWatch Staff
Share:

Using ASP to Create a Website from Spreadsheets

T.Mallard

A common problem with many businesses is that their data is not set up in any kind of relational database at some level. Oh, fun, it's always a big mess to go in and do anything with it without spending way more time than it's worth. What to do? ASP allows you to quickly create what you need to assemble a website without hand-wringing and develop a relational database on the way, making the time worth the trouble.

Work flow for this project. This article takes raw excel files, text files, anything the customer has to create a website using it. There are several ASP pages used only to assemble database tables from the original files. The final product of this is a website with basic linked navigation built from pricing files. An added touch is using VBScript to strip common words from the html and create meta tags with technical keywords for each product page during a pre-processing of the product page.

Architecture for the build consists of:

  • notes (which are extracted into a table by part number)
  • header (company name, basic navigation to homepage)
  • content (based upon existing HTML pages)
  • product page (created from the above with next-previous navigation)
  • keywords (using VBScript to parse the content into a table)
  • meta tags (inserting the keywords into product pages)
  • footer (standard footer for the page)
First is the raw data, an example is:
ModelPart NumberDescriptionSIN 132-8 GSA Price
CMU-710150-1224-01Campus-Star Management Unit, including embedded SNMP agent in local FLASH memory and Software Usage License.$1,435
CNS-700150-1220-02Campus-Star Chassis with slots for up to 14 Line Units, 14 Interface Modules, one CMU, and two Power Supplies (1, 3)$1,914
CPS-720150-1226-01Campus-Star 100-240VAC Power Supply (2,3)$619
CPS-730150-1226-02Campus-Star 48VDC Power Supply (2)$619
PGTMS/CS150-1296-01Campus-Star Management Application for HP OpenView/ Windows (MS Windows 3.1)$480
BP-014150-2160-01Blank Filler Panel Kit for Star Concentrator (Qty. 14 panels)$93
Notes:
(1) Price includes one Power Supply. Please specify either CPS-720 AC Power Supply or CPS-730 DC Power Supply for each Campus-Star Chassis ordered.
(2) Price for a second Power Supply or spares, if required.
(3) Specify either US Cordset (654-1033-02) for North America or EC-Cordset (654-1042-02) for Continental Europe with each CPS-720 AC Power Supply.

This is all on one spreadsheet of course, text notes mixed in with pricing and description. OK. To handle this takes the creation of a database and a few tables, I'll have to prepare the data semi-manually at first to save a lot of coding time, there's a trade-off between automating every step on a job, as you would for an enterprise, versus a situation where the code is basically one-shot data prep stuff and doesn't get used after that. Head down, it's cut and paste from Excel file to Access table field (after arranging the work-flow to find what fields go where in the database). The table named "content" contains a heirarchy of links for navigation, as well as the page information.

Notes are extracted into a table by part number. That's gns_dat_02.asp. Then product pages are created in a series of pages, pricing pages, and finally the index. It took about 6-7 versions to get it all worked out, but the results are perfect. Very useful.




T.Mallard
Using ASP to Create a Website from Spreadsheets Continued

So, I manually copied and pasted from the original Excel file into tables which were created with wizards and default values in Microsoft Access for the database. To the right is the "content" table definitions, note that any number fields need to be listed first, then text fields and memo fields last; it's important in some operations to call them in order as well. OLEDB is used for the connection for speed, ODBC isn't needed as this is workflow on a PC using PWS, a scaled down version of IIS4 as the webserver for win98 to create static html pages for a website using asp.

The dim's aren't necessary, I include them for clarity and while not datatyped, the arrangement is so they can be imported into VisualBasic and turned into a COM object easily (psst, it's also so I don't get lost); the latest tests show that for production performance the dim's should be taken out. Not all variables are used on each page of this series.
The response.write's are for reporting about how many pages were made when the asp is run, so the screen becomes a log in function. For repeatable operations, this can be changed to output to a log file.

<%@ Language=VBScript%>
<!-- # include virtual="/asp/adovbs.inc" -->
<% response.buffers = true %>
<%
dim input, output, htmlOut, author, editor, pubdate, topic, source, locale, annotate, errs, err2
dim conn1, conn2, rsInput, rsOutput, rsTemp, rsCreate
dim check, line, newrecs, allrecs, found, site
dim aline, afields, sql_01, sql_02, sql_03, cmd1
dim adate
adate = date
sql_01 =""
lines = 0
newrecs = 0
alreadys = 0
set conn1 = server.createobject("ADODB.Connection")
conn1.open "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=x:\webshare\wwwroot\gns\cgi\gns_01.mdb"

set rsInput = server.createobject("ADODB.Recordset")
rsInput.open "product", conn1, 2, 3, 2

set rsOutput = server.createobject("ADODB.Recordset")
rsOutput.open "content", conn1, 2, 3, 1

The code above opens a connection to the fresh tables. First is extracting the notes, which are never more than three for the entire product catalog. This page is very simple, it extracts the notes and inserts them. The code checks to see if the current first note is empty, then fills from the previous collection of notes by product type, not by product number; part of the hierarchy I was able to create by using a database. The response.write's are just periods and colons to indicate how many records are processed visually. When this job is done, all the notes are in the Access tables and ready for the pages which follow.

T.Mallard
Using ASP to Create a Website from Spreadsheets Continued



while not rsInput.EOF
  if isnull(rsInput("note1")) then
lines = lines+1
rsInput.moveprevious
addin1 = rsInput("note1")
addin2 = rsInput("note2")
addin3 = rsInput("note3")
rsInput.movenext
rsInput("note1") = addin1
rsInput("note2") = addin2
rsInput("note3") = addin3
rsInput.update
response.write("."& vbCrLf)
  end if
  rsInput.movenext
  response.write(":" & "<br>"& vbCrLf)
wend
conn1.close
set rsInput = nothing
response.write("done: " & lines & vbCrLf)
%>

Next step is the basic product page, which has auto-numbering and link navigation created as a part of the build process. This first section of code just arranges the auto-numbering for this. The next opens a new output file, then opens the tables needed to produce a page between chunks of html. The finished page here is a first pass of many passes before all the keywords are inserted and the page is ready to publish. The database is within the web server's scope so has read/execute/script permissions set under IIS4, and the folder is shared for protection using the machine account under win98/NT4.

set rsInput = server.createobject("ADODB.Recordset")
rsInput.open "content", conn1, 2, 3, 2
rsInput.movefirst
lines = 1
while not rsInput.EOF
if not isnull(rsInput("prod_model")) then
series = series + 1
lines = lines + 1
 if series < 100 then
  if series < 10 then
   htmlOut = "x:\webshare\wwwroot\gns\v1\prod_00" & series & ".html"
   sNext = series + 1
   htmlNext = "prod_00" & sNext & ".html"
   if series > 1 then
    sPrev = series - 1
    htmlPrev = "prod_00" & sPrev & ".html"
  end if
 else
   htmlOut = "x:\webshare\wwwroot\gns\v1\prod_0" & series & ".html"
   sNext = series + 1
   htmlNext = "prod_0" & sNext & ".html"
   sPrev = series - 1
   if sPrev = 9 then
    htmlPrev = "prod_009.html"
  else
   htmlPrev = "prod_0" & sPrev & ".html"
   end if
  end if
 else
  htmlOut = "x:\webshare\wwwroot\gns\v1\prod_" & series & ".html"
  sNext = series + 1
  htmlNext = "prod_" & sNext & ".html"
  if series = 1 then
   htmlPrev = "prod_001.html"
  else
   sPrev = series - 1
  if sPrev = 99 then
   htmlPrev = "prod_099.html"
  else
   htmlPrev = "prod_" & sPrev & ".html"
  end if
 end if
end if
filename = mid(htmlOut, instr(htmlOut, "prod_"))
response.write("filename: " & filename & "<br>")

T.Mallard
Using ASP to Create a Website from Spreadsheets Continued

The next-previous values are done, this took a few tries but not much time to develop. Next step is to open the recordsets needed to make the first part of the html now that the filename is ready.

set cmd1 = server.createobject("ADODB.Command")
cmd1.ActiveConnection = conn1
cmd1.CommandText = "update product set prod_page = '" & filename & "' where product.part_number = '" & rsInput("part_number") & "'"
set rsOutput = cmd1.execute
response.write("updated: " & rsInput("part_number") & "<br>")
set cmd1.ActiveConnection = nothing
set cmd1 = nothing
set conn2 = server.createobject("Scripting.FileSystemObject")
set output = conn2.OpenTextFile(htmlOut, 8, True)

The html page is now open with the new filename and ready to use the Access table for source data to build the page within the html template. The outside while loop creates pages for the entire catalog; after this the package is ready for a few more additions. Note that the "8" is used here, I had trouble with ForAppending due to an old version of Access (95 v7) and use the numbers for these settings. So here is a hunk of html with a few insertions throughout of database material. Work flow is to finish the head area first, then navigation, add the content, the footer and save the page.

output.writeline("<!-- DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 3.2 Final//EN""-->")
output.writeline("<html>")
output.writeline("<head>")
output.writeline("<title>PairGain from Government Network Solutions - " & rsInput("prod_type") & "</title>")
output.writeline("<meta name=""Publisher"" content=""Government Network Solutions"">")
output.writeline("<meta name=""Publisher-email"" content=""gns@gns-pairgain.com"">")
output.writeline("<meta name=""Key"" content=""" & rsInput("part_number") & """>")
output.writeline("<meta name=""Description"" content="""">")
output.writeline("<meta name=""Identifier-URL"" content=""http://www.gns-pairgain.com/"">")
output.writeline("<meta name=""Content-Language"" content=""en-US"">")
output.writeline("<meta name=""Coverage"" content=""Worldwide"">")
output.writeline("<meta name=""Date-created-yyyymmdd"" content=""19990416"">")
output.writeline("<meta name=""Date-Revised-yyyymmdd"" content=""*"">")
output.writeline("</head>")
output.writeline("<body bgcolor=ffffff link=0000ff vlink=8e2323 alink=00009c>")
output.writeline("<basefont face='verdana,arial,helvetica' size=2 color=000000>
output.writeline("<a name=""page_top""> </a>")
output.writeline("<table border=1 cellspacing=6 cellpadding=6 bordercolor=000080 align=left>")
output.writeline("<tr><td bgcolor=0066cc><b><font size=3 color=ffffff>G</font><font size=2 color=99ccff>OVERNMENT</font> <font size=3 color=ffffff>N</font><font size=2 color=99ccff>ETWORK</font> <font size=3 color=ffffff>S</font><font size=2 color=99ccff>OLUTIONS</font></b></td></tr>")
output.writeline("<tr><td bgcolor=ffffff background=""../jpgs/aback_05.jpg""><center><img src=""gifs/pairgain_logo_07.gif"" width=148 height=51 hspace=4 vspace=4 border=0 alt=""PairGain Products.""></center></td></tr>")
output.writeline("</table>")
output.writeline("<p align=right><a href=""order.html"">How to Order or Get More Information</a></p>")
output.writeline("<p align=right><a href=""gsa_price.html"">GSA Price List</a></p>")
output.writeline("<p align=right><a href=""index.html"">Return to Home Page</a> <hr></p>")
output.writeline("<font size=4 color=800000 face=""Arial,Helvetica,Verdana"">" & rsInput("prod_type") & "</font><br>")
output.writeline("<font size=4 color=000000 face=""Arial,Helvetica,Verdana"">" & rsInput("prod_group_title") & "</font><br>")
output.writeline("<font size=2 color=000000 face=""Arial,Helvetica,Verdana"">" & rsInput("description") & "</font><br>")
output.writeline("<font size=4 color=000000 face=""Arial,Helvetica,Verdana"">" & rsInput("prod_title") & "</font><br>")

Now that has the navigation in, time to open some more recordsets...getting pricing and product content, and of course the notes. The connection strings use "2" for a dynamic cursor, "3" is next for optimistic locking and "2" represents a table as a source of data. The final wend and end if relate to the series variable and page naming loop above.

T.Mallard



Using ASP to Create a Website from Spreadsheets Continued




set conn4 = server.createobject("ADODB.Connection")
conn4.open "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=x:\webshare\wwwroot\gns\cgi\gns_01.mdb"
set rsTemp = server.createobject("ADODB.Recordset")
rsTemp.open "product", conn4, 2, 3, 2
rsTemp.movefirst
while not rsTemp.EOF
  if not rsInput("part_number") = rsTemp("part_number") then
  rsTemp.movenext
else
  output.writeline("<a href=""" & rsTemp("prod_price_page") & """><font size=2 color=800000 face=""Arial,Helvetica,Verdana"">" & rsInput("prod_title") & " pricing</font></a><p>")
  rsTemp.movenext
  end if
wend
set rsTemp = nothing
output.writeline(rsInput("prod_content") & "<hr>")
output.writeline("<br><font size=2 color=000000 face=""Arial,Helvetica,Verdana"">Notes:</font><br>")
set rsTemp2 = server.createobject("ADODB.Recordset")
rsTemp2.open "notes", conn1, 2, 3, 2
while not rsTemp2.EOF
  if rsInput("prod_type") = rsTemp2("prod_type") then
    if not isnull(rsTemp2("note1")) then
    output.writeline("<font size=1 color=000000 face=""Arial,Helvetica,Verdana"">" & rsTemp2("note1") & "</font><br>")
    end if
  if not isnull(rsTemp2("note2")) then
    output.writeline("<font size=1 color=000000 face=""Arial,Helvetica,Verdana"">" & rsTemp2("note2") & "</font><br>")
    end if
  if not isnull(rsTemp2("note3")) then
    output.writeline("<font size=1 color=000000 face=""Arial,Helvetica,Verdana"">" & rsTemp2("note3") & "</font><br>")
    end if
  rsTemp2.movelast
  end if
  rsTemp2.movenext
wend
set rsTemp2 = nothing
output.writeline("<p>" & rsInput("prod_footer"))
output.writeline("<hr><br><a href=""#page_top"">To Top of Page</a>")
output.writeline("</body>")
output.writeline("</html>")
output.close
response.write("done: " & lines & "<br>")
rsInput.movenext
else
response.write("error: " & lines)
rsInput.movenext
end if
wend
%>

Now for the fun part, first, parse through the html to select out the keywords...this is a kludgy, but effective, method of listing words to omit from the keyword list since the catalog uses a limited vocabulary in it's content. This section is being run within the product page while loop, so those values are resolved for vnum, and a new table is created for each of these, then fills it with results of the parsing filter. The table is then finally used to insert the keywords into the product pages. This is done as a version so the auto-numbering of filenames allows automating this step.

T.Mallard


<% Server.ScriptTimeOut = 2240 %>
set cmd1 = server.createobject("ADODB.Command")
cmd1.ActiveConnection = conn1
cmd1.CommandText = "update product set prod_page = '" &filename &"' where product.part_number = '" &rsInput("part_number") &"'"
set rsOutput = cmd1.execute
response.write("updated: " &rsInput("part_number") &"<br>")
set cmd1.ActiveConnection = nothing
set cmd1 = nothing
set conn2 = server.createobject("Scripting.FileSystemObject")
set output = conn2.OpenTextFile(htmlOut, 8, True)

set rsCreate = server.createobject("ADODB.Recordset")
set rsWords = server.createobject("ADODB.Recordset")

dim aword, alist, start
if series < 100 then
  if series < 10 then
  vnum = "00" &series
  else
  vnum = "0" &series
  end if
  else
  vnum = series
end if

version = cstr("metas_" &vnum)
sql01 = "CREATE TABLE metas_" &vnum & " (kword Text)"
rsCreate.open sql01, conn1, 2, 3, adCmdTxt
rsWords.open version, conn1, 2, 3, 2
response.write("table created: " &adate &"<br>" &vbCrLf)
stuff = rsInput("prod_content")
endcontent = len(stuff)
for i = 1 to endcontent step 1
letter = asc(mid(stuff, i, 1))
if letter = 60 then
  endtag = instr( i + 1, stuff, chr(62))
  i = endtag
else
  if not ((letter = 40) or (letter = 41) or (letter = 10) or (letter = 13) or (letter = 32) or (letter = 33) or (letter = 34) or (letter = 35) or (letter = 37) or (letter = 38) or (letter = 39) or (letter = 42) or (letter = 44) or (letter = 46) or (letter = 47) or (letter = 58) or (letter = 59) or (letter = 60) or (letter = 61) or (letter = 62) or (letter = 63) or (letter = 91) or (letter = 92) or (letter = 93) or (letter = 94) or (letter = 123) or (letter = 124) or (letter = 125) or (letter = 126) or (letter = 160)) then
  aword = aword &lcase(mid(stuff, i, 1))
   'use the next line to see what words are left, comment it out to run the build.
  response.write("letter: " &lcase(mid(stuff, i, 1)) &", ascii: " &letter &"<br>")
  else
    if not isempty(aword) then
      if ((aword = "up") or (aword = "v") or (aword = "@") or (aword = "+") or (aword = "c") or (aword = "0") or (aword = "longer") or (aword = "past") or (aword = "9") or (aword = "8") or (aword = "7") or (aword = "6") or (aword = "5") or (aword = "4") or (aword = "3") or (aword = "2") or (aword = "1") or (aword = "works") or (aword = "that") or (aword = "each") or (aword = "x") or (aword = "done") or (aword = "of") or (aword = "matter") or (aword = "last") or (aword = "furthermore") or (aword = "making") or (aword = "lower") or (aword = "a") or (aword = "today") or (aword = "easy") or (aword = "g") or (aword = "e") or (aword = "s") or (aword = "at") or (aword = "was") or (aword = "as") or (aword = "ends") or (aword = "both") or (aword = "providing") or (aword = "provide") or (aword = "for") or (aword = "front") or (aword = "less") or (aword = "performs") or (aword = "out") or (aword = "build") or (aword = "learning") or (aword = "speed") or (aword = "high") or (aword = "an") or (aword = "on") or (aword = "the") or (aword = "these") or (aword = "this") or (aword = "your") or (aword = "in") or (aword = "it") or (aword = "and") or (aword = "center") or (aword = "or") or (aword = "this") or (aword = "needs") or (aword = "to") or (aword = "is") or (aword = "comes") or (aword = "with") or (aword = "changes") or (aword = "meet") or (aword = "unique") or (aword = "two") or (aword = "designed") or (aword = "low") or (aword = "multiple") or (aword = "features") or (aword = "better") or (aword = "than") or (aword = "comparable")) then
      response.write("<b><font size=2>.</font></b>")
      aword = ""
    else
      if not (aword = ",") and not isempty(aword) then
        rsWords.addNew "kword", aword
        rsWords.update
        alist = alist & aword &";"
        aword = ""
      else
        aword = ""
        end if
      end if
    end if
  end if
end if
next
set rsCreate = nothing

T.Mallard
The keyword list is done. Well, at the same time the keyword list is made, it's filed under product number in the database. This was an important pass and since it really was fast to add words to the omit list, I thought for the results, it took zero coding time, but definitely took script processing time. The next pass transforms the table data to set up the following step of inserting the keywords into the pages, with a final pass in this series, gns_dat_03i.asp, to take off the trailing comma after these keywords were added, an ugly detail which I took another pass for.

 

<%@ Language=VBScript enablesessionstate = false %>
<%
= true %>
<% Server.ScriptTimeOut = 2240 %>
<%
adate = date
sql_01 =""
lines = 0
done = 0
alreadys = 0
series = 0
i=8

set conn1 = server.createobject("ADODB.Connection")
conn1.open "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=X:\webshare\wwwroot\gns\cgi\gns_01.mdb"
set rsInput = server.createobject("ADODB.Recordset")
set rsCreate = server.createobject("ADODB.Recordset")
set rsOutput = server.createobject("ADODB.Recordset")
if i < 100 then
if i < 10 then
ctaba = "metas_00" & i & "a"
else
ctaba = "metas_0" & i & "a"
end if
else
ctaba = "metas" & i & "a" end if
if i < 100 then
if i < 10 then
htmlpage = "d:\webshare\wwwroot\gns\v1\prod_00" & i & ".html"
htmlOut = "d:\webshare\wwwroot\gns\v1\prod_00" & i & "a.html"
else
htmlpage = "d:\webshare\wwwroot\gns\v1\prod_0" & i & ".html"
htmlOut = "d:\webshare\wwwroot\gns\v1\prod_0" & i & "a.html"
end if
else
htmlpage = "d:\webshare\wwwroot\gns\v1\prod_" & i & ".html"
htmlOut = "d:\webshare\wwwroot\gns\v1\prod_" & i & "a.html"
end if

set conn2 = server.createobject("Scripting.FileSystemObject")
set input = conn2.OpenTextFile(htmlpage, 1, True)

if not input.atendofstream then
apage = input.readall
start = instr(1, apage, "</title>")
if start > 0 then
place = start + 8

set output = conn2.CreateTextFile(htmlOut, 8, True)

rsInput.open ctaba, conn1, 2, 3, 2
while not rsInput.EOF and not rsInput.BOF
metas = metas & rsInput("kword") & ","
rsInput.movenext
wend
if not isempty(metas) then
metas = mid(metas, 1, len(metas)-1)
else
metas = "no_keywords"
end if
mtag = "<meta name=""keywords"" content=""" & metas & """>"
thepage = mid(apage, 1, place) & mtag & mid(apage, place)
output.write(thepage)
lines = lines + 1
response.write(thepage)
response.write(i & ":i<br>")
response.write(start & "<br>")
response.write("place: " & place & "<br>")
response.write("lines: " & lines & "<br>")
end if
end if
set rsOutput = nothing
set rsCreate = nothing
set rsInput = nothing
input.close
set conn1= nothing
set conn2= nothing
set conn3= nothing
%>


T.Mallard


Using ASP to Create a Website from Spreadsheets Continued





Finished with the product pages, but the catalog needs a heirarchy, so some asp pages were created to arrange several levels of indexes and also the pricing pages within the navigation schema. These are by product and group types. Then, the homepage was coded to be created from database sources to create drop-down select boxes by product or group. Since these two groupings rely on the same logic, slightly altered, here's the one for the index of product types.

set conn2 = server.createobject("Scripting.FileSystemObject")
htmlOut = "X:\webshare\wwwroot\gns\v1\type_price_00" & series & ".html"
set output = conn2.OpenTextFile(htmlOut, 8, True)
    ...typical html header and navigation response.write's go here...
set conn1 = server.createobject("ADODB.Connection")
conn1.open "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=X:\webshare\wwwroot\gns\cgi\gns_01.mdb"
set rsInput = server.createobject("ADODB.Recordset")
rsInput.open "product", conn1, 2, 3, 2
ptype = rsInput("prod_type")
tfooter = "<br><font size=1>Dynamic Content Resources by<br><a href='http://www.mallard-design.com/'>Mallard Design Company</a> ©1999</font><hr><p><a href=""#page_top"">To Top of Page</a>"
while not rsInput.EOF
series = series + 1
if (rsInput("prod_type") = ptype) or (series < 2) then
lines = lines + 1
output.writeline("<tr><td align=center>" & lcase(rsInput("prod_type")) & "</td><td align=""center"" bgcolor=""#ccffff""><a href=""" & rsInput("prod_page") & """ target=_top><font face='verdana,arial,helvetica' size=2>" & rsInput("prod_model") & "</font></a></td><td><font size=1>" & rsInput("part_number") & "</font></td><td align=""center"" bgcolor=""#eaeaea"">" & rsInput("description") & "</td><td align=right>$" & formatnumber(rsInput("sin132_price"), 0, -2) & "</td></tr>")
rsInput.movenext
else
'finish the page
output.writeline("</table><p>")
output.writeline("<font size=2 color=000000 face=""Arial,Helvetica,Verdana"">Notes:</font><br>")
set conn1 = server.createobject("ADODB.Connection")
conn1.open "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=X:\webshare\wwwroot\gns\cgi\gns_01.mdb"
set rsTemp3 = server.createobject("ADODB.Recordset")
rsTemp3.open "notes", conn1, 2, 3, 2
while not rsTemp3.EOF
if not (rsTemp3("prod_type") = ptype) then
  if done = 0 then
    done = 1
  if not isnull(rsTemp3("note1")) then
    output.writeline("<font size=1 color=000000 face=""Arial,Helvetica,Verdana"">" & rsTemp3("note1") & "</font><br>")
    end if
    if not isnull(rsTemp3("note2")) then
      output.writeline("<font size=1 color=000000 face=""Arial,Helvetica,Verdana"">" & rsTemp3("note2") & "</font><br>")
    end if
    if not isnull(rsTemp3("note3")) then
      output.writeline("<font size=1 color=000000 face=""Arial,Helvetica,Verdana"">" & rsTemp3("note3") & "</font><br>")
    end if
  end if
end if
rsTemp3.movenext
wend


T.Mallard



Using ASP to Create a Website from Spreadsheets Continued




Section of a product pricing page.
The result is the table listing on the left has the links to the product pages. Then there are the indexes, and below are the main index/homepage loops to create some drop-down select boxes on the page. From all of these, I hope it's obvious that these pages were easy to code, taking about the standard business week to finalize working on it between other things. Now, as things change, it takes a half-hour to create and publish the website from scratch. It has better navigation than the historical website, and a better clarity of the relationships between the products by using the database.

 


output.writeline("<select
name=""prodtype"">
")
set conn1 = server.createobject("ADODB.Connection")
conn1.open "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=X:\webshare\wwwroot\gns\cgi\gns_01.mdb"
set rsInput = server.createobject("ADODB.Recordset")
rsInput.open "product", conn1, 2, 3, 2
rsInput.movefirst
ptype = rsInput("prod_type")
pmodel = rsInput("prod_model")
pgroup = rsInput("prod_group")

while not rsInput.EOF
lines = lines + 1
if (lines = 1) then
  output.writeline("<option value=""" & rsInput("prod_type_page") & """>" & lcase(rsInput("prod_type")))
  end if
if rsInput("prod_type") = ptype then
  ptype = rsInput("prod_type")
  pmodel = rsInput("prod_model")
  pgroup = rsInput("prod_group")
  rsInput.movenext
else
  output.writeline("<option value=""" & rsInput("prod_type_page") & """>" & lcase(rsInput("prod_type")))
  ptype = rsInput("prod_type")
  pmodel = rsInput("prod_model")
  pgroup = rsInput("prod_group")
  rsInput.movenext
end if
wend

output.writeline("</select>  <font size=1><input type=""Button"" name=""ptype"" onclick=""gotype();"" value=""go"" align=""middle""></font>")
output.writeline("<select name=""prodgroup"">")
rsInput.movefirst
ppage = rsInput("prod_page")
pgroup = rsInput("prod_group")
lines = 0
output.writeline("<option value=""" & rsInput("prod_group_page") & """>" & lcase(rsInput("prod_group")))
response.write("moved first<br>")
while not rsInput.EOF
lines = line + 1
if (rsInput("prod_group") = pgroup) then
  response.write("equals: " & rsInput("prod_group")
& "<br>")
  ppage = rsInput("prod_page")
  pgroup = rsInput("prod_group")
  rsInput.movenext
else
  output.writeline("<option value=""" & rsInput("prod_page") & """>" & lcase(rsInput("prod_group")))
  ppage = rsInput("prod_page")
  pgroup = rsInput("prod_group")
  rsInput.movenext
end if
wend
output.writeline("</select>  <font size=1><input type=""Button"" name=""pgroup"" onclick=""gogroup();"" value=""go"" align=""middle""></font>")

This flow started with Excel files having text and numeric data mixed which was hand pasted into Access table fields created with wizards to establish a database to build an entire website from, including auto-numbered paging, next-previous buttons, and indexes to the two main divisions of product type. The original html pages were used to create keyword lists for each product page which were then added back into the page. And, the main homepage is also built from the database.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved