Refresh Report Viewer when data changes

[yousamook]

hi all,

I am working with vb.net 2010 SQL express 2008 and ReportViewer. I have created a form that a user can add, edit and delete data which is all stored in an SQL db. I am printing out reports for the user with ReportViewer. The issue I am encountering now is that at runtime if I make any changes and decided to view the changes via a report with ReportViewer, the change will not reflect. I have to close the program and open it up again so that I can view the changes. What do I have to do in order to view the changes view ReportViewer during runtime? This is my code:

First Form, Process Button:

Private Sub btnProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcess.Click

        ' Declare the values
        Dim dblHrsWorked As Double
        Dim dblHrRate As Double
        Dim dblTotalMedSS As Double
        Dim dblTotalGrs As Double
        Dim dblTotalBfWH As Double
        Dim dblWithHold As Double
        Dim dblLessWH As Double
        Dim dblGndTotal As Double
        Dim dblMedSS As Double = 0.0565

        ' validates that user entered a value for hours worked
        If txtHrsWrkd.Text = "" Then
            MessageBox.Show("Please do not leave hours worked blank.", "Error", MessageBoxButtons.OK)
            txtHrsWrkd.Focus()
            txtMedSS.Clear()
            Exit Sub
        End If

        ' assign user input to variables
        dblHrsWorked = Convert.ToDouble(txtHrsWrkd.Text)
        dblHrRate = txtHrRate.Text

        dblWithHold = lblWithH.Text ' EMPLOYEE'S ALLOWANCE OF WITHHOLDINGS

        ' checks to see the employee's filing status
        Dim IsMarried As Boolean

        If lblfStatus.Text = "SINGLE" Then                  ' if Single
            IsMarried = False
        ElseIf lblfStatus.Text = "MARRIED" Then               ' if Married
            IsMarried = True
        ElseIf lblfStatus.Text = "HEAD OF HOUSEHOLD" Then     ' if Head of Houshold
            IsMarried = False
        ElseIf lblfStatus.Text = "WIDOWER" Then
            MessageBox.Show("Please select a different filing status" & _
                            " other than " & lblfStatus.Text & " !")
            txtHrsWrkd.Focus()
            txtHrsWrkd.Clear()
            Exit Sub
        End If

        ' calculate total
        dblTotalGrs = (dblHrRate * dblHrsWorked)
        dblTotalMedSS = dblTotalGrs * dblMedSS
        dblTotalBfWH = dblTotalGrs - dblTotalMedSS

        ' the value of federal tax withholding
        dblLessWH = CalcBiWeeklyFedWHFromTableB1(dblTotalGrs, IsMarried, dblWithHold)

        ' Grand Total earned
        dblGndTotal = dblTotalGrs.ToString - dblLessWH.ToString - dblTotalMedSS


        ' output total with currency format
        txtGross.Text = Format(dblTotalGrs, "Currency")
        txtMedSS.Text = Format(dblTotalMedSS, "Currency")
        'txtTotalbfwh.Text = Format(dblTotalBfWH, "Currency")
        txtWithHValue.Text = Format(dblLessWH, "Currency")
        txtGndTotal.Text = Format(dblGndTotal, "Currency")

        Try

            ' To check if the employee is already in our database
            Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Larry\Documents\Visual Studio 2010\Projects\" & _
                                          "DDSPayRoll\DDSPayRoll\DDSPayRoll.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
            Dim cmds As New SqlCommand("SELECT * FROM fedtax WHERE eid = '" & txtEmpID.Text & "' AND payfrom = '" & CDate(dtpFromDate.Text) & "' " & _
                                       "AND payto = '" & CDate(dtpToDate.Text) & "' ", conn)
            conn.Open()
            Dim sdr As SqlDataReader = cmds.ExecuteReader()

            If (sdr.Read() = True) Then

                txtHrsWrkd.Clear()
                txtMedSS.Clear()
                txtGross.Clear()
                'txtTotalbfwh.Clear()
                txtWithHValue.Clear()
                txtGndTotal.Clear()
                txtHrsWrkd.Focus()

                MessageBox.Show("User already has payroll for the specified pay period!")

                Exit Sub

            Else

                Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Larry\Documents\Visual Studio 2010\Projects\" & _
                                             "DDSPayRoll\DDSPayRoll\DDSPayRoll.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
                Dim constr As String = (" INSERT INTO fedtax " & _
                                        "(eid, payfrom," & _
                                        " payto, hrswrkd, hrlyrate, " & _
                                        "grosstotal, medss, withholding, " & _
                                        " grandtotal)" & _
                                        " values " & _
                                        " ( @val1, @val2, @Val3," & _
                                        " @Val4, @Val5, @Val6, @Val7," & _
                                        "  @Val9, @Val10) ") ' totalbfwh, ' @Val8,

                Dim cmd As New SqlCommand(constr, con)

                'Dim kk = cmd.BeginExecuteNonQuery()

                cmd.Parameters.Add(New SqlParameter("@val1", txtEmpID.Text))
                cmd.Parameters.Add(New SqlParameter("@val2", (Format(dtpFromDate.Text, "short date"))))
                cmd.Parameters.Add(New SqlParameter("@val3", (Format(dtpToDate.Text, "short date"))))
                cmd.Parameters.Add(New SqlParameter("@val4", txtHrsWrkd.Text))
                cmd.Parameters.Add(New SqlParameter("@val5", txtHrRate.Text))
                cmd.Parameters.Add(New SqlParameter("@val6", txtGross.Text))
                cmd.Parameters.Add(New SqlParameter("@val7", txtMedSS.Text))
                'cmd.Parameters.Add(New SqlParameter("@val8", txtTotalbfwh.Text))
                cmd.Parameters.Add(New SqlParameter("@val9", txtWithHValue.Text))
                cmd.Parameters.Add(New SqlParameter("@val10", txtGndTotal.Text))

                con.Open()

                Dim i As Integer
                i = cmd.ExecuteNonQuery()
                If i > 0 Then

                    ' cmd.EndExecuteNonQuery(kk)

                    MessageBox.Show("Payroll information Saved!")

                    txtHrsWrkd.Clear()
                    txtMedSS.Clear()
                    txtGross.Clear()
                    'txtTotalbfwh.Clear()
                    txtWithHValue.Clear()
                    txtGndTotal.Clear()
                    txtHrsWrkd.Focus()

                Else
                    MessageBox.Show("Payroll information failed to save")
                End If

                con.Close()
                con = Nothing

            End If

        Catch ex As Exception

            MessageBox.Show("Please fill in all the information. ", "Error")

        End Try



    End Sub

At this point, once the data is processed, the data is saved into the DB.

I hit the back button to go to the main menu and choose the from that carries the Reportviewer.

Form with Report Viewer code:

to view the report I click on the print button.

Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click

        If cboPayPeriod.SelectedItem = Nothing Then
            MessageBox.Show("Please select a Pay Period. ", "Error")
            Exit Sub
        End If

        Dim biwk As New Report_BWk

        Dim value As New Date

        lblPayFrom.Text = cboPayPeriod.Text ' SelectedItem

        ' MessageBox.Show(lblPayFrom.Text)

        value = lblPayFrom.Text

        biwk.valpay = value


        Me.biweeklyTableAdapter.Fill(Me.DDSPayRollDataSet.biweekly, biwk.valpay)
        Me.ReportViewerbiwk.RefreshReport()
        Me.ReportViewerbiwk.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout)

    End Sub

At this point after processing the data, the ReportViewer will not show the updated information. I have to close the program and open the program again, go to Report Viewer and view what I have previously processed.

What do I have to do so the ReportViewer can show the updated data during runtime?

Please help!

Thanks,

Larry

[vishalrane]

If the data gets updated by some other program, you could use a timer control to refresh the report every 5 minutes (or whatever time frame you would like.)

If the data gets updated by your program, then just put the code that loads the report in a method and call that method every time the user updates the data.

[yousamook]

If the data gets updated by some other program, you could use a timer control to refresh the report every 5 minutes (or whatever time frame you would like.)

If the data gets updated by your program, then just put the code that loads the report in a method and call that method every time the user updates the data.

hi thanks for your reply,

I The data is inside the program so I tried what you said but still the data will not update unless you close the program and open it back up. There has to be some othere way to update data to the reportviewer during run time.

[tbijujoseph]

Write a CancelEventHandler for ReportRefresh and get the new data in the event handler.

-- declare eventhandler
public event CancelEventHandler ReportRefresh;

... Add event handler
this.reportViewer1.ReportRefresh += new CancelEventHandler(reportViewer1_ReportRefresh);

...implement event handler
private void reportViewer1_ReportRefresh(object sender, CancelEventArgs e)
{
//update your report data source here
reportDataSource.Value = updated dataset;
}

Biju,
MCPD

[yousamook]

Write a CancelEventHandler for ReportRefresh and get the new data in the event handler.

-- declare eventhandler
public event CancelEventHandler ReportRefresh;

... Add event handler
this.reportViewer1.ReportRefresh += new CancelEventHandler(reportViewer1_ReportRefresh);

...implement event handler
private void reportViewer1_ReportRefresh(object sender, CancelEventArgs e)
{
//update your report data source here
reportDataSource.Value = updated dataset;
}

Biju,
MCPD

Hi Biju,

Thanks for your prompt reply, Im somewhat of a newbie but it seems that your example is C#. Although the launguages seem very similar I am a newbie and do need some explanation with the pseudo code you put there. Can you please elaborate on your example of the code a bit more.

Thanks,

Larry

[tbijujoseph]

Sorry I don't know much of VB.NET syntax and therefore, my code is in C#.

What you need to do is to get the new data for the report in the refresh event handler.

Hope the below sample will help you...

using System.Text;
using System.Windows.Forms;
using Microsoft.Reporting.WinForms;


namespace RDLC
{
    public partial class Form1 : Form
    {
        public event CancelEventHandler ReportRefresh;
        ReportDataSource reportDataSource = new ReportDataSource();

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'DataSet1.Supplier'   table. You can move, or remove it, as needed.
            this.SupplierTableAdapter.Fill(this.DataSet1.Supplier);
            reportDataSource.Name = "DataSet1_Supplier";
            reportDataSource.Value = this.DataSet1.Supplier;
            this.reportViewer1.LocalReport.DataSources.Clear();
            this.reportViewer1.ReportRefresh += new CancelEventHandler(reportViewer1_ReportRefresh);
            this.reportViewer1.LocalReport.DataSources.Add(reportDataSource);
            this.reportViewer1.RefreshReport();
        }

        private void reportViewer1_ReportRefresh(object sender, CancelEventArgs e)
        {
            this.SupplierTableAdapter.Fill(this.DataSet1.Supplier);
            reportDataSource.Value = this.DataSet1.Supplier;
        }
    }
}

Points:

1. Initialise the report in the Form1_Load Event,

2. write you codet to fetch the lastest data in "reportViewer1_ReportRefresh method and assign "the new data" to reportDataSource.Value property.

3. Declare an Event in the Form level

Hope this helps..
Biju

[pixma]

i have already done that before..what i did is made a Refresh button..wherein the only code inside is the string connection..meaning you'll be reconnecting to the database and that way you'll get the most updated information from the database