C# print goes infinitely while printing database value on multiple pages

Question

0.00/5 (No votes)

See more:

I want to print database value on multiple pages (30 values on one page). By using while(dr.Read()) loops goes infinitely and the same data is displaying on each page, How to solve it?

What I have tried:

private void test2_Load(object sender, EventArgs e)
        {

           itemperpage = 0;
            printPreviewDialog1.Document = printDocument1;

            ((ToolStripButton)((ToolStrip)printPreviewDialog1.Controls[1]).Items[0]).Enabled
            = false;//disable the direct print from printpreview.as when we click that Print button PrintPage event fires again.


            printDocument1.DefaultPageSettings.PaperSize = paperSize;
            printPreviewDialog1.ShowDialog();
        }

private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
        {
            int kx = 25;
            int ky = 50;
            con.Close();
            con.Open();
            String sql = "SELECT * from cash_bill";
            cmd = new OleDbCommand(sql, con);
            dr1 = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            
               while (dr1.Read()) 
                {
                    e.Graphics.DrawString(dr1[7].ToString(), printFont, Brushes.Black, kx + 75, ky + 5);
                   
           
                    if (itemperpage < 29) 
                    {
                        itemperpage += 1; 
                        e.HasMorePages = false; 
                        ky = ky + 50;
                    }

                    else 
                    {
                        itemperpage = 0; 
                        e.HasMorePages = true; 
                         return;

                    }
                   
                }
                con.Close();
            }

Posted 26-Nov-19 20:21pm

Prachi92

Updated 26-Nov-19 20:34pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-11-26T20:35:00

Well ... you read the same data from the DB each time that method is executed - so as long as the DB contains 30 or more items, you will print the first page, tell the system there are more pages to print, and exit. The system will then call you again to print the next page, you will read the same data from the DB, and the cycle goes round again.

Your method needs to be aware of which page it is printing, and either:
1) "Skip" data from previous pages by ignoring lines you have printed already,
Or
2) (Better) only fetching the data for one page at a time.

You can do this in many ways, but if your DB table has an integer ID value (an IDENTITY perhaps), then you can store the last printed ID and then change your query to

SQL

SELECT TOP 30 * FROM cash_bill WHERE ID > @LastID ORDER BY ID ASC

And supplying the last ID value you printed as the parameter. By setting the last ID value to int.MinValue before you print the last page the first page will automatically be printed.

Additionally, you need to Close and Dispose your Connection, Command, and Reader objects properly - dump the CommandBehavior.CloseConnection stuff and create your items inside a using block:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT Age, Description FROM myTable WHERE ID = @ID", con))
        {
        cmd.Parameters.AddWithValue("@ID", myTextBox.Text);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                int age = (int) reader["Age"];
                string desc = (string) reader["Description"];
                Console.WriteLine($"{age}\n{desc}");
                }
            }
        }
    }

Then the system will Close and Dispose for you immediately they go out of scope.