ServerWatch content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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:websharewwwrootgnscgigns_01.mdb“ set rsInput = server.createobject(“ADODB.Recordset“) set rsOutput = server.createobject(“ADODB.Recordset“) |
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.
