locked
Passing datagridview to report viewer By using DataTable RRS feed

  • Question

  • Hi everybody ,

    I have some code for pagination DGV , It is working properly ..But recently i need to pass the DGV table to a report viewer 

    After searching on the web i found a snippet to do this job but with some error .. Like the Report viewer show the table header only without data or some columns only,This is my code to passing DGV table to Report viewer

    Form8.ReportViewer1.LocalReport.DataSources.Clear()
    Form8.ReportViewer1.LocalReport.ReportPath = Path.Combine(Application.StartupPath, "Report1.rdlc")
    
    Dim rds = New ReportDataSource("DataSet1", DataGridView1.DataSource) 
    Form8.ReportViewer1.LocalReport.DataSources.Add(rds)
          
     If Form8.ShowDialog() = DialogResult.OK Then
     Else
     End If
     Form8.Dispose()

    I enabled to fix that by filling the DGV again before going to the report viewer , It is OK for a DGV with one page but if i have more than one page the first page only pass 

    I tried to use the pagination code to solve the issue but i failed ..It has a Clone Datatable called "dtTemp" in order to clone a main datatable to make pages to the DGV.

    I tried to put it in this line (New ReportDataSource("DataSet1", dtTemp)) but i failed also 

    Here is my code to make pagination(In the comments) it works like a charm ,But without the code of passing data to Report Viewer

    Any Help,,,,,,,,,,,,, Thanks


    Regards From Egypt











    • Edited by Amr_Aly Thursday, January 23, 2020 12:34 PM
    Tuesday, December 31, 2019 11:56 AM

Answers

  • Thanks everybody ,

    I want to share with you how i enabled to solve my issue.I hoped to get help here or on StackOverFlow about this issue but i failed to get help at all due to the long code of "Pagination Data Grid View" i know that it is long , but it's so useful and wonderful .>>>>>>This is an update to my answer i think that will be useful to anyone need to pass datagridview to report viewer .

    After searching, The good way to perform this job is making a datatable then add the columns of the database columns to it then after the datagridview is populated with data , Looping in Datagridview Rows ,In order to ensure that every page pass to the report viewer without any missing of data . Ignoring the pagination code .

    Whatever page exist on the Datagridview this next code will transfer the DGV table(the page appears on the form) to the report viewer 

    Dim con As New SqlConnection(cs)
    Dim da As New SqlDataAdapter()
    Dim ds As New DataSet
    Dim cmd As New SqlCommand("select ID,
                                      EName,
                                      Tel,
                                      Mob1,
                                      StartDate,
                                      EndDate,
                                      EDone
                                from Emp")
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = con
                    da.SelectCommand = cmd
                    
                    Dim gdt As New DataTable
                    gdt.Columns.Add("ID")
                    gdt.Columns.Add("EName")
                    gdt.Columns.Add("Tel")
                    gdt.Columns.Add("Mob1")
                    gdt.Columns.Add("StartDate")
                    gdt.Columns.Add("EndDate")
                    gdt.Columns.Add("EDone")
                    For i = 0 To DataGridView1.Rows.Count - 1
                        gdt.Rows.Add(DataGridView1.Rows(i).Cells(0).Value,
                                    DataGridView1.Rows(i).Cells(1).Value,
                                    DataGridView1.Rows(i).Cells(2).Value,
                                    DataGridView1.Rows(i).Cells(3).Value,
                                    DataGridView1.Rows(i).Cells(4).Value,
                                    DataGridView1.Rows(i).Cells(5).Value,
                                    DataGridView1.Rows(i).Cells(6).Value)
                    Next
    
                    Form8.ReportViewer1.LocalReport.DataSources.Clear()
                    Form8.ReportViewer1.LocalReport.ReportPath = "Report1.rdlc"
    Dim rds = New ReportDataSource("DataSet1", gdt) 
                    Form8.ReportViewer1.LocalReport.DataSources.Add(rds)
    That's no need to fill DGV again like my first answer

    Now i will modify my question and delete some unnecessary code to be clear to any new one 

    Thanks again –


    Regards From Egypt



    • Marked as answer by Amr_Aly Monday, January 20, 2020 8:02 AM
    • Edited by Amr_Aly Thursday, January 23, 2020 12:29 PM
    Monday, January 20, 2020 8:02 AM

All replies

  • Hi,

    I feel sorry but could you simplify the code appropriately? Because now I have a bit of difficulty concentrating on reading it thoroughly.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 3, 2020 8:34 AM
  • Thanks for paying attention Julie,

    First short code is to pass datagridview table to Report viewer .

    But the second long one is to make a "Pagination to the DGV table", It divides DGV table into pages this is the link to DGV Pagination <<<

    https://support.microsoft.com/en-us/help/305271/how-to-perform-paging-with-the-datagrid-windows-control-by-using-visua

    >>>Unfortunately it gives "Page Not Found" 

    But i will write the second pagination code without any additions and without any useless comments

    ''' <summary>
    ''' The next lines are for making pagination to datagridview table, I will explain it briefly 
    '''page size TextBox,It's for determine the number of the rows that i want it to appear to the user on the page,and i make the user change this number as he want from(1 row on the page to 100 rows only) the default is 25 rows
    '''TextBox to DIsplay page number, It enables the user to show the whole number of pages and what is the current page,
    '''(i.e) if DGV table results 200 rows and he write in page size TextBox 25 , He wiil have 8 pages.
    '''And Four pictureboxes as (first,next,previous and last Buttons) To enable user to browse between pages,
    '''You will see in every PictureBox the btnFill Button to fill the DGV again before loading the page by LoadPage(),And the Sub LoadPage() To load the cloned DataTable. 
    '''The Function CheckFillButton() just for check if the btnFill is clicked or not in order to fill the DGV.
    '''Really it is a nice code to make DGV divides into Pages.
    '''Every Select Case statements are for avoiding errors ,I faced it during using this pretty snippet   
    ''' </summary>
    
        Sub LoadPage()
            Dim i As Integer
            Dim startRec As Integer
            Dim endRec As Integer
            Dim dtTemp As DataTable
    
            Select Case True
                Case DataGridView1.RowCount = Nothing
                    
                    Exit Sub
            End Select
    
            'Duplicate or clone the source table to create the temporary table.
            dtTemp = dtSource.Clone
    
            If currentPage = PageCount Then
                'MsgBox("currentPage = PageCount")
                endRec = maxRec
            Else
                endRec = pageSize * currentPage
            End If
    
            startRec = recNo
    
            'Copy the rows from the source table to fill the temporary table.
            For i = startRec To endRec - 1
                dtTemp.ImportRow(dtSource.Rows(i))
                recNo = recNo + 1
            Next
    
            DataGridView1.DataSource = dtTemp.DefaultView
            
            DisplayPageInfo()
    
        End Sub
    
        Private Function CheckFillButton() As Boolean
    
            'Check if the user clicks the "Fill Grid" button.
            If pageSize = 0 Then
                MessageBox.Show("PLS determine the size of pages", "Hint")
                CheckFillButton = False
            Else
                CheckFillButton = True
            End If
    
        End Function
    
        Sub DisplayPageInfo()
            txtDisplayPageNo.Text = "Page " & currentPage.ToString & "/ " & PageCount.ToString
        End Sub
    
        Private Sub PictureBox3_Click(sender As Object, e As EventArgs) Handles PictureBox3.Click
            'As button first
            Label5.Text = "first"
            If Not CheckFillButton() Then Return
            Select Case True
                Case Val(txtPageSize.Text) = 0 Or txtPageSize.Text = ""
                    MsgBox("items cannot be zero", MsgBoxStyle.Exclamation, "Error")
                    
                    txtPageSize.Text = "25"
                    Exit Sub
            End Select
        
            ' Check if you are already at the first page.
            If currentPage = 1 Then
                MessageBox.Show("You are at the First Page!")
                Return
            End If
    
            currentPage = 1
            recNo = 0
    
            LoadPage()
            
        End Sub
    
        Private Sub PictureBox4_Click(sender As Object, e As EventArgs) Handles PictureBox4.Click
            'As button next
            Label5.Text = "next"
            'If the user did not click the "Fill Grid" button then Return
            If Not CheckFillButton() Then Return
            Select Case True
                Case Val(txtPageSize.Text) = 0 Or txtPageSize.Text = ""
                    MsgBox("items cannot be zero", MsgBoxStyle.Exclamation, "Error")
                   
                    txtPageSize.Text = "25"
                    Exit Sub
            End Select
           
            'Check if the user clicked the "Fill Grid" button.
            If pageSize = 0 Then
                MessageBox.Show("Set the Page Size, and then click the button!")
                Return
            End If
    
            currentPage = currentPage + 1
    
            If currentPage > PageCount Then
                currentPage = PageCount
    
                'Check if you are already at the last page.
                If recNo = maxRec Then
                    MessageBox.Show("You are at the Last Page!")
                   
                    Return
                End If
            End If
    
            Select Case True
                Case currentPage <= 1
                    MsgBox("Your are in the same page " + vbCrLf +
                           "Try to divide it into pages",
                           MsgBoxStyle.Exclamation, "Error")
                    Exit Sub
            End Select
    
            LoadPage()
            
        End Sub
    
        Private Sub PictureBox5_Click(sender As Object, e As EventArgs) Handles PictureBox5.Click
            'As button previous
            Label5.Text = "previous"
            If Not CheckFillButton() Then Return
            Select Case True
                Case Val(txtPageSize.Text) = 0 Or txtPageSize.Text = ""
                    MsgBox("items cannot be zero", MsgBoxStyle.Exclamation, "Error")
                    'txtPageSize.Focus()
                    txtPageSize.Text = "25"
                    Exit Sub
            End Select
            
    
            If currentPage = PageCount Then
                recNo = pageSize * (currentPage - 2)
            End If
    
            currentPage = currentPage - 1
    
            'Check if you are already at the first page.
            If currentPage < 1 Then
                MessageBox.Show("You are at the First Page!")
                
                currentPage = 1
                Return
            ElseIf currentPage = 1 Then
                'MessageBox.Show("current page = 1")
                recNo = pageSize
            Else
                recNo = pageSize * (currentPage - 1)
            End If
            
            'this select solve a problem to fill the grid
            Select Case True
                Case currentPage <= 1
                    MsgBox("Your are in the page 1 " ,
                           MsgBoxStyle.Exclamation, "Error")
                    btnFill_Click(sender, e)
                    
                    Exit Sub
            End Select
    
            LoadPage()
                 
        End Sub
    
        Private Sub PictureBox2_Click(sender As Object, e As EventArgs) Handles PictureBox2.Click
            'As button last
    
            Label5.Text = "last"
            If Not CheckFillButton() Then Return
            Select Case True
                Case Val(txtPageSize.Text) = 0 Or txtPageSize.Text = ""
                    MsgBox("items cannot be zero", MsgBoxStyle.Exclamation, "Error")
                   
                    txtPageSize.Text = "25"
                    Exit Sub
            End Select
            
            ' Check if you are already at the last page.
            If recNo = maxRec Then
                MessageBox.Show("You are at the Last Page!", "Hint")
                Return
            End If
    
            currentPage = PageCount
    
            recNo = pageSize * (currentPage - 1)
    
            LoadPage()
            Movements(PictureBox2, 0.05)
    
        End Sub
    
        Private Sub txtPageSize_TextChanged(sender As Object, e As EventArgs) Handles txtPageSize.TextChanged
            If Val(txtPageSize.Text) > 100 Then
                Exit Sub
            End If
            Select Case True
                Case Val(txtPageSize.Text) = 0 Or txtPageSize.Text = ""
                    MsgBox("Items cannot be Zero", MsgBoxStyle.Exclamation, "Error")
                    txtPageSize.Text = "25"
                    Exit Sub
            End Select
            btnFill_Click(sender, e)
    
    
        End Sub
    
        Private Sub btnFill_Click(sender As Object, e As EventArgs) Handles btnFill.Click
    
                    Using conn As New SqlConnection(cs)
                        conn.Open()
                        Using cmd As New SqlCommand("select * from Emp")
                            'cmd.Parameters.Add("@a", SqlDbType.Int).Value = Val(TextBox5.Text)
                            cmd.CommandType = CommandType.Text
                            cmd.Connection = conn
    
                            'Set the DataAdapter's query.
                            Using ds As New DataSet(), da As New SqlDataAdapter(cmd)
    
                                ' Fill the DataSet.
                                da.Fill(ds, "Emp")
                                'DataGridView1.DataSource = ds.Tables("Emp")
                                ' Set the source table.
                                dtSource = ds.Tables("Emp")
                                
                            End Using
                        End Using
                    End Using
    
                    'Set the start and max records. 
                    pageSize = CInt(Val(txtPageSize.Text))
                    maxRec = dtSource.Rows.Count
    
                    PageCount = maxRec \ pageSize
    
                    ' Adjust the page number if the last page contains a partial page.
                    If (maxRec Mod pageSize) > 0 Then
                        PageCount = PageCount + 1
                    End If
    
                    'Initial seeings
                    currentPage = 1
                    recNo = 0
    
                    ' Display the content of the current page.
                    LoadPage()
    
       End Sub    


    Oh, I have a public DataTable called "dtSource" .This code is so strong to divide DGV table into Pages

    and it works with me till now ....

     

    Regards From Egypt



    • Edited by Amr_Aly Saturday, January 4, 2020 2:00 PM
    Friday, January 3, 2020 9:27 AM
  • Thanks everybody ,

    I want to share with you how i enabled to solve my issue.I hoped to get help here or on StackOverFlow about this issue but i failed to get help at all due to the long code of "Pagination Data Grid View" i know that it is long , but it's so useful and wonderful .>>>>>>This is an update to my answer i think that will be useful to anyone need to pass datagridview to report viewer .

    After searching, The good way to perform this job is making a datatable then add the columns of the database columns to it then after the datagridview is populated with data , Looping in Datagridview Rows ,In order to ensure that every page pass to the report viewer without any missing of data . Ignoring the pagination code .

    Whatever page exist on the Datagridview this next code will transfer the DGV table(the page appears on the form) to the report viewer 

    Dim con As New SqlConnection(cs)
    Dim da As New SqlDataAdapter()
    Dim ds As New DataSet
    Dim cmd As New SqlCommand("select ID,
                                      EName,
                                      Tel,
                                      Mob1,
                                      StartDate,
                                      EndDate,
                                      EDone
                                from Emp")
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = con
                    da.SelectCommand = cmd
                    
                    Dim gdt As New DataTable
                    gdt.Columns.Add("ID")
                    gdt.Columns.Add("EName")
                    gdt.Columns.Add("Tel")
                    gdt.Columns.Add("Mob1")
                    gdt.Columns.Add("StartDate")
                    gdt.Columns.Add("EndDate")
                    gdt.Columns.Add("EDone")
                    For i = 0 To DataGridView1.Rows.Count - 1
                        gdt.Rows.Add(DataGridView1.Rows(i).Cells(0).Value,
                                    DataGridView1.Rows(i).Cells(1).Value,
                                    DataGridView1.Rows(i).Cells(2).Value,
                                    DataGridView1.Rows(i).Cells(3).Value,
                                    DataGridView1.Rows(i).Cells(4).Value,
                                    DataGridView1.Rows(i).Cells(5).Value,
                                    DataGridView1.Rows(i).Cells(6).Value)
                    Next
    
                    Form8.ReportViewer1.LocalReport.DataSources.Clear()
                    Form8.ReportViewer1.LocalReport.ReportPath = "Report1.rdlc"
    Dim rds = New ReportDataSource("DataSet1", gdt) 
                    Form8.ReportViewer1.LocalReport.DataSources.Add(rds)
    That's no need to fill DGV again like my first answer

    Now i will modify my question and delete some unnecessary code to be clear to any new one 

    Thanks again –


    Regards From Egypt



    • Marked as answer by Amr_Aly Monday, January 20, 2020 8:02 AM
    • Edited by Amr_Aly Thursday, January 23, 2020 12:29 PM
    Monday, January 20, 2020 8:02 AM