Re: Multiple Column Table db results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: McKirahan (News_at_McKirahan.com)
Date: 02/11/05 

Date: Thu, 10 Feb 2005 22:30:26 -0600

"Jake" <spamthis@alltel.net> wrote in message
news:uYpgCf#DFHA.3596@TK2MSFTNGP12.phx.gbl...
> How can I display my db results in a multi-column table?
>
> Need to build a formatted mailing label print page. I need to display a 3
> column table and also have a page break after every 10 rows.
>
> How can I do this?
>
> TIA!
>

I suggest that instead of generating a Web page with labels you generate a
CSV file and do a Mail Merge with a (Avery) Label document in MS-Word.

Here's what you asked for. Watch for word-wrap.

It uses the FPNWIND.MDB which ships with FrontPage.

<% @Language="VBScript" %>
<% Option Explicit
'****
' This ASP (Active Server Pages) program does the following:
' 1) Reads the FP Northwind "Customers" table into an array.
' 2) Generates mailing labels; 3 columns wide, 10 rows per page.
'****
   '*
   '* Declare Constants
   '*
    Const cASP = "FPNWind.asp"
    Const cMDB = "FPNWind.mdb"
    Const cDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
    Const cROW = 10
   '*
   '* Declare Variables
   '*
    Dim aSTR()
  ReDim aSTR(100)
    Dim iSTR
        iSTR = 0
    Dim sSTR
   '*
    Dim intROW
        intROW = 0
    Dim arrRST()
  ReDim arrRST(3,2,0)
   ' arrRST(0,#,#) = CompanyName
   ' arrRST(1,#,#) = ContactName
   ' arrRST(2,#,#) = Address
   ' arrRST(3,#,#) = City, (Region,) Country
   ' arrRST(#,0,#) = Label #1
   ' arrRST(#,1,#) = Label #2
   ' arrRST(#,2,#) = Label #3
   ' arrRST(#,#,0) = Row #1+
    Dim intRS1
        intRS1 = 0
    Dim intRS2
        intRS2 = 0
    Dim intRS3
        intRS3 = 0
    Dim strRST
    Dim strSQL
        strSQL = "SELECT CompanyName, ContactName,"
        strSQL = strSQL & " Address, City, Region, Country"
        strSQL = strSQL & " FROM Customers"
        strSQL = strSQL & " ORDER BY CompanyName"
   '*
   '* Declare Objects
   '*
    Dim objADO
    Set objADO = Server.CreateObject("ADODB.Connection")
        objADO.Open cDSN & Server.MapPath(cMDB)
    Dim objRST
    Set objRST = objADO.Execute(strSQL)
   '*
   '* Read RecordSet
   '*
    Do While Not objRST.EOF
        If intRS2 > UBound(arrRST,2) Then
            intRS1 = UBound(arrRST,1)
            intRS2 = UBound(arrRST,2)
            intRS3 = intRS3 + 1
            ReDim Preserve arrRST(intRS1,intRS2,intRS3)
            intRS2 = 0
        End If
        arrRST(0,intRS2,intRS3) = objRST("CompanyName")
        arrRST(1,intRS2,intRS3) = objRST("ContactName")
        arrRST(2,intRS2,intRS3) = objRST("Address")
        strRST = objRST("City") & ", "
        If objRST("Region") <> "" Then
            strRST = strRST & objRST("Region") & ", "
        End If
        strRST = strRST & objRST("Country")
        arrRST(3,intRS2,intRS3) = strRST
        intRS2 = intRS2 + 1
        objRST.MoveNext
    Loop
   '*
   '* Destroy Objects
   '*
    Set objRST = Nothing
        objADO.Close
    Set objADO = Nothing
   '*
   '* Build Labels
   '*
    Append "<table border='0' width='700'>"
    For intRS3 = 0 To UBound(arrRST,3)
        If intRS3 > 0 _
        And intRS3 Mod cROW = 0 Then
            Append "</table>"
            Append "<div class='page'></div>"
            Append "<table border='0' width='700'>"
        End If
        Append "<tr>"
        For intRS2 = 0 To UBound(arrRST,2)
            Append " <td width='33%'>"
            For intRS1 = 0 To UBound(arrRST,1)
                Append " <br>" & arrRST(intRS1,intRS2,intRS3)
            Next
            Append " </td>"
        Next
        Append "</tr>"
    Next
    Append "</table>"

Sub Append(sSTR)
'****
'* Appends strings to array entries ReDim as needed; (see "Concat()").
'****
    sSTR = sSTR & ""
    If iSTR > UBound(aSTR) Then ReDim Preserve aSTR(UBound(aSTR) + 100)
    aSTR(iSTR) = sSTR & vbCrLf
    iSTR = iSTR + 1
End Sub

Function Concat()
'****
'* Concatenates array entries into a single string; (see "Append()").
'****
    Redim Preserve aSTR(iSTR)
    Concat = Replace(Join(aSTR,""),"`",Chr(34))
    Erase aSTR
    ReDim aSTR(100)
    iSTR = 0
End Function
%>
<html>
<head>
<title><%=cASP%></title>
<style type="text/css">
 body { font-family:Arial; font-size:9pt }
 td { font-family:Arial; font-size:9pt }
 th { font-family:Arial; font-size:9pt; font-weight:bold }
.page { page-break-before:always }
</style>
</head>
<body>
<%=Concat%>
</body>
</html>

Relevant Pages

  • RE: Sections in repeater
    ... Setting "display: ... Dim lblSection As Label ... If lastCity = c.City Then ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • Re: Shipping Label Design
    ... BusinessAddressStreet ... After changing the name of the file in the program to .dotx, my template ... Interestingly, when I saved the "label" which I made with your program, it ... It highlighted "Dim oWord As Word.Application" ...
    (microsoft.public.word.docmanagement)
  • Re: Default use Specific Printer and Label Size
    ... Thanks, however this is a .mde file, so the design view wouldn't be available. ... Dim rpt As Access.Report ... Dim lngNumber As Long ... 'print to the label printer ...
    (comp.databases.ms-access)
  • Re: Default use Specific Printer and Label Size
    ... Dim rpt As Access.Report ... Dim lngNumber As Long ... 'print to the label printer ... CNames, gvarErrNum, gvarErrDesc, rvarErrorMsg) ...
    (comp.databases.ms-access)
  • Re: Default use Specific Printer and Label Size
    ... Dim rpt As Access.Report ... Dim lngNumber As Long ... 'print to the label printer ... CNames, gvarErrNum, gvarErrDesc, rvarErrorMsg) ...
    (comp.databases.ms-access)