Print Multiple records on a reportveiwer all together

[Tajaldeen]

I have a reportviewer that shows a certificate form for 30 students , this report retrieves the data from access db through an ID numbers on a combobox.
Everytime I choose the ID from the combobox the data for the specific student appears in the report , then I press print , after that I chose the second ID and print it , one by one manually to the last record.
Can you help me to make a button that would print the data for the 30 students all together.

[schoemr]

Tajaldeen,

Yes, I think I can help, however you are going to have to give more information.

1) I assume you are using Visual Studio?
2) Do you have the RDLC designer installed? And in your solution explorer can you see the .rdlc file?
3) In your current form (the form that have the reportviewer on it) can you double click on it and paste all the code of the form here?


Let's take it from there.

[Tajaldeen]

Tajaldeen,


Yes, I think I can help, however you are going to have to give more information.

1) I assume you are using Visual Studio?
2) Do you have the RDLC designer installed? And in your solution explorer can you see the .rdlc file?
3) In your current form (the form that have the reportviewer on it) can you double click on it and paste all the code of the form here?


Let's take it from there.

1) Correct, i'm using Visual Studio.
2) Yes, the RDLC file is visible on solution explorer.
3) it's very long code contains too many functions, anyway ... here it is:

Imports System.Data.OleDb
Imports Microsoft.Reporting.WinForms

Public Class Form9


    Public strcon As OleDbConnection = strconnection()
    Public result As String
    Public cmd As New OleDbCommand
    Public da As New OleDbDataAdapter
    Public dt As New DataTable



    Private Sub Form9_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        fillcombo()   ' تحميل ارقام السجل في الكمبوبوكس عند تشغيل الفورم
        sa()         ' دالة عرض البيانات من القاعدة في مربعات النص
        sa_Rank()   ' عرض البيانات الخاصة بدالة كويري الترتيب


        If ComboBox1.Text = "" Then
            MsgBox("عذرا .. لا توجد بيانات لعرضها", MsgBoxStyle.Critical, "تنبيه")
            Me.Close()
        
        End If




        Dim a As Integer
        Dim b As Integer
        Dim c As Integer
        Dim d As Integer
        Dim h As Integer
        Dim f As Integer

        a = Convert.ToInt32(TextBox3.Text)
        b = Convert.ToInt32(TextBox5.Text)
        c = Convert.ToInt32(TextBox7.Text)
        d = Convert.ToInt32(TextBox9.Text)
        h = Convert.ToInt32(TextBox11.Text)

        f = a + b + c + d + h
        TextBox2.Text = CStr(f)




        Try
            ReportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout)  ' تغيير الى عرض الطباعة مباشرة في التقرير
            ReportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent
            ReportViewer1.ZoomPercent = 120    ' تحديد نسبة عرض التقرير في الشاشة


            'TODO: This line of code loads data into the 'DataSet1.DataTable1' table. You can move, or remove it, as needed.
            Me.DataTable1TableAdapter.Fill(Me.DataSet1.DataTable1)
            Me.ReportViewer1.RefreshReport()



        Catch ex As Exception
            MsgBox("لعرض هذه الشهادة يرجى داخال بيانات الطلاب أولا", MsgBoxStyle.Information, "تنبيه")
        End Try


    End Sub

    Sub fillcombo()  ' comboBox دالة عرض ارقام السجل في  
        '===========================================================================================================
        Dim con1 As New OleDbConnection
        con1.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\testdb.accdb;Persist Security Info=True")
        If con1.State = ConnectionState.Closed Then
            con1.Open()
        End If

        Dim strsql1 As String
        strsql1 = " select ID from tbl_accounts order by Name"
        Dim cmd1 As New OleDbCommand
        cmd1.CommandText = strsql1
        cmd1.Connection = con1
        Dim reader1 As OleDbDataReader
        reader1 = cmd1.ExecuteReader
        While (reader1.Read())
            ComboBox1.Items.Add(reader1("ID"))
        End While
        cmd1.Dispose()
        reader1.Close()

        ComboBox1.SelectedIndex = 0    ' كود ملء اول خانة في الكمبوبوكس عند تحميل الفورم

    End Sub


    '============================================================================================================================================
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged  ' زر الكمبوبوكس


        Me.DataTable1TableAdapter.Search_ID(Me.DataSet1.DataTable1, ComboBox1.Text)   ' عرض سجل الطالب بمجرد تغيير قيمة الكمبوبوكس
        Me.ReportViewer1.RefreshReport()


        sa() ' دالة عرض البيانات في مربعات النص
        sa_Rank()   ' دالة عرض بيانات الكويري الخاص بالترتيب


        Dim ali As New ReportParameter("Para1", TextBox1.Text)    ' ارسال مربع نص التفقيط الى الباراميتر الخاص به في التقرير
        Dim ali1 As New ReportParameter("Para2", TextBox4.Text)
        Dim ali2 As New ReportParameter("Para3", TextBox6.Text)
        Dim ali3 As New ReportParameter("Para4", TextBox8.Text)
        Dim ali4 As New ReportParameter("Para5", TextBox10.Text)
        Dim ali5 As New ReportParameter("Para6", TextBox12.Text)
        Dim ali6 As New ReportParameter("Para7", TextBox14.Text)
        Dim ali7 As New ReportParameter("Para8", TextBox16.Text)
        Dim ali8 As New ReportParameter("Para9", TextBox18.Text)
        Dim ali9 As New ReportParameter("Para10", TextBox20.Text)
        Dim ali10 As New ReportParameter("Para11", TextBox22.Text)
        Dim ali11 As New ReportParameter("Para12", TextBox24.Text)
        Dim ali12 As New ReportParameter("Para13", TextBox26.Text)
        Dim ali13 As New ReportParameter("Para14", TextBox28.Text)
        Dim ali14 As New ReportParameter("Para15", TextBox30.Text)
        Dim ali15 As New ReportParameter("Para16", TextBox32.Text)
        Dim ali16 As New ReportParameter("Para17", TextBox34.Text)

        Me.DataTable1TableAdapter.Search_ID(Me.DataSet1.DataTable1, ComboBox1.Text)   ' عرض سجل الطالب بمجرد تغيير قيمة مع ارسال التفقيط الى الشهادة


        ReportViewer1.LocalReport.SetParameters(ali)    ' عرض الباراميتر في التقرير
        ReportViewer1.LocalReport.SetParameters(ali1)
        ReportViewer1.LocalReport.SetParameters(ali2)
        ReportViewer1.LocalReport.SetParameters(ali3)
        ReportViewer1.LocalReport.SetParameters(ali4)
        ReportViewer1.LocalReport.SetParameters(ali5)
        ReportViewer1.LocalReport.SetParameters(ali6)
        ReportViewer1.LocalReport.SetParameters(ali7)
        ReportViewer1.LocalReport.SetParameters(ali8)
        ReportViewer1.LocalReport.SetParameters(ali9)
        ReportViewer1.LocalReport.SetParameters(ali10)
        ReportViewer1.LocalReport.SetParameters(ali11)
        ReportViewer1.LocalReport.SetParameters(ali12)
        ReportViewer1.LocalReport.SetParameters(ali13)
        ReportViewer1.LocalReport.SetParameters(ali14)
        ReportViewer1.LocalReport.SetParameters(ali15)
        ReportViewer1.LocalReport.SetParameters(ali16)


        Me.ReportViewer1.RefreshReport()


    End Sub
    '============================================================================================================================================



    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Close()

    End Sub



    Public Sub sa_Rank()    ' دالة عرض البيانات الخاصة بكويري الترتيب

        Try
            Dim sql As String
            Dim cmd As New OleDbCommand
            Dim dt As New DataTable
            Dim da As New OleDbDataAdapter
            strcon.Open()

            sql = " select * from One_Term_Query where ID like '%" & ComboBox1.Text & "%'"
            cmd.Connection = strcon
            cmd.CommandText = sql
            da.SelectCommand = cmd

            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                TextBox33.Text = dt.Rows(0).Item("Rank").ToString



            End If

        Catch ex As Exception
        Finally
            strcon.Close()

        End Try


    End Sub


    Public Sub sa()    ' دالة عرض البيانات في التكست بوك تمهيدا لتفقيطها وارسالها الى الشهادة

        Try
            Dim sql As String
            Dim cmd As New OleDbCommand
            Dim dt As New DataTable
            Dim da As New OleDbDataAdapter
            strcon.Open()

            sql = " select * from tbl_accounts where ID like '%" & ComboBox1.Text & "%'"
            cmd.Connection = strcon
            cmd.CommandText = sql
            da.SelectCommand = cmd

            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                TextBox2.Text = dt.Rows(0).Item("Total_Term").ToString
                TextBox3.Text = dt.Rows(0).Item("Sc_Term_Total").ToString
                TextBox5.Text = dt.Rows(0).Item("M_Term_Total").ToString
                TextBox7.Text = dt.Rows(0).Item("A_Term_Total").ToString
                TextBox9.Text = dt.Rows(0).Item("S_Term_Total").ToString
                TextBox11.Text = dt.Rows(0).Item("Q_Term_Total").ToString
                TextBox13.Text = dt.Rows(0).Item("Q_Term").ToString
                TextBox15.Text = dt.Rows(0).Item("S_Term").ToString
                TextBox17.Text = dt.Rows(0).Item("A_Term").ToString
                TextBox19.Text = dt.Rows(0).Item("M_Term").ToString
                TextBox21.Text = dt.Rows(0).Item("Sc_Term").ToString
                TextBox23.Text = dt.Rows(0).Item("Q_Calculate").ToString
                TextBox25.Text = dt.Rows(0).Item("S_Calculate").ToString
                TextBox27.Text = dt.Rows(0).Item("A_Calculate").ToString
                TextBox29.Text = dt.Rows(0).Item("M_Calculate").ToString
                TextBox31.Text = dt.Rows(0).Item("Sc_Calculation").ToString



                TextBox2.Text = Format(Val(TextBox2.Text), "0")     ' تقريب مربعات النص بدون فاصلة عشرية
                TextBox3.Text = Format(Val(TextBox3.Text), "0")
                TextBox5.Text = Format(Val(TextBox5.Text), "0")
                TextBox7.Text = Format(Val(TextBox7.Text), "0")
                TextBox9.Text = Format(Val(TextBox9.Text), "0")
                TextBox11.Text = Format(Val(TextBox11.Text), "0")
                TextBox13.Text = Format(Val(TextBox13.Text), "0")
                TextBox15.Text = Format(Val(TextBox15.Text), "0")
                TextBox17.Text = Format(Val(TextBox17.Text), "0")
                TextBox19.Text = Format(Val(TextBox19.Text), "0")
                TextBox21.Text = Format(Val(TextBox21.Text), "0")
                TextBox23.Text = Format(Val(TextBox23.Text), "0")
                TextBox25.Text = Format(Val(TextBox25.Text), "0")
                TextBox27.Text = Format(Val(TextBox27.Text), "0")
                TextBox29.Text = Format(Val(TextBox29.Text), "0")
                TextBox31.Text = Format(Val(TextBox31.Text), "0")



            End If

        Catch ex As Exception
        Finally
            strcon.Close()

        End Try


    End Sub


    '===================================================================== تفقيط مربعات النص ================================================================
    Private Sub TextBox2_TextChanged(sender As Object, e As EventArgs) Handles TextBox2.TextChanged    ' تفقيط اجمالي الفصل
        If TextBox2.Text = "" Then
            Exit Sub
        Else
            TextBox1.Text = Salem(TextBox2.Text, "", "")
        End If

    End Sub

    Private Sub TextBox3_TextChanged(sender As Object, e As EventArgs) Handles TextBox3.TextChanged     ' تفقيط اجمالي العلوم
        If TextBox3.Text = "" Then
            Exit Sub
        Else
            TextBox4.Text = Salem(TextBox3.Text, "", "")
        End If
    End Sub

    Private Sub TextBox5_TextChanged(sender As Object, e As EventArgs) Handles TextBox5.TextChanged     ' تفقيط اجمالي الرياضيات
        If TextBox5.Text = "" Then
            Exit Sub
        Else
            TextBox6.Text = Salem(TextBox5.Text, "", "")
        End If
    End Sub

    Private Sub TextBox7_TextChanged(sender As Object, e As EventArgs) Handles TextBox7.TextChanged     ' تفقيط اجمالي اللغة العربية
        If TextBox7.Text = "" Then
            Exit Sub
        Else
            TextBox8.Text = Salem(TextBox7.Text, "", "")
        End If
    End Sub


    Private Sub TextBox9_TextChanged(sender As Object, e As EventArgs) Handles TextBox9.TextChanged     ' تفقيط اجمالي الاسلامية
        If TextBox7.Text = "" Then
            Exit Sub
        Else
            TextBox10.Text = Salem(TextBox9.Text, "", "")
        End If
    End Sub

    Private Sub TextBox11_TextChanged(sender As Object, e As EventArgs) Handles TextBox11.TextChanged     ' تفقيط اجمالي الاسلامية
        If TextBox11.Text = "" Then
            Exit Sub
        Else
            TextBox12.Text = Salem(TextBox11.Text, "", "")
        End If
    End Sub

    Private Sub TextBox13_TextChanged(sender As Object, e As EventArgs) Handles TextBox13.TextChanged     ' تفقيط نهائي الفصل قرآن
        If TextBox13.Text = "" Then
            Exit Sub
        Else
            TextBox14.Text = Salem(TextBox13.Text, "", "")
        End If
    End Sub

    Private Sub TextBox15_TextChanged(sender As Object, e As EventArgs) Handles TextBox15.TextChanged     ' تفقيط نهائي الفصل اسلامية
        If TextBox15.Text = "" Then
            Exit Sub
        Else
            TextBox16.Text = Salem(TextBox15.Text, "", "")
        End If
    End Sub

    Private Sub TextBox17_TextChanged(sender As Object, e As EventArgs) Handles TextBox17.TextChanged     ' تفقيط نهائي الفصل عربي
        If TextBox17.Text = "" Then
            Exit Sub
        Else
            TextBox18.Text = Salem(TextBox17.Text, "", "")
        End If
    End Sub

    Private Sub TextBox19_TextChanged(sender As Object, e As EventArgs) Handles TextBox19.TextChanged     ' تفقيط نهائي الفصل رياضيات
        If TextBox19.Text = "" Then
            Exit Sub
        Else
            TextBox20.Text = Salem(TextBox19.Text, "", "")
        End If
    End Sub

    Private Sub TextBox21_TextChanged(sender As Object, e As EventArgs) Handles TextBox21.TextChanged     ' تفقيط نهائي الفصل رياضيات
        If TextBox21.Text = "" Then
            Exit Sub
        Else
            TextBox22.Text = Salem(TextBox21.Text, "", "")
        End If
    End Sub

    Private Sub TextBox23_TextChanged(sender As Object, e As EventArgs) Handles TextBox23.TextChanged     ' تفقيط محصلة قرآن
        If TextBox23.Text = "" Then
            Exit Sub
        Else
            TextBox24.Text = Salem(TextBox23.Text, "", "")
        End If
    End Sub

    Private Sub TextBox25_TextChanged(sender As Object, e As EventArgs) Handles TextBox25.TextChanged     ' تفقيط محصلة اسلامية
        If TextBox25.Text = "" Then
            Exit Sub
        Else
            TextBox26.Text = Salem(TextBox25.Text, "", "")
        End If
    End Sub

    Private Sub TextBox27_TextChanged(sender As Object, e As EventArgs) Handles TextBox27.TextChanged     ' تفقيط محصلة عربي
        If TextBox27.Text = "" Then
            Exit Sub
        Else
            TextBox28.Text = Salem(TextBox27.Text, "", "")
        End If
    End Sub

    Private Sub TextBox29_TextChanged(sender As Object, e As EventArgs) Handles TextBox29.TextChanged     ' تفقيط محصلة رياضيات
        If TextBox29.Text = "" Then
            Exit Sub
        Else
            TextBox30.Text = Salem(TextBox29.Text, "", "")
        End If
    End Sub



    Private Sub TextBox31_TextChanged(sender As Object, e As EventArgs) Handles TextBox31.TextChanged     ' تفقيط محصلة رياضيات
        If TextBox31.Text = "" Then
            Exit Sub
        Else
            TextBox32.Text = Salem(TextBox31.Text, "", "")
        End If
    End Sub


    Private Sub TextBox33_TextChanged(sender As Object, e As EventArgs) Handles TextBox33.TextChanged     ' تفقيط محصلة رياضيات
        If TextBox33.Text = "" Then
            Exit Sub
        Else
            TextBox34.Text = Salem_Rank(TextBox33.Text, "", "")
        End If
    End Sub

  

   

  
End Class

[schoemr]

Hi,

So yesterday I waited hours and hours for your reply and the last person to finally show up was Taljadeen. I don't have much time now my mom wants to leave this morning and then I will only have internet on my phone. So if needed this will then have to carry on next week.

There are several ways you could do this, and which method is the best I think will depend on your preference. Some of these guys here can even suggest better ideas I am sure. The simple method and perhaps a bit crude is to iterate through the combo and print each record. Something like this: (I am not able to test it now)

 Dim printDialog As New PrintDialog()
 printDialog.Document = New PrintDocument()
       If printDialog.ShowDialog() = DialogResult.OK Then
        For Each item In ComboBox1.Items
            currentItem = item
            printDialog.Document.Print()
        Next
    End If

The second method which is perhaps more efficient (maybe ask someone like jmc but beware sometimes he got pms) is to iterate though the datatable and print:

Private Sub PrintButton_Click(sender As Object, e As EventArgs) Handles PrintButton.Click
    Me.DataTable1TableAdapter.Fill(Me.DataSet1.DataTable1)

    For Each row As DataRow In Me.DataSet1.DataTable1.Rows
        Me.DataTable1BindingSource.DataSource = row
        Me.ReportViewer1.RefreshReport()
    Next

    'Print all pages of the report to the default printer
    Me.ReportViewer1.PrintToPrinter(1, False, 0, 0)
End Sub

these arguments that you see "(1, False, 0, 0)" mean the follwing:

the first argument - "1" >> number of copies
2nd argument "False" >> it whether to colate or not. "False" means say you have 30 students and each student report have 5 pages then it will print all the page 1's first, then print all the page 2's, then all the page 3's etc. If is "False it will print each student by student..
The last two arguments or values is "first page" to print and "last page" to print.

But let me tell you how I would have done it.........

I would not have bothered to do this in the reportviewer like you asked. I would have use a filesteam to export the records to a pdf viewer and print it (or save it) from there. This is what I am actually doing in my project:

Private Sub PrintButton_Click(sender As Object, e As EventArgs) Handles PrintButton.Click
    Me.DataTable1TableAdapter.Fill(Me.DataSet1.DataTable1)

    For Each row As DataRow In Me.DataSet1.DataTable1.Rows
        Me.DataTable1BindingSource.DataSource = row
        Me.ReportViewer1.RefreshReport()
    Next

    'Export the report to a PDF file
    Dim pdfBytes As Byte() = ReportViewer1.LocalReport.Render("PDF")
    Dim pdfFileName As String = "Report.pdf"
    File.WriteAllBytes(pdfFileName, pdfBytes)
    'Open the PDF file
    Process.Start(pdfFileName)
End Sub

[schoemr]

Also, did you come right with your other question? It is possible to pass your returned value from your function to a textbox in rdlc. I will explain that on Monday, k?

I have to go now..

bye

[schoemr]

oh one thing i forgot if you want to use export to pdf make sure you have the correct namespaces on top of your form

imports system.IO
imports system.diagnostics
imports microsoft.reporting.winforms

[Tajaldeen]

Hi,

So yesterday I waited hours and hours for your reply and the last person to finally show up was Taljadeen.

I'm sorry for that, I was sleeping due to time zone difference perhaps
Anyway, thank you very much for your willing to help, I really appreciate that, I'm not in a hurry, it's not a big deal, I just want to lay down enjoying a hot cup of coffee while my printer do its job instead of pick them up record by record, so I can wait till next week, just take care of your Mom, she is all the matter now.
Have a nice day

[Tajaldeen]

oh one thing i forgot if you want to use export to pdf

Export to PDF method only show the last record in PDF file !!!
another problem is : when there is a Null field it returns an error message, when i fill that filed it works but only show last record.

[Tajaldeen]

Also, did you come right with your other question? It is possible to pass your returned value from your function to a textbox in rdlc. I will explain that on Monday, k?

I have to go now..

bye

I used indirect way to solve that problem, first i retrived all data from db to textboxs in my Form then i applied the NoToText function on them directly from a module that i've created, finally i transffered them back to my report using parameters, it works perfectly.

[schoemr]

Oh okay I see what the problem is... the loop goes through all the records in datatable1 but each time it goes through the loop it overwrite the data source for the report.. That's why at the end only the last record is displayed in the PDF.

So we'll create a list to hold all the records and each loop will add the record to that list and when it's done we'll tell the report to use the list as the data source..

It's not a big deal deal but if no one else jumps in I'll let do it for you on Monday

[schoemr]

Hi Tajaldeen,

So I played around with this. Can you test it and let me know...?

Private Sub PrintButton_Click(sender As Object, e As EventArgs) Handles PrintButton.Click
    Me.DataTable1TableAdapter.Fill(Me.DataSet1.DataTable1)

    'create new datasource that will hold all records
    Dim allRowsDataSource as New BindingSource
    allRowsDataSource.DataSource = Me.DataSet1.DataTable1
    
    'clear existing datasource and add all records 
     Me.ReportViewer1.LocalReport.DataSources.Clear()
    Me.ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("DataSet1", allRowsDataSource))

    'refresh report
    Me.ReportViewer1.RefreshReport()
    
    'export the report to pdf
    Dim pdfBytes As Byte() = ReportViewer1.LocalReport.Render("PDF")
    Dim pdfFileName As String = "Report.pdf"
    File.WriteAllBytes(pdfFileName, pdfBytes)
    'Open the pdf file
    Process.Start(pdfFileName)
End Sub

[schoemr]

I'm just reading eveything again you said last week and you mention the problem with the NULL...

This is one way maybe that you can handle it:

For Each row As DataRow In Me.DataSet1.DataTable1.Rows
    If row("FieldName") Is DBNull.Value Then
        row("FieldName") = "Default Value"
    End If
Next

you can place it right after the Fill (Me.DataTable1TableAdapter.Fill(Me.DataSet1.DataTable1))

[Tajaldeen]

Hi Tajaldeen,

So I played around with this. Can you test it and let me know...?

This time only first record shown up.

[Tajaldeen]

I'm just reading eveything again you said last week and you mention the problem with the NULL...

This is one way maybe that you can handle it:

Thank you very much , If one of the fields is Null, i show a message for the user that he should provide his data first, that solved the whole issue.

[schoemr]

Okay so we managed to display the last record and now the first record How does it look? I mean for the student. Is it displaying correctly in pdf?

[Tajaldeen]

Okay so we managed to display the last record and now the first record How does it look? I mean for the student. Is it displaying correctly in pdf?

Perfictly displayed , the only issue is, how can we display all records in one PDF file to be able to print them all at one press.

[schoemr]

Hi Tajaldeen,

This turned out to be not as easy as I initially thought! I started to think that I bit off more than I can chew...

Well I managed to get it right. After much search and try and try and try... But I am happy I did it because I learned a few things. It's almost midnight here now and it feels like I just wrote an exam!

I'll show you tomorrow what I did.

In the meantime can you please click on Tools >> Nuget Package Manager >> Manage Nuget Packages for Solution

and install iTextSharp:

Attachment 186807

[schoemr]

In vbforums you can only paste 5 images at a time so I will explain what I did in more than one reply.

I created a mini project, because I wanted to test the various exports before posting it here. I used a SQL database named (Northwind). It is free sample database that contains data for a fictitious company called "Northwind Traders" and this company sells food and beverages. I am going to make a few tutorials on RDLC reporting using the Northwind database.

NOTE: I explain a lot of detail on how to create a rdlc report because I also want to use this to make a tutorial later on - so please bear with me

So for this report:

DataSet name is "NorthwindDataSet"
Table name is "Customers"

You will obviously have to change these names to what you have for your student database since I use Customers and you use Students.


I created a main form and it is just a drag and drop from the Customers table. As you can see from below image is it just the basic Customer details. There are 90 customers.


Attachment 186811



Next I created the RDLC report in the designer as follows:

Step1: In the solution explorer right-click on your project name and select "Add" >> "New Item"
Step2: Browse down in the list and select "Report"
Step3: Provide a name for your RDLC report (in my example I named it "Customers.rdlc")

NOTE: If you do not find "Report" in the list then it means that you do not have the .rdlc report designer installed and you will have to install it first before continuing here. I will make another tutorial on how to do it because I see there was questions about that in the past.

Once you have the .rdlc report designer open the next action is to specify the Data Source and Data Set for the report:


Specify the Data Source and Data Set for RDLC Report in DataSet Properties

Step1: Open "Report Data" by clicking in "View" >> "Report Data" (make sure you click on a blank space in the .rdlc designer before clicking on "View"). If you do not see Report Data then click "View" >> "Other Windows"
Step2: In the Report Data window click on "New"
Step3: You will see the default name of the DataSet is "DataSet1" (You can leave it as is for the purpose of this example)
Step4: In the middle there is a drop down and you can click on it to select your DataSource (in my example it is NorthWindDataSet)
Step5: In the bottom drop down and you can click on it to select your DataSet (in my example it is Custromers)

After specifying your Data Source and Data Set it should look like this:

Attachment 186812

Now you can click on "Ok" and from here you can drag and drop the respective columns that you want onto the designer. Each column will now be a textbox and display "Expr". In my example I added labels and aligned everything neatly. I also added another label for the heading:


Attachment 186813


That is the report. The next step is to display the rdlc report. For this we use a Userform and a ReportViewer.

Using ReportViewer to Display RDLC Report

Step1: In the solution explorer right-click on your project name and select "Add" >> "Userform"
Step2: Provide a name for your from in my example I just named it "ReportCustomers"
Step3: In the solution explorer right-click on your project name and select "Add" >> "ReportViewer"
Step4: Place the ReportViewer on your form.

NOTE: If you do not find "ReportViewer" in the list then it means that you do not have the ReportViewer installed and you will have to install it first before continuing here. I will make another tutorial on how to do it because I see there was questions about that in the past.

Once you have placed the reportViewer on your form you will be prompted to choose a report. If not click on the tiny arrow in the top-right corner of the reportviewer, and select the rdlc report from the drop down (In my example it is Customers.rdlc)

Attachment 186814

In the next post I will explain:

1) How to view the report and export it to PDF, Word, and Excel (with the default ReportViewer functionalities.
2) Display "a" Selected customer in the report using a ComboBox.
3) Export the selected customer to a pdf file.
4) Export all customers to multiple pdf files (each customer in a pdf file)
5) Combine all customers into one pdf file and open in pdf viewer

[schoemr]

Load RDLC Report with ReportViewer


Once the Form with the reportViewr is loaded the RLDC report will populate in the form Load event. If you double-click on the Form (not the ReportViewer) you will see:

Private Sub ReportCustomers_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)
        Me.ReportViewer1.RefreshReport()
    End Sub

And the report will look like this:


Attachment 186825


Note that in the menu bar of the report viewer you can Print, and if you click on the "Save" icon there is an option to export to PDF, Word, and Excel.

At this point it is also worth pointing out that at the time you selected the rdlc report for the ReportViewer the following was automatically placed at the bottom of the form design view:

Attachment 186818

The next thing we want to do is to place a combobox on the form and populate it with all the "Company Names" from the customer table. Now for this you will have to use a separate BindingSource and TableAdapter. The reason for this is because the current BS and TA populates "a" customer at a time, and on the same report view we would like to populate ALL the Company Names.

Setting up the DataBound ComboBox

Step1: From the Toolbox drag and drop a ComboBox on the Form with the ReportViewer
Step2: In your Data Sources window right-click on NorthWindDataSet and select "Edit DataSet with Designer"
Step3: Once you have the NorthWindDataSet open in design view right-click on any blank space and click "Add" >> "TableAdapter"
Step4: Verify your connection sting (NorthWindConnectionString) - Click Next
Step5: Select "Use SQL Statements" (it should be the default) - Click Next
Step6: Enter the query as follows:

SELECT        CustomerID, CompanyName
FROM            Customers

Step7: Click "Finish"

* Notes:
- In step2 if you do not see the Data Sources window then click on "View" >> "Other Windows" >> "DataSources". I usually pin the Data Sources window because I work with it a lot.
- There are much more that can be explained in these 7 steps above, but for the purposes of this tutorial that is all we will do.
- You will now notice that an additional Customers1BindingSource and Customers1TableAdapter was automatically placed in the toolbox below (refer to image above)

Binding the Combobox to the DataSource

Step1: In the top-right corner of the ComboBox click on the small arrow.
Step2: Check "Use Data Bound Items"
Step3: Start on top and click on "Data Sourcs" to open the drop down. Now this is very important: Click "Other Data Sources" >> "Project Data Sources" >> "NorthWindDataSet" >> "Customers1"

Then set the:

- Display Member: "CompanyName"
- Value Member: "Company Name"

Thats it! The combobox is now binded to a data source.

Next we will add 3 Buttons to the Form and the form now looks like this:

Attachment 186822

Now we need to update the report based on the combobox selection. Double click on the combobox and add the following code to update the report when the combobox selection change:

 Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Me.CustomersTableAdapter.FillByCompanyName(Me.NorthwindDataSet.Customers, ComboBox1.Text)
        Me.ReportViewer1.RefreshReport()
    End Sub

Exporting Report Data to PDF

Ensure that you add the following namespaces on top of the form with the reportViewer:

Imports System.IO
Imports iTextSharp.text
Imports iTextSharp.text.pdf

1) The 1st button - Combine in PDF (ALL)

This code will combine all the generated reports into one PDF document using PdfCopy. It makes use of a temporary file and once the document is displayed it will delete the temp file. The reason why there is a 5 second delay before the temp file is deleted is so that the pdf viewer have enough time to render the report (I'm not 100% if this is the correct method, but hey, it's working!)

I also make use of the WaitCursor to indicate to the user that the export is busy. I should actually also disable the buttons while the export is in process.

Private Sub ButbtnExportToSingleReport_Click(sender As Object, e As EventArgs) Handles btnCombine.Click

       Me.Cursor = Cursors.WaitCursor
        Me.NorthwindDataSet.Customers.Clear()
        Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)

        Dim outputFile As String = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "AllCustomers.pdf")
        Dim document As New Document
        Dim pdfCopy As PdfCopy = New PdfCopy(document, New FileStream(outputFile, FileMode.Create))

        document.Open()
        Dim customersCopy As NorthwindDataSet.CustomersDataTable = NorthwindDataSet.Customers.Copy()

        For Each row As DataRow In customersCopy.Rows
            Me.CustomersTableAdapter.FillByCompanyName(Me.NorthwindDataSet.Customers, row("CompanyName").ToString())
            Me.ReportViewer1.RefreshReport()

            Dim pdfRenderBytes As Byte() = ReportViewer1.LocalReport.Render("PDF")
            Dim reader As New PdfReader(pdfRenderBytes)

            For i As Integer = 1 To reader.NumberOfPages
                pdfCopy.AddPage(pdfCopy.GetImportedPage(reader, i))
            Next

            reader.Close()
        Next

        document.Close()

        Process.Start(outputFile)

        Me.Cursor = Cursors.Default

End Sub.

90 Customers exported to PDF:

Attachment 186823

[schoemr]

The 2nd button - Exporting ALL customer records as individual PDF Files

PDF files is created in a folder named "temp" in the My Documents of your PC. NOTE: If you export to this folder and there are records from the previous export it will overwrite it!

 Private Sub btnIndividualFiles_Click(sender As Object, e As EventArgs) Handles btnIndividualFiles.Click

        Me.Cursor = Cursors.WaitCursor

        Me.NorthwindDataSet.Customers.Clear()
        Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)

        Dim tempPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\temp\"
        If Not Directory.Exists(tempPath) Then
            Directory.CreateDirectory(tempPath)
        End If

        Dim customersCopy As NorthwindDataSet.CustomersDataTable = NorthwindDataSet.Customers.Copy()

        For Each row As DataRow In customersCopy.Rows
            If Not row.IsNull("CompanyName") Then
                Me.CustomersTableAdapter.FillByCompanyName(Me.NorthwindDataSet.Customers, row("CompanyName"))
                Me.ReportViewer1.RefreshReport()

                Dim pdfBytes As Byte() = ReportViewer1.LocalReport.Render("PDF")

                File.WriteAllBytes(tempPath & row("CompanyName").ToString().Replace("/", "_") & ".pdf", pdfBytes)
            End If
        Next

        Process.Start("explorer.exe", tempPath)

        Me.Cursor = Cursors.Default

        MsgBox("Done!")

End Sub

Attachment 186824

[schoemr]

The 3rd button - Export single customer based on combobox selection

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Me.Cursor = Cursors.WaitCursor
        Dim selectedCompanyName As String = ComboBox1.SelectedValue()

        Me.CustomersTableAdapter.FillByCompanyName(Me.NorthwindDataSet.Customers, selectedCompanyName)

        Me.ReportViewer1.RefreshReport()

        Dim pdfBytes As Byte() = ReportViewer1.LocalReport.Render("PDF")

        Dim outputFile As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\temp\" & selectedCompanyName.Replace("/", "_") & ".pdf"

        File.WriteAllBytes(outputFile, pdfBytes)

        Me.Cursor = Cursors.Default

        Process.Start("explorer.exe", outputFile)
     End Sub

[Tajaldeen]

Ensure that you add the following namespaces on top of the form with the reportViewer:

Imports iTextSharp.text
Imports iTextSharp.text.pdf

Okay let's deal with the first problem I had.
I've downloaded the file named iTextSharp, it contains many Zip files most of them are Dll extended, there is not execute file among them, I have no clue how to deal with or setup such files in order to be able to import iTextSharp.text and ITextSharp.text.pdf namespace.

Any instructions on that or link? maybe I got the wrong file

[schoemr]

In your project you must click on Tools >> Nuget Package Manager >> Manage Nuget Packages for Solution and then:


Attachment 186828



In the search box type iTextSharp. Once you find it then check the Project and Customers checkboxes (In your case it will be whatever the name of your project is)

The click "Install" (mine is howing "Uninstall" because I already have it installed. Yours will say "Install"

[schoemr]

Or.... you can try Tools >> Nuget Package Manager >> Package Manager Console

It will open a command prompt:

Then you type Install-Package iTextSharp like so:

PM> Install-Package iTextSharp

then press enter and wait for it to install. You will get a message at the bottom where you typed it to say something like "successfully installed"



Attachment 186829

[SALIAHM]

Or.... you can try Tools >> Nuget Package Manager >> Package Manager Console

It will open a command prompt:

Hi, its me (Tajaldeen) Thank you very much my dear teacher, finally i managed to do it perfectly as you told me step by step.
only a little problem that i couldn't figure out
parameters fields do not change for each students, its stay the same for all records

[schoemr]

Hi Taljadeen,

For which button? Did you verify the code I posted here because I did make a change last week.

If it is the exact code I have here can you tell me which method have the problem? The combined pdf?

[SALIAHM]

Hi Taljadeen,

For which button?

Combine all in PDF , here is my code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click



        Me.Cursor = Cursors.WaitCursor
        Me.DataSet1.DataTable1.Clear()
        Me.DataTable1TableAdapter.Fill(Me.DataSet1.DataTable1)

        Dim outputFile As String = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "AllStudents.pdf")
        Dim document As New Document
        Dim pdfCopy As PdfCopy = New PdfCopy(document, New FileStream(outputFile, FileMode.Create))

        document.Open()
        Dim customersCopy As DataSet1.DataTable1DataTable = DataSet1.DataTable1.Copy()

        For Each row As DataRow In customersCopy.Rows
            Me.DataTable1TableAdapter.Search_ID(Me.DataSet1.DataTable1, row  ("ID") .ToString)
            Me.ReportViewer1.RefreshReport()

            Dim pdfRenderBytes As Byte() = ReportViewer1.LocalReport.Render("PDF")
            Dim reader As New PdfReader(pdfRenderBytes)

            For i As Integer = 1 To reader.NumberOfPages
                pdfCopy.AddPage(pdfCopy.GetImportedPage(reader, i))
            Next

            reader.Close()
        Next

        document.Close()

        Process.Start(outputFile)

        Me.Cursor = Cursors.Default

    End Sub

[schoemr]

Hi Taljadeen,

I can see you are a very lazy man Just want to drink coffee and print records hey!!

You declare variables with "customers" (from the example), but you have "students" You should fix that...

Okay but I don't think that is the problem...I think it is just a small mistake in your code. In my example I have a Customers table. You have students table. Can you show me the structure of that students table?

e.g.

- ID
- FirstName
- Lastname
- StudentNumber

And also what is the exact name of that table?

I think the mistake is in this line:

Me.DataTable1TableAdapter.Search_ID(Me.DataSet1.DataTable1, row  ("ID") .ToString)

[SALIAHM]

Hi Taljadeen,



And also what is the exact name of that table?

DataSetName is : DataSet1
Attachment 186903

the problem is how to make parameters change for each student like the other field of the table , it works fine when i change the combobox value.

[schoemr]

DataSetName is : DataSet1
Attachment 186903

On vbforums that attachement button is not working. Click on "advanced" and "manage attachments" then upload the attachment from there..

[SALIAHM]

On vbforums that attachement button is not working. Click on "advanced" and "manage attachments" then upload the attachment from there..

[schoemr]

Okay ill look into this tonight.. I can't do it right now, k?

[SALIAHM]

Thanks for your kind help, i really do appreciate that.
the parameters fields i mentioned contains the NoToText function that i talked about on the other thread, i told you how did i slove that problem by making textboxes, apply NoToText function and send them to my report throght parametes, if we could just make that function works direcly from the report there will be no need to these parameters, because they are not included in the Table that is why they dont change for each record i guess, you have mentioned something about using Class and you've attached some kind of a picture but i couln't access it.

[schoemr]

Before we go and try to re-invent the wheel:

1) What happens if you remove the function?
2) You said

parameters fields do not change for each students

so it did correctly for some? Only for the 1st record? Only for the last record? Or any record(s) in between?

I doubt if it could be the function, but honestly, I am not sure. We will have to do a bit of experimenting I think..

Even if you make use of a class for the function, you still need to define rdlc report parameters.

I tested the customers example app I created and it is working perfectly. We just have to figure out what is different from what you are doing now. But my example works. 100% correctly.

[schoemr]

Also look at my post #23 can you try that? If "a" selected record export correctly then it is not the function...

[SALIAHM]

Also look at my post #23 can you try that? If "a" selected record export correctly then it is not the function...

I'm not quite sure if this is against the rules here, but in the following link you will find my whole project, go to Form9 Report3, button PrintAll and you can see yourself what is wrong if u don't mind, if this is against the rules i do apologize for that and i can edit this post right now, all i want you to do is to have a closer look you may get somewhere.

https://drive.google.com/file/d/150B...usp=share_link

[schoemr]

Hi, what it the name I should call you, because it changed?

Okay, I am not insulting you, k? But after having a look at your project I can say it is a big mess. It is no wonder you wanted to export pdf's one by one because your database design is very bad to begin with. Are you also like me that you're not really a programmer? Or are you a student?

Anyways, you have not gone that far in your project yet and if you want you can still change a bit of direction with your current project. But I also have some questions.:

1) Why did you choose an Access database? (It is not wrong; I just want to understand what your rationale was)
2) Would you consider going over to a SQL database?
3) You have two tables can you briefly describe what the purpose of each table is? I see there is no relationship between these two tables.
4) So in your app you want to enter English numeric values and then output that to Arabic numerals in the reports?


and then.....what on earth is this?? Please tell me this is not how you divided the sections 'visually'....

Attachment 186907

Maybe you can consider starting over. But this time make sure you do it right. I will help you, many people here will give advice. I started with my app and fighting that Ausie here since I was 18. I didn't know anything about vb.net. But I had an idea. And that is how it starts. You start small and work at it and never give up. Maybe you build a great app that you can sell...

What I'm saying is maybe for now you just want a lil tool to print records, but if you do it right it can grow into something that can be worthwhile...

Also, I notice you perform calculations in your database. I suppose technically speaking it is not wrong, but it is not a good practice. Maybe someone else will have more to say on this, but I am of the view that you have to keep your data in your database as raw an unprocessed as possible. Unless there is of course a clear performance advantage which I don't see for a student record system. Storing calculated values such as the sum of test scores for each student can lead to data integrity issues because the values may not be updated automatically if data changes...

[SALIAHM]

Hi, what it the name I should call you, because it changed?

Just call me Tajaldeen or my little student
I'm not a programmer, not even close to be one, I'm a beginner student down to the basic level, but I make a tiny little progress every 2 or 3 decades.
1) I choose access because i know how to work on it, make functions, calculations … etc, SQL I have no idea how to deal with it.
2) Each table has a specific and separate job, the tbl_accounts is for students records, Marks, calculation fields, school_Year is for some fixed information that user has to provide such as school name, term, school manager, governorate … etc.
In my app everything is okay and perfect and work efficiently, the only problem is is the parameters fields that didn’t change when I press PrintAll Button that you helped me to create.
3) Yes, these crap /// ###### ///////// is to divide my access db visually, I know it looks bad but it helped me a lot to keep everything in place and to avoid more mess.
Okay, I think my project is in a save hand, just try to how can we solve the parameters issue, it's not a big deal , not necessary either, I can pick them up one by one and drink my coffee later.

[SALIAHM]

4) So in your app you want to enter English numeric values and then output that to Arabic numerals in the reports?

No, i already did that , everything is cool and work perfectly, the only prolem is in the PrintAll function you provide, it only change the access field for each student , parameters stay the same for the specific student who displayed by combobox.

[schoemr]

Hi Taljadeen,

This project I downloaded is not even compiling because it's got too many errors. It seems there are still leftovers from other things you tried like crystal reports, webforms, etc. Also I can't see where you tried to export records.

Your database is really bad design, and I am surprised you got some things to work (apparently) I did offer you an alternative, but your response was like someone who's out at sea and his lil boat is sinking and another boat comes by and offer to fix the leak and you respond by saying, "no, no" I'm fine my boat is sailing, just give me a bucket"

I really want to help you, but I am unable to.

I wish you best of luck with your project.