Labels Creation using Crystal Report in Windows Application using C#.NET 2.0

Introduction

Hi friends. I have came up with this code for label creation using Crystal Report. First, I was advised to use a text report to complete this task. But the drawback of a text report is that I can only take one label per sample. But now I have come up with a report in which I can make many labels for a single sample and go on. I have done the coding in Visual Studio .NET Windows application and the back end used is Oracle 10g.

Using the Code

Before getting into the coding, you have to create tables (Oracle or My SQL) for the specification for which we are going to create the labels. In Visual Studio, create 14 datatables in the dataset. Create a form to select the label details using any codes you prefer as you see in the picture above. In this screen, you can take labels in one stretch or even one by one. By checking that check box, you can take it in one stretch or you can take it one by one. The coding which you will see below only belongs to the insert button. The "checkclr" is used to clear the checks made in this screen and the "clear" button is used to clear the dataset. I have created this code using the connection class. You open a connection to Oracle or SQL on your own and try this.

Open a Crystal report and place all those 14 datatables in the order in which you want to take a printout. The names Label1 that you find in the if loops are the names of the datatable in the physical dataset.

"n" in the code represents the number of labels required for the particular sample.

if (checkBox5.Checked)
{
    string lblstr = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                    LABREPORT B WHERE A.DRUGCODE = B.DRUGCODE AND B.SAMPLECODE BETWEEN '"
                    + cmbsamplecd.Text + "' AND '" + cmbsampleto.Text
                    + "' ORDER BY SAMPLECODE";
    DataTable lbldt = Conn.ExecuteRead(lblstr);

    rptDataSet ds = new rptDataSet();
    if (radioButton20.Checked)
    {
        int n, m;//n = count of the label, m = count of samplecode (Harry Christopher)
        m = lbldt.Rows.Count;
        for (int k = 0; k <= m - 1; k++)
        {
            n = Convert.ToInt32(lbldt.Rows[k]["count"].ToString());
            if (j != 0)
            {
                n = n + j;
                try
                {
                    if (n < 18)
                    {
                        for (i = j; i <= n - 1; i++)
                        {
                            if (i == 1 && radioButton1.Checked != true
                                && ds.Label1.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE = 
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label1", query);
                                radioButton1.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;//This is used to stop the loop once all
                                              //the labels have been created.
                                              //This will lead to an exception, 
                                              //do not care,
                                              //still it works fine. (Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 2 && radioButton2.Checked != true && 
                                ds.Label2.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '" +
                                    lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label2", query);
                                radioButton2.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show
                                        ("Labels", new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop once
                                                //all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 3 && radioButton3.Checked != true && 
                                ds.Label3.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE = B.DRUGCODE
                                    AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label3", query);
                                radioButton3.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop once
                                                //all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 4 && radioButton4.Checked != true && 
                                ds.Label4.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE = B.DRUGCODE
                                    AND B.SAMPLECODE = '" +
                                    lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label4", query);
                                radioButton4.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop once
                                                //all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 5 && radioButton5.Checked != true && 
                                ds.Label5.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE = B.DRUGCODE
                                    AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label5", query);
                                radioButton5.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop
                                                //once all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 6 && radioButton6.Checked != true && 
                                ds.Label6.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE = B.DRUGCODE
                                    AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label6", query);
                                radioButton6.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop
                                                //once all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 7 && radioButton7.Checked != true && 
                                ds.Label7.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label7", query);
                                radioButton7.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop
                                                //once all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 8 && radioButton8.Checked != true && 
                                ds.Label8.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                    rptOpen.SetData("label8", query);
                                radioButton8.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop
                                                //once all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 9 && radioButton9.Checked != true && 
                                ds.Label9.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label9", query);
                                radioButton9.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop
                                                //once all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 10 && radioButton10.Checked != true && 
                                ds.Label10.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label10", query);
                                radioButton10.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop
                                                //once all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 11 && radioButton11.Checked != true && 
                                ds.Label11.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label11", query);
                                radioButton11.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop
                                                //once all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 12 && radioButton12.Checked != true && 
                                ds.Label12.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label12", query);
                                radioButton12.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;  //This is used to stop the loop
                                                //once all the labels have been created.
                                                //This will lead to an exception, 
                                                //do not care,
                                                //still it works fine.(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 13 && radioButton13.Checked != true && 
                                ds.Label13.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label13", query);
                                radioButton13.Checked = true;
                                j = i + 1;
                                if (k == m - 1 && i == n - 1)
                                {
                                    rptOpen.Show("Labels", 
                                        new Forms.QC.Reports.QCLabels16());
                                    k = m + 1;    //This is used to stop the loop
                                                  //once all the labels have been created
                                                  //This will lead to an exception, 
                                                  //do not care,
                                                  //still it works fine. 
                                                  //(Harry Christopher)
                                    i = n + 1;
                                }
                            }
                            if (i == 14 && radioButton14.Checked != true && 
                                ds.Label14.Rows.Count == 0)
                            {
                                string query = "SELECT A.*, 
                                    B.SAMPLECODE FROM QCDRUGSPEC A,
                                    LABREPORT B WHERE A.DRUGCODE =
                                    B.DRUGCODE AND B.SAMPLECODE = '"
                                    + lbldt.Rows[k]["samplecode"].ToString() + "'";
                                rptOpen.SetData("label14", query);
                                radioButton14.Checked = true;
                                int v = n - i;    //This will help in not 
                                                  //breaking the loop
                                                  //while new page opens.
                                                  //This will make the loop to continue. 
                                                  //(Harry Christopher)
                                n = v;
                                i = 0;
                                j = i + 1;
                                rptOpen.Show("Labels", 
                                    new Forms.QC.Reports.QCLabels16());
                                //This query is to just fill the dataset 
                                //with null values,
                                //nothing special with this query.(Harry Christopher)
                                string query1 = 
                                "select address3 from labmaster where labcode = 'ABCD'";
                                rptOpen.SetData("label1", query1);
                                rptOpen.SetData("label2", query1);
                                rptOpen.SetData("label3", query1);
                                rptOpen.SetData("label4", query1);
                                rptOpen.SetData("label5", query1);
                                rptOpen.SetData("label6", query1);
                                rptOpen.SetData("label7", query1);
                                rptOpen.SetData("label8", query1);
                                rptOpen.SetData("label9", query1);
                                rptOpen.SetData("label10", query1);
                                rptOpen.SetData("label11", query1);
                                rptOpen.SetData("label12", query1);
                                rptOpen.SetData("label13", query1);
                                rptOpen.SetData("label14", query1);
                                rptOpen.SetData("label15", query1);
                                rptOpen.SetData("label16", query1);
                            }
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("Labels are Ready!!! Go ahead!!!");
                }
            }
        }
    }
}
if (!checkBox5.Checked)
{
    rptDataSet ds = new rptDataSet();
    if (radioButton20.Checked)
    {
        int n;
        n = Convert.ToInt32(txtno.Text);

        if (j != 0)
        {
            n = n + j;
            try
            {
                if (n < 18)
                {
                    for (i = j; i <= n - 1; i++)
                    {
                        if (i == 1 && radioButton1.Checked != true && 
                            ds.Label1.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label1", query);
                            radioButton1.Checked = true;
                            j = i + 1;
                        }
                        if (i == 2 && radioButton2.Checked != true && 
                            ds.Label2.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label2", query);
                            radioButton2.Checked = true;
                            j = i + 1;
                        }
                        if (i == 3 && radioButton3.Checked != true && 
                            ds.Label3.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                                rptOpen.SetData("label3", query);
                            radioButton3.Checked = true;
                            j = i + 1;
                        }
                        if (i == 4 && radioButton4.Checked != true && 
                            ds.Label4.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label4", query);
                            radioButton4.Checked = true;
                            j = i + 1;
                        }
                        if (i == 5 && radioButton5.Checked != true && 
                            ds.Label5.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label5", query);
                            radioButton5.Checked = true;
                            j = i + 1;
                        }
                        if (i == 6 && radioButton6.Checked != true && 
                            ds.Label6.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                                rptOpen.SetData("label6", query);
                            radioButton6.Checked = true;
                            j = i + 1;
                        }
                        if (i == 7 && radioButton7.Checked != true && 
                            ds.Label7.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                                rptOpen.SetData("label7", query);
                            radioButton7.Checked = true;
                            j = i + 1;
                        }
                        if (i == 8 && radioButton8.Checked != true && 
                            ds.Label8.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label8", query);
                            radioButton8.Checked = true;
                            j = i + 1;
                        }
                        if (i == 9 && radioButton9.Checked != true && 
                            ds.Label9.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label9", query);
                            radioButton9.Checked = true;
                            j = i + 1;
                        }
                        if (i == 10 && radioButton10.Checked != true && 
                            ds.Label10.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label10", query);
                            radioButton10.Checked = true;
                            j = i + 1;
                        }
                        if (i == 11 && radioButton11.Checked != true && 
                            ds.Label11.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label11", query);
                            radioButton11.Checked = true;
                            j = i + 1;
                        }
                        if (i == 12 && radioButton12.Checked != true && 
                            ds.Label12.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label12", query);
                            radioButton12.Checked = true;
                            j = i + 1;
                        }
                        if (i == 13 && radioButton13.Checked != true && 
                            ds.Label13.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label13", query);
                            radioButton13.Checked = true;
                            j = i + 1;
                        }
                        if (i == 14 && radioButton14.Checked != true && 
                            ds.Label14.Rows.Count == 0)
                        {
                            string query = "SELECT A.*, B.SAMPLECODE FROM QCDRUGSPEC A,
                                LABREPORT B WHERE A.DRUGCODE =
                                B.DRUGCODE AND B.SAMPLECODE = '"
                                + cmbsamplecd.Text + "'";
                            rptOpen.SetData("label14", query);
                            radioButton14.Checked = true;
                            j = i + 1;
                        }
                    }
                }
            }
            catch
            {
                MessageBox.Show("You are running out of the count, 
                    Let go with next report.");
            }
        }
    }
}

Points of Interest

I didn't learn anything, but I went mad for one month while creating this code, since my seniors were not aware of this and they made fun of me. I wrote this thing only to show that I'm not to be criticised. Hope you could understand the above description. Please give your feedback so that I can tune this for the best performance.

History

• 17^th July, 2008: Initial version