Re: In 3 col x 7 row table cannot add less than 21 records

Hi Paulo,

Here's the complete coding:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using iTextSharp.text;
using iTextSharp.text.pdf;
using MySql.Data.MySqlClient;
using System.IO;
 
 
 
 
 
public partial class _Default : System.Web.UI.Page
{
    static string pdfFileLocation;
 
    protected void Page_Load(object sender, EventArgs e)
    {
 
        int UserID;
 
        if (!int.TryParse(Request.QueryString["UserID"], out UserID))
        {
            //Console.WriteLine("User ID not passed to page");     // Handle error
           //this.form1.
        }
 
        else
        {
     
       
       
        Document doc = new Document(PageSize.A4, 12, 15, 30, 0);
        pdfFileLocation = @"D:\inetpub\DomainID54847\ConvertToPdf\Labels for User " + UserID + ".pdf";
 
        //if pdf file present, delete it
        if (System.IO.File.Exists(pdfFileLocation))
        {
            System.IO.File.Delete(pdfFileLocation);
        }    
           
           
            //string pdfFileLocation = "Test.pdf";
        System.IO.FileStream docStream = new System.IO.FileStream(pdfFileLocation, System.IO.FileMode.Create);
        PdfWriter.GetInstance(doc, docStream);
           
           
        doc.Open();
       
        //PdfPTable table = new PdfPTable(2);
        PdfPTable table = new PdfPTable(3);
 
        //actual width of table in points
        table.TotalWidth = 580f;
       
        //fix the absolute width of the table
        table.LockedWidth = true;
 
       
        //relative col widths in proportions - 1/3 and 2/3
        //float[] widths = new float[] { 1f, 2f };
 
        //relative widths 1/3 1/3 1/3
        float[] widths = new float[] { 1f, 1f, 1f };
 
 
        table.SetWidths(widths);
        table.HorizontalAlignment = 0;
        //leave a gap before and after the table
        //table.SpacingBefore = 42f;
        //table.SpacingAfter = 30f;
 
        table.DefaultCell.Border = 0;
        table.DefaultCell.PaddingLeft = 5;
       
       
        float TopPad = 12.5F;
        table.DefaultCell.PaddingTop = TopPad;
       
        table.DefaultCell.PaddingBottom = 30;
       
       
        PdfPCell cell = new PdfPCell();
 
       
        cell.FixedHeight = 100;
 
        //cell.Colspan = 2;
        cell.Colspan = 3;
        cell.Border = 0;
        cell.HorizontalAlignment = 1;
       
 
 
        //table.AddCell(cell);   NB MAY HAVE TO UNQUOTE THIS!!!!!
 
        BaseFont bfTimes = BaseFont.CreateFont(BaseFont.TIMES_ROMAN, BaseFont.CP1252, false);
        Font times = new Font(bfTimes, 10);
 
 
        // Create the select command string
        // The school name is reduced in length to 38 characters to avoid it spilling over to two lines on the label
            //int UserID = int.Parse(Request.QueryString["UserID"]);
 
        string cmdstring = "select fldContact, Name, If(trim(addr1)='','.  ', addr1) as ad1, If(trim(addr2)='','.  ',addr2) as ad2," +
            "If(trim(addr3)='','.  ',addr3) as ad3, If(trim(addr4)='','.  ',addr4) as ad4, If(trim(addr5)='','.  ',addr5) as ad5 " +
            "FROM (" +
            "select fldContact, LEFT(trim(fldName),38) AS Name, fldADDR1, fldADDR2, fldTOWN, fldCOUNTY, fldPOSTCODE, ad," +
               "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 1)," +
                                 "LENGTH(SUBSTRING_INDEX(ad, char(26), 1 -1)) + 1)," +
                                 "char(26), '') as addr1," +
               "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 2)," +
                                 "LENGTH(SUBSTRING_INDEX(ad, char(26), 2 -1)) + 1)," +
                                 "char(26), '') as addr2," +
               "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 3)," +
                                 "LENGTH(SUBSTRING_INDEX(ad, char(26), 3 -1)) + 1)," +
                                 "char(26), '') as addr3," +
               "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 4), " +
                                 "LENGTH(SUBSTRING_INDEX(ad, char(26), 4 -1)) + 1)," +
                                 "char(26), '') as addr4," +
               "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 5)," +
                                 "LENGTH(SUBSTRING_INDEX(ad, char(26), 5 -1)) + 1)," +
                                 "char(26), '') as addr5 " +
          "from (" +
        "SELECT fldContact, fldName, fldADDR1, fldADDR2, fldTOWN, fldCOUNTY, fldPOSTCODE," +
               "concat_ws(char(26),nullif(trim(fldADDR1),'')," +
                                  "nullif(trim(fldADDR2),'')," +
                                  "nullif(trim(fldTOWN),'')," +
                                  "nullif(trim(fldCOUNTY),'')," +
                                  "nullif(trim(fldPOSTCODE),'')) as ad " +
          "FROM tblschools RIGHT JOIN tblselectedschools ON tblschools.fldSCHOOL_ID = tblselectedschools.fldSCHOOL_ID " +
        "WHERE tblselectedschools.fldUserID = " + UserID.ToString() + " AND tblselectedschools.fldSelected = 1 ORDER BY fldName, fldPOSTCODE) as v" +
        ") as v1;";    
 
 
 
         
       
       
        // Create the Connectionstring
     // PersistSecurityInfo = false means that the password is not displayed in the
     // connectionstring of the MySqlCommand.
     //
 
   
       
 
    MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
    connBuilder.Database ="xxx";
    connBuilder.Password ="xxxx";
    connBuilder.PersistSecurityInfo = false;
    connBuilder.Server = "xxxxx";
    connBuilder.UseCompression = true;
    connBuilder.UserID = "xxxxx";
 
    // Create the connection
    MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
   
   
 
 
 
        MySqlCommand comm = new MySqlCommand(cmdstring, conn);
 
//comm.Parameters.AddWithValue("userID", 2);
comm.Parameters.AddWithValue("selected", 1);
    //comm.Parameters.AddWithValue("userID", 2);
    //comm.Parameters.AddWithValue("selected", 1);
 
    try {
      // Open and execute the command
      conn.Open();
     
   
 
      using (MySqlDataReader reader = comm.ExecuteReader())
      {
          while (reader.Read())
          {
              string text = string.Format(
                "{0}\r\n{1}\r\n{2}\r\n{3}\r\n{4}\r\n{5}\r\n{6}",
                reader["fldContact"],
                reader["Name"],
                reader["ad1"],
                reader["ad2"],
                reader["ad3"],
                reader["ad4"],
                reader["ad5"]);
 
              Phrase myPhrase = new Phrase(text, times);
             
              table.AddCell(myPhrase);
             
          }
      }    
 
 
 
      doc.Add(table);
 
  }
  finally
  {
      // Close should always be called now
      // If the PDF has no contents, an exception will not be caught,
      // but at least the file will be closed (and unlocked)
      try
      {
          doc.Close();
      }
      finally
      {
          // Also make sure the underlying stream is closed
          // You could delete the file here if it's empty
          docStream.Close();
         
      }
 
      conn.Dispose();
      comm.Dispose();
  }
 
 
 
        }  //else
        this.lblInstructions.Text = "When Acrobat Reader displays your labels, your can:  <br>" +
            "PRINT by selecting FILE/PRINT. You can then set print options, for example number of pages, etc <br>" +
            "COPY the labels by selecting FILE/SAVE COPY AS to save a copy on your machine for printing later";
       
    }
 
    protected void ViewPrintPdf(object sender, EventArgs e)
    {
 
  FileInfo fi = new FileInfo(pdfFileLocation);
 
  Response.Clear();
  Response.ContentType = "application/pdf";
  Response.AddHeader("Content-Disposition", string.Format(
    "attachment; filename=\"{0}\";", fi.Name));
  Response.WriteFile(fi.FullName);
  Response.End();
}
}

Paulo Soares-3 wrote:

Where's the table creation?

Paulo

> -----Original Message-----
> From: bogorman [mailto:brian_og@hotmail.com]
> Sent: Monday, June 08, 2009 6:30 PM
> To: itextsharp-questions@lists.sourceforge.net
> Subject: [itextsharp-questions] In 3 col x 7 row table cannot
> add less than 21 records
>
>
> I am using itextsharp to create a pdf containing a table with
> the above
> format and then read text from a mySQL database into its
> cells. Everything
> works ok unless:
> a) there are less than 21 records in the recordset. I then
> get the error
> "the document has no pages"
> b) if there are more than 21 records in the recordset and
> total number of
> records in the recordset is not  a multiple of 21, the pdf is
> created but
> the last page is missing (i.e. the one which should contain
> less than 21
> records).
>
> The part of the coding which reads in the data is:
>
>
>
>     try {
>       // Open and execute the command
>       conn.Open();
>      
>    
>  
>       using (MySqlDataReader reader = comm.ExecuteReader())
>       {
>           while (reader.Read())
>           {
>               string text = string.Format(
>                 "{0}\r\n{1}\r\n{2}\r\n{3}\r\n{4}\r\n{5}\r\n{6}",
>                 reader["fldContact"],
>                 reader["Name"],
>                 reader["ad1"],
>                 reader["ad2"],
>                 reader["ad3"],
>                 reader["ad4"],
>                 reader["ad5"]);
>  
>               Phrase myPhrase = new Phrase(text, times);
>              
>               table.AddCell(myPhrase);
>              
>           }
>       }    
>  
>  
>  
>       doc.Add(table);
>
>   }
>   finally
>   {
>       // Close should always be called now
>       // If the PDF has no contents, an exception will not be caught,
>       // but at least the file will be closed (and unlocked)
>       try
>       {
>           doc.Close();
>       }
>       finally
>       {
>           // Also make sure the underlying stream is closed
>           // You could delete the file here if it's empty
>           docStream.Close();
>          
>       }
>
>       conn.Dispose();
>       comm.Dispose();
>   }
>  
>
> fldContact, Name, ad1, ad2, ad3, ad4 and ad5 are fields in
> the recordset. I
> have run the debugger in VS2005 and checked that the data is
> correct. It is.
>
> Would very much appreciate anyones help on this.


Aviso Legal:

Esta mensagem é destinada exclusivamente ao destinatário. Pode conter informação confidencial ou legalmente protegida. A incorrecta transmissão desta mensagem não significa a perca de confidencialidade. Se esta mensagem for recebida por engano, por favor envie-a de volta para o remetente e apague-a do seu sistema de imediato. É proibido a qualquer pessoa que não o destinatário de usar, revelar ou distribuir qualquer parte desta mensagem.



Disclaimer:

This message is destined exclusively to the intended receiver. It may contain confidential or legally protected information. The incorrect transmission of this message does not mean the loss of its confidentiality. If this message is received by mistake, please send it back to the sender and delete it from your system immediately. It is forbidden to any person who is not the intended receiver to use, distribute or copy any part of this message.




------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
itextsharp-questions mailing list
itextsharp-questions@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/itextsharp-questions