how to print multiple newly page for multiple different invoice number in c#.net using sap crystal report

Question

I want to print multiple page according to their invoice number. For example, there is two option for print the bill that is from and to, it means from invoice number to to invoice number i.e. I want to print invoice number 6 to 10 and for different invoice number different page would be print. From my following code multiple page is showing but not according to the invoice number it showing according to the data[i.e. when the data is full on one page then it shows the second page]. But I don't want this one.

Here is my code,

try
{
    string mtmptbl = "TmpTaxInvoicePrint";
    RetailInvoicePrint frm = new RetailInvoicePrint();
    Cursor = Cursors.WaitCursor;

    ReportDocument cryRpt = new ReportDocument();
    SqlCommand MyCommand = new SqlCommand();
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(cn.ConnectionString);
    ConnectionInfo crConnectionInfo = new ConnectionInfo();
    TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();

    string qryPreviewDocument = " SELECT Company.companyname, Company.address, Company.city, Company.state, Company.phono, Company.gtin as CompanyGTinNo, Company.ctin as CompanyCTinNo, Company.gtindate as CompanyGTinDate, " + System.Environment.NewLine;
    qryPreviewDocument += " Client.clientname, Client.contactname, Client.billingaddrtess, Client.GTin as ClientGTinNo, Client.CTin as ClientCTinNo, Client.gtindate as ClientGTinDate, Client.ctindate as ClientCTinDate, " + System.Environment.NewLine;
    qryPreviewDocument += " TaxInvoice.invoiceno, TaxInvoice.pono, TaxInvoice.pono2, TaxInvoice.pono3, TaxInvoice.pono4, " + System.Environment.NewLine;
    qryPreviewDocument += " TaxInvoice.issuedate as IssueDate, " + System.Environment.NewLine;
    qryPreviewDocument += " TaxInvoice.discount as Discount, TaxInvoice.shipping as Shipping, TaxInvoice.tax as Tax, TaxInvoice.vat as Vat, " + System.Environment.NewLine;
    qryPreviewDocument += " TaxInvoice.sese as Sese, TaxInvoice.paymenttype as PaymentType, TaxInvoice.chequeno as Chequeno, TaxInvoice.totalamt as TotalAmt, " + System.Environment.NewLine;
    qryPreviewDocument += " TaxInvoice.description as Description, TaxInvoice.paymentpaid as PaymentPaid, TaxInvoice.subtotal as Subtotal, " + System.Environment.NewLine;

    qryPreviewDocument += " Product.productname as ProductName, TaxInvoiceProductDetail.uom as Uom, " + System.Environment.NewLine;
    qryPreviewDocument += " TaxInvoiceProductDetail.quantity as Quantity, TaxInvoiceProductDetail.price as Price, " + System.Environment.NewLine;
    qryPreviewDocument += " TaxInvoiceProductDetail.challanno as ChallanNo, TaxInvoiceProductDetail.issuedate as ChallanDate " + System.Environment.NewLine;

    qryPreviewDocument += " into " + mtmptbl + " " + System.Environment.NewLine;

    qryPreviewDocument += " from tbl_TaxInvoice TaxInvoice " + System.Environment.NewLine;
    qryPreviewDocument += " LEFT OUTER JOIN tbl_TaxInvoiceProductDetail TaxInvoiceProductDetail ON TaxInvoice.invoiceno = TaxInvoiceProductDetail.invoiceno " + System.Environment.NewLine;
    qryPreviewDocument += " LEFT OUTER JOIN tbl_clientdetail Client ON TaxInvoice.clientid = Client.clientid " + System.Environment.NewLine;
    qryPreviewDocument += " LEFT OUTER JOIN tbl_companydetail Company ON TaxInvoice.BranchID = Company.companyid " + System.Environment.NewLine;
    qryPreviewDocument += " LEFT OUTER JOIN tbl_product Product ON TaxInvoiceProductDetail.productid = Product.productid " + System.Environment.NewLine;

    qryPreviewDocument += " where TaxInvoice.BranchID = " + lbl_branchid.Text + " " + System.Environment.NewLine;
    qryPreviewDocument += " and TaxInvoice.YearID = " + lbl_yearid.Text + " " + System.Environment.NewLine;
    qryPreviewDocument += " and TaxInvoice.invoiceno = " + txt_invoice.Text + "";

    qryPreviewDocument += " and TaxInvoiceProductDetail.BranchID = " + lbl_branchid.Text + " " + System.Environment.NewLine;
    qryPreviewDocument += " and TaxInvoiceProductDetail.YearID = " + lbl_yearid.Text + " " + System.Environment.NewLine;
    qryPreviewDocument += " and TaxInvoiceProductDetail.invoiceno = " + txt_invoice.Text + "";

    string SQL = "select upper(name) as TABLE_NAME FROM sysobjects WHERE type = 'U' and name = '" + mtmptbl + "' order by name";
    DataTable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter(SQL, cn);
    da.Fill(dt);
    if (dt.Rows.Count > 0)
    {
        string qrydrop = "drop table " + mtmptbl + "";
        SqlCommand cmd = new SqlCommand(qrydrop, cn);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }

    MyCommand = new SqlCommand(qryPreviewDocument, cn);
    MyCommand.CommandType = CommandType.Text;
    cn.Open();
    MyCommand.ExecuteNonQuery();

    //Add BillType field to the tmptable
    using (MyCommand = new SqlCommand("alter table [" + mtmptbl + "] add [billtype] varchar(20) NULL", cn))
    {
        MyCommand.CommandType = CommandType.Text;
        MyCommand.ExecuteNonQuery();
    }
    cn.Close();

    string crReportPath = Application.StartupPath.Replace("bin\\Debug", "") + "\\Print";

    cryRpt.Load(crReportPath + "\\RptTaxInvoicePrint.rpt");

    builder.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["con"];
    string dbName = builder.InitialCatalog;
    string dbDataSource = builder.DataSource;
    string userID = builder.UserID;
    string pass = builder.Password;

    crConnectionInfo.ServerName = dbDataSource;
    crConnectionInfo.DatabaseName = dbName;
    crConnectionInfo.UserID = userID;
    crConnectionInfo.Password = pass;

    Tables Crtables;
    Crtables = cryRpt.Database.Tables;

    foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in Crtables)
    {
        crtableLogoninfo = CrTable.LogOnInfo;
        crtableLogoninfo.ConnectionInfo = crConnectionInfo;
        CrTable.ApplyLogOnInfo(crtableLogoninfo);
    }

    frm.crystalReportViewer1.ReportSource = cryRpt;
    frm.crystalReportViewer1.RefreshReport();

    Cursor = Cursors.Arrow;
    frm.Show();
    btn_reset.Focus();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Thanks In Advance

Answer 1

This can be done by add the groupheadersection to the invoice no.