Printing Avery Address Labels From SQL Database

General
1

Hello,

I was wondering if anyone knows where I can get some sample code for printing address labels (30 on a page) on Avery labels from a SQL database. I am sure someone has already done this. I need to get a quick mailing list out to customers, and I don’t have a lot of time to build my own. If anyone knows where there is some sample code, I would certainly appreciate it.

Normally I would do this myself, but I am under the gun to get this completed. Any help would be appreciated.

Jim

2

If you don’t get any XOJO specific replies and your in that much of a hurry maybe using Excel and ODBC would be a quicker option?

3

I think excel+ODBC is better. I have some for labels but don’t sure the size, do you have a Avery’s Product Number?

4

For a one and done like this I would use a Word merge. Then when you have time implement a more automated solution.

5

Well, I actually have some older code that I got somewhere (can’t remember where or whose it is) and it almost works after tweaking some of the settings for a while here.

The problem I am having now is setting the left margin so it is set off to the right a little more. Normally you can do this by doing the following:

g.DrawString("Hello World"), 20, 20

But my line of code is:

 g.DrawString customer,  columnIndex * columnWidth, rowOffset + g.TextAscent

Can anyone tell me how I can change the code so that margin starts over about 1/4 inch more to the right? Any help would be appreciated. The full printing label code is shown below.

Any help would be greatly appreciated.

Jim

[code] dim g as Graphics
dim rs As RecordSet
dim customer as String
dim columnIndex, columnWidth as Integer
dim rowOffset, rowHeight as integer

g = OpenPrinterDialog()

// if user clicks cancel Nil
if g = NIL then
return’ true
end

// Set the text font and size that we want to print with

g.TextFont = “Arial”
g.TextSize = 10

// calculate how wide each column is – divided the width of page by 3

const kColumncount = 3
columnWidth = g.Width /kColumncount + 20

// Calculate height of each record (7 lines of text)
//rowHeight = g.TextHeight * 7
rowHeight = 62

// select customer fields from database

Dim sqlRecords as string
sqlRecords = “select * from Members”
rs=Roster.SQLSelect(sqlRecords)

//move to top of row of page
rowOffset = 27

// loop through records and dra page toprint out
while not rs.eof

// draws 3 records into the column
for columnIndex = 0 to kColumncount-1
  if rs.eof then
    exit
  end
  //format data into a labels
  
  customer =  rs.Field("First").StringValue + " " + rs.Field("Last").StringValue +EndOfLine // have to space out "1234"
  customer = customer + rs.Field("Street").StringValue + EndOfLine // Have to space out "1234"
  //customer = customer + rs.Field("Street2").StringValue + EndOfLine // Have to space out "1234"
  customer = customer + rs.Field("Town").StringValue + ", " + rs.Field("State").StringValue + " " + rs.Field("Zip").StringValue //have to space out "1234"
  // all upper case
  customer = Uppercase(customer)
  
  // draw to page
  g.DrawString customer,  columnIndex * columnWidth, rowOffset + g.TextAscent
  
  rs.MoveNext
Next

//if not records then finish printing
if rs.EOF then
  Exit
end
// finish printing that row move t next row for columns to be drawn

rowOffset = rowOffset +(rowHeight) +g.TextHeight

// if moved to end of page send to printer
if (rowOffset + 20) > g.Height then
  
  //send to printer and clear graphics class for next page
  g.NextPage
  
  //move to top of row of page
  rowOffset = 27
end if

Wend

//the last page is sent to the printer when the graphic class goes out of scope
return’ true //we handled this menu action[/code]