Strange Situation re: Retrieval & Display of Records

[Rexolio]

Okay...this is a confusing one from the get-go, so I don't know how much sense all of this will make and I'm not sure that I will explain this sufficiently. But I'm at my wit's end and I need your help...

I'm using ActivePDF to print PDF mailing labels. The mailing label stock is 30 to a page in rows of 3. The information for the labels, obviously, is pulled from a database. The problem I'm having is that the standard <% if not n mod 3 = 0 %> that allows my records to be retrieved and displayed horizontally, 3 across, then start a new row won't cut it for ActivePDF.

Obviously each record will consist of Name (1st line), Address (2nd line), and City/State/Zip (3rd line). The problem with ActivePDF is that as far as I can tell, is that I can't print record #1's info on 3 lines of one row, then record #2's info on 3 lines of the same row and ditto with record #3. ActivePDF doesn't seem to use &quot;rows&quot;, but instead &quot;lines&quot;. Sooo...

For this example, TRUE means stay on line; FALSE means move to next line. It should be like this (simplified):

PrintLine Record1(Name) at 0.35&quot;, TRUE
PrintLine Record2(Name) at 3.5&quot;, TRUE
PrintLine Record3(Name) at 6.0&quot;, FALSE - move to next line
PrintLine Record1(Address) at 0.35&quot;, TRUE
PrintLine Record2(Address) at 3.5&quot;, TRUE
PrintLine Record3(Address) at 6.0&quot;, FALSE - move to next line

I THINK you man get the picture...I don't know how to say &quot;Pull first 3 records' Name, then go back and pull the Address for those same 3 records, then...&quot; Don't know how to go in and out of a record like that and still continue on consecutively if there's several records.

Am I making ANY sense?

Thanks,
Rexolio

 

[LucyP]

Hi

there may be a better solution to this, but here's something that will acheive what you need using much less processor time than what you suggest.

What you need to do is write the recordset to an array (which you can work with much more quickly) and then write a loop for it.

I've no idea about the Acrobat stuff, but if the sql you use to create the recordset is
SELECT name, address1, address2 FROM database

then your vbscript would be something like this (I'll write the output to a variable called output):

arrnames = recordset.getrows

[this gives you an array containing all of the data - it has 3 columns and as many rows as there are records]

for i = 0 to ubound(arrnames,2)
if i mod 3 = 0 then 'ie the rownumber is 0,3,etc
output = Arrnames(1,i) & vbtab & Arrnames(1,i+1) & vbtab & Arrnames(1,i+2)
output=output & Arrnames(2,i) & vbtab & Arrnames(2,i+1) & vbtab & Arrnames(2,i+2)
.....[you get the picture]
elseif i mod 3 = 1
.....
end if
next

I know it's still a bit messy, but it will work.

 

[Rexolio]

Lucy,

Thanks for replying back. I hate to be such a moron, but I don't think your array helped me very much as I don't understand how it will apply to my situation.

You exampled the following:
SELECT name, address1, address2 FROM database

Then you exampled:
arrnames = recordset.getrows

for i = 0 to ubound(arrnames,2)
if i mod 3 = 0 then 'ie the rownumber is 0,3,etc
output = Arrnames(1,i) & vbtab & Arrnames(1,i+1) & vbtab & Arrnames(1,i+2)
output=output & Arrnames(2,i) & vbtab & Arrnames(2,i+1) & vbtab & Arrnames(2,i+2)
.....[you get the picture]
elseif i mod 3 = 1
.....
end if
next

Again, sorry to be a dork, but I don't understand where name, address1 and address2 would fit in here. I don't understand the use of the digits 1 and 2 in the array. Need to understand so that I can see how it would apply for the purposes above.

I appreciate your time very much, as well as anyone else you have any suggestions.

Thanks,
Rexolio

 

[jcj88]

The 1 and 2 in the array are the database columns. When you use GetRows, it dims the first dimension to 0 to n-1 fields pulled from the database. The second dimension is the row number (0 to m-1 rows).

So in her example above, Arrnames(1,i) is the &quot;Address1&quot; column from the i-th row in the array.

Be careful of EOF though, the entire loop will bomb because the array was not intialized. Just check for EOF before doing arrnames and set a boolean flag:
If rs.EOF Then
booData = False
Else
booData = True
arrNames = rs.GetRows()
End If

Good luck, but in return I have a quick question on ActivePDF: Do you know if you can create a dynamic PDF that can be sent directly to the browser via the Response stream? (i.e. It does not create a physical file on the server). We are evaluating ActivePDF for use on our site.

Thanks!
Jay

 

[Rexolio]

jc,

thanks for your assistance. to my knowledge and from what I can tell, you CAN send a dynamic pdf to the browser via the response stream. There is a few lines of code that does this in an example that I found on the activePDF web site (very well hidden.) I'm not sure if you have this example, but it is where I'm drawing a lot of my knowledge (as well as questions) on the subject. If you don't have it, you can get it here:

http://www.activepdf.com/examples.asp?ID=ASP0008

In a moment I plan on posting what I believe to be the correct code utilizing what you and Lucy have given me from above. I'd very much appreciate your feedback on its accuracy.

thanks,
Rexolio

 

[Rexolio]

jc,

if you can gather what you need from this, the code you're looking is as follows from what I can tell:

if bDidPrint = true then
i = APServer.Wait(30)
end if

Response.Write &quot;<HTML><BODY TOPMARGIN=0 LEFTMARGIN=0 SCROLL=no>&quot; & vbCrLf
Response.Write &quot;<embed width=100% height=100% fullscreen=yes src=&quot;&quot;&quot; & sName & &quot;&quot;&quot;>&quot; & vbCrLf
Response.Write &quot;</BODY></HTML>&quot; & vbCrLf

Set APServer = Nothing
Set APPrinter = Nothing

Conn.Close
Set Conn = Nothing

I placed the lines above and below the Response lines in the hopes that you could better reference where they should be placed.

Thanks,
Rexolio

 

[Rexolio]

I left this line out...goes right before the first response line:
sName = &quot;pdf/&quot; & APServer.NewUniqueID & &quot;.pdf&quot;

 

[Rexolio]

Okay guys, is THIS how it should be done?

sqlstr = &quot;SELECT name, address, city, state, zip FROM my_table&quot;
Set rs = Conn.Execute(sqlstr)

If rs.EOF Then
booData = False
Else
booData = True
arrNames = rs.GetRows()
End If

for i = 0 to ubound(arrnames,2)
if i mod 3 = 0 then
output = Arrnames(name,i) & vbtab & Arrnames(name,i+1) & vbtab & Arrnames(name,i+2)
output = output & Arrnames(address,i) & vbtab & Arrnames(address,i+1) & vbtab & Arrnames(address,i+2)
output = ...and on until the last field
elseif (i + 1) mod 3 = 0 then
output = Arrnames(name,i)
output = output & Arrnames(address,i)
output = ...and on
else
output = Arrnames(name,i) & vbtab & Arrnames(name,i+1)
output = output & Arrnames(address,i) & vbtab & Arrnames(address,i+1)
output = ...and on
end if
next

 

[jcj88]

Thanks for the info on activePDF.

For your code, you need to check the boolean variable before doing the loop (you will get an error otherwise):
If booData Then
...
End If

Also, in the array you need to reference a number and not the actual name of the column. For instance, 0 = name, 1 = address, etc.

Other than that it looks good as far as my eye-checker can tell.

Thanks,
Jay

 

[Rexolio]

Okay...last time I bother anyone in this post with this. Below is the actual script I am using. I keep getting a Subscript error.

sqlstr = &quot;SELECT * FROM my_table WHERE inputdate = &quot; & sInDate
Set rs = Conn.Execute(sqlstr)

If rs.EOF Then
booData = False
Else
booData = True
arrNames = rs.GetRows()

for i = 0 to ubound(arrnames,2)
if i mod 3 = 0 then
output = Arrnames(1,i) & vbtab & Arrnames(1,i+1) & vbtab & Arrnames(1,i+2) <-- error on this line
output = output & Arrnames(2,i) & vbtab & Arrnames(2,i+1) & vbtab & Arrnames(2,i+2)
output = ...and on until the last field
elseif (i + 1) mod 3 = 0 then
output = Arrnames(1,i)
output = output & Arrnames(2,i)
output = ...and on
else
output = Arrnames(1,i) & vbtab & Arrnames(1,i+1)
output = output & Arrnames(2,i) & vbtab & Arrnames(2,i+1)
output = ...and on
end if
next
end if

I'm getting a Subscript out of range: '[number: 47]' on the very first line that begins with &quot;output&quot;. 47 is the number of records for the specified criteria in my database. The only thing I haven't entered here is:

1 = name
2 = address
3 = city

that JC referenced. I've tried a couple of different placements of this, but I get errors on those lines. HELP.

 

[jcj88]

In VB Script, arrays are always zero-based. So 0=Name, not 1.

Also, speaking as a DBA, don't use Select *, use the actual column names. Otherwise you may get unnecessary pseudo-columns (depending on the rdbms) or cause the database to take an inefficient path to the data.

 

[Rexolio]

Okay, now I get this message:

Microsoft VBScript compilation error '800a0400'

Expected statement

labels.asp, line 424

0 = name
^

How exactly should this string begin and where in the code above to I place it

I know, I know...promised the last one was my last question, but this one is, I promise!!

 

[jcj88]

Sorry for the confusion, it should be:

output = Arrnames(0,i)

NOT

output = Arrnames(0,i)

Basically, just subtract one from the first column in the array.

But now I noticed another error, you need to check that you are not above the UBOUND before referencing i+1 and i+2. For example, lets say you are on record 46 (i=45) of 47 records. i+1 = 46 (record # 47), which is ok, but i+2=47 (record 48, doesn't exist) which is out of the array bounds.

Hope this helps...

 

[jcj88]

oops, meant:
output = Arrnames(0,i)
NOT
output = Arrnames(1,i)

...For the Name column.

 

[Rexolio]

hmmm...but I had changed that and still getting same message.

 

[jcj88]

On this line, you could possibly over-step the array bounds b/c of the i+1 and i+2 in the arrays:

output = Arrnames(1,i) & vbtab & Arrnames(1,i+1) & vbtab & Arrnames(1,i+2)

You probably need to check if i+1 and i+2 are over the UBOUND (something like this):

If i+1 > ubound(arrnames,2) OR i+2 > ubound(arrnames,2) Then
'do something else
Else
output = Arrnames(1,i) & vbtab & Arrnames(1,i+1)
& vbtab & Arrnames(1,i+2)
End If

Jay