Multiple Column Table db results

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!

Re: Multiple Column Table db results

"Jake" 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 "

Re: Multiple Column Table db results

Thanks! Just what I was looking for but now Im curious about the Mail Merge.

I know how to generate a csv but how do you do the mail merge? Or is that on
the MS Word side?

Thanks Again!


"McKirahan" wrote in message
news:MZWdnb9knNHsqZHfRVn-3A@comcast.com...
> "Jake" 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 "

Re: Multiple Column Table db results

"Jake" wrote in message
news:e##x9Q$DFHA.208@TK2MSFTNGP12.phx.gbl...
> Thanks! Just what I was looking for but now Im curious about the Mail
Merge.
>
> I know how to generate a csv but how do you do the mail merge? Or is that
on
> the MS Word side?
>
> Thanks Again!

You're welcome!

You should study up om MS-Word's Mail Merge feature as I now see that you
can use MS-Access as a source without having to generate a CSV file first
(unless you want to). You can specify the Avery label type you're using so
formatting's a breeze. Below is an extract of Microsoft Word Help:


Overview of mail merge


How do I start a mail merge?

You can use the Mail Merge Helper to create form letters, mailing labels,
envelopes, or catalogs. The Mail Merge Helper guides you through organizing
the address data, merging it into a generic document, and printing the
resulting personalized documents.

Open or create a main document, which contains the generic information that
you want to repeat in each form letter, mailing label, envelope, or catalog.

Open or create a data source, which contains the data that varies in the
merged documents - for example, the name and address of each recipient of a
form letter. The data source can be an existing spreadsheet, database, or
text file, or a Word table that you create by using the Mail Merge Helper.

In the main document, insert merge fields, which are placeholders that tell
Microsoft Word where to insert data from the data source.

Merge data from the data source into the main document. Each row (or record)
in the data source produces an individual form letter, mailing label,
envelope, or catalog item. You can send the merged documents directly to a
printer, or to e-mail addresses or fax numbers. Or you can collect the
merged documents into a new document so you can review and print them later.

Note If you're sending mail to U.S. addresses, and you're using one of the
following versions of Microsoft Office (Small Business, Professional, or
Premium), then you can use Direct Mail Manager to create form letters,
mailing labels, and envelopes for a direct mailing, take advantage of U.S.
Postal Service bulk mail discounts, and find out about services that can
print and deliver a mailing for you. For more information, see Direct Mail
Manager Help.

What types of data sources can I use?

You can use just about any type of data source that you want, including a
Word table, Microsoft Outlook contact list, Excel worksheet, Microsoft
Access database, or ASCII text file. If you haven't already stored
information in a data source, Word guides you step by step through setting
up a Word table that contains your names, addresses, and other data. Learn
about mail-merge data sources.

How do I customize a mail merge?

To customize a mail merge, you can specify which data to merge, preview the
merged documents, and fine-tune the results. For example, you can target
specific customers, such as those in specific postal code areas. Or you can
have Word prompt you to enter a client's appointment date and time.

Re: Multiple Column Table db results

"Jake" wrote in message
news:e##x9Q$DFHA.208@TK2MSFTNGP12.phx.gbl...
> Thanks! Just what I was looking for but now Im curious about the Mail
Merge.
>
> I know how to generate a csv but how do you do the mail merge? Or is that
on
> the MS Word side?
>
> Thanks Again!

An update.

The following should be added/changed:

Const adCmdText = &H0001

Set objRST = objADO.Execute(strSQL,,adCmdText)


Per Bob Barrows:

"always tell ADO what the commandtype is. Don't make it guess. It takes a
couple nanoseconds for it to make its guess, so performance really is not
the issue here: in rare cases it can guess wrong, leading to symptoms that
will be very difficult to debug. The Execute method takes one required
argument, and two optional arguments: the third argument is where you can
specify the command type and any execution options that are relevant."