how to Query a DataTable

[VIPER5646]

Hi all
I created a RDCL in the designer.
I would like the User to Determine what is shown in the Report.
How can I change the query at runtime? (ex. changing the customer Name)

This is the query that I have in the properties
SELECT [Date], Address, Customer, Orders
FROM Report_qry
WHERE (Customer= 'JohnDoe')

Thanks

[sgt_toasty]

I usually have something the users can interact with (comboboxes, textboxes, etc) and then you can add on to the query based on the user's selected/inputted items.

So for example if you had two textboxes, one for minimum ordered quantity, one for address and the user input min order qty of 5 and address of 123 Fake Street then you could build your query in a string that starts with what you posted above, and then expand your where clause to be:

Where (customer ='JohnDoe' AND Min_Order_Qty >= 5 AND Address = '123 Fake Street').

Of course you can have whatever parameters are relevent (such as customer name as you mentioned), this is just an example.

Hope this helps!

[VIPER5646]

Thanks sgt_tosty for your reply.
Yes I have a text box that the user will type in the customer's name.
But my problem in not how to query.
My difficulty is the data table has been created in the Designer which now seams to be the same results every time I run the Report.
I need to find out is how can I replace the query that I created in the designer with one that I would like to creat in code.

[Mitja Bonca]

You have one main dataTable with all the data. When you want to filter it, create new DataTable, create same column as main one has (by using Clone method):

Dim temp As New DataTable()
temp = mainTable.Clone()
'this was you get the table structure - same as main one

Now use Select property to filter data.
my Example:

Private table As DataTable
Private Sub MethodToFilter()
	table = New DataTable("MainTable")
	table.Columns.Add("Id", GetType(Integer))
	table.Columns.Add("Value", GetType(String))

	'adding some example rows (10):
	For i As Integer = 0 To 9
		table.Rows.Add(i, "item " & i)
	Next

	'now filter:
	'get all rows that Id is higher then 6 (so 3 rows will be added to new dataTable):
	Dim filterDT As DataTable = table.Clone()
	Dim rows As DataRow() = table.[Select]("Id > 6")
	For Each row As DataRow In rows
		filterDT.ImportRow(row)
	Next

	'now use filterDT as your new filtered dataTable...
End Sub

[VIPER5646]

Thanks Mitja for your reply.
Now after the Datatable being filtere how can we go about adding it to the .RDLC

Where is what I being trying but does not work
The datagrid has data but not the reportviewer

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        GetData()
        Me.DataGridView1.DataSource = GetData()
        With Me.ReportViewer1.LocalReport
            .ReportPath = "Report1" & ".rdlc"
            .DataSources.Clear()
        End With
        Dim rptDataSource As ReportDataSource
        rptDataSource = New ReportDataSource("Report1", GetData)
        Me.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
    End Sub

    Private Function GetData() As DataTable
        Dim mdt As New DataTable("reports")
        Dim m_dr As OleDbDataReader
        Dim mycon As New Connection
        mycon.FilePath = "C:\Report\Report.txt"
        mycon.connect()
        Dim query As String = "Select * from Report_qry WHERE [Address]=@address "
        Dim cmd As New OleDb.OleDbCommand(query, mycon.con)
        cmd.CommandType = CommandType.Text
        cmd.CommandText = query
        With cmd
            .Parameters.AddWithValue("@address", TXTAD)
        End With
        mycon.con.Open()
        m_dr = cmd.ExecuteReader
        mdt.Load(m_dr)
        Dim dataset1 As New DataSet
        dataset1.Tables.Add(mdt)
        Return mdt
    End Function
End Class

[VIPER5646]

Thank you ALL

I have solved by using the following QUERY in the TableAdapter.

SELECT        [Date], Address, First Name, Orders
FROM            Report_qry
WHERE        (Address = ?)