how to show a table based on 2 parameters in report viewer in vb.net

Question

i have a report where i need to show a view from my database based on 2 parameters (1 combo box and 1 variable) from a Form.

Here is the image of my View (view_population).

Here is the code i used to fill the table for the Reports.

 Me.view_populationTableAdapter.Fill(Me.phil_vetDataSet.view_population)

I need to filter the view based on the Year (combo box) and the Category (varCategory) from a Form (frmReport) but how do I use the 2 parameters to filter the table of the Report?

I used this code to do it but i don't know how to use it properly.

Dim varCategory As String = ""
If frmReport.cbBackyard.Checked = True Then
    varCategory = "Backyard"
ElseIf frmReport.cbCommercial.Checked = True Then
    varCategory = "Commercial"
Else
    varCategory = ""
End If

Dim Year As New ReportParameter("for_year", frmReport.cboYear.Text)
ReportViewer1.LocalReport.SetParameters(Year)

I need this output in the Report but i don't know how to put the sql statement in the Form to filter the view.

Any suggestion and comments are greatly appreciated. Thank you

Answer 1

if you want to filter report at runtime you can do something like that.

Dim con As New OleDbConnection
    con.ConnectionString = connection_string
    con.Open()


    Dim cmd As New OleDbCommand("<Query>", con)

    cmd.CommandType = CommandType.Text

    Dim ds As New DataSet
    Dim da As New OleDbDataAdapter(cmd)

    da.Fill(ds)

    Dim rpt As New <ReportName>
    rpt.SetDataSource(ds.Tables(0))



    <TableName>.CrystalReportViewer1.ReportSource = rpt
    cmd.ExecuteNonQuery()
    con.Close()

This code is just to give you an idea how u will do it. for the parameters you just need to add objects in the query and it will filter the record for you in report.