How to add WHERE Clause to Table Adapter @ Run Time

2010-08-18

Question

_{Wednesday, August 18, 2010 10:05 AM}

I am using VB 2008 and Access Database.

Also using Typed DataSet.

My Question is how do i add Where Clause to TableAdapter at Run Time

I want to allow User to select Fields tobe filtered and values they should have.

For this my Select query is common but no. of records it will show will depend on User selected criteria.

Thanks in advance

SANDEEP

All replies (12)

_{Thursday, August 19, 2010 10:05 PM ✅Answered | 3 votes}

Couple different options here,

If your form has a binding source then do this:

PurchasesBindingSource.Filter = "SupplierId = " & cboSupplier2Filter.SelectedValue

And datagrid should update automatically.

If no binding source then:

dsPurchases.Purchases.DefaultView.RowFilter = "SupplierId = " & cboSupplier2Filter.SelectedValue

Datagrid.Datasource = dsPurchases.Purchases

Karl ,
Please 'Mark as Answer' if we helped.
This helps others who have the same problem!

_{Wednesday, August 18, 2010 10:24 AM}

Go to your Solution Explorer.

Open the dataset (the xsd)\

Right click on that and do add => querry

Go to the wizard and add to the SQL transact code which you see for instance

....Where ID = ?

You can use this for instance in your program like

    Dim da As New Database11DataSetTableAdapters.TestTableAdapter
    Dim dt As Database11DataSet.TestRow = da.GetData(Cint(TextBox.text))

Be aware that the this is a sample, it will be seldom that the user types a correct value in the textboxSuccess
Cor

_{Wednesday, August 18, 2010 9:08 PM}

You could add a filter based on the selection that your customers select.

e.g.

dt.DefaultView.RowFilter = “Field=Value”

where dt is your datatable. You can change this value at run-time and can use most WHERE Clause Type filters such as > < Like = <> AND OR ect...

Karl ,
Please 'Mark as Answer' if we helped.
This helps others who have the same problem!

_{Thursday, August 19, 2010 10:58 AM}

I want to Set the Clause At Run Time Only.

_{Thursday, August 19, 2010 2:30 PM}

yes,

dt.DefaultView.RowFilter = “Field=Value”

this works ar runtime. you can set the .RowFilter property back to "" to remove filter.

Karl ,
Please 'Mark as Answer' if we helped.
This helps others who have the same problem!

_{Thursday, August 19, 2010 6:05 PM}

Hello Karl!

as you said i have set following statement

dsPurchases.Purchases.DefaultView.RowFilter = "SupplierId = " & cboSupplier2Filter.SelectedValue

now that how would i get the results in visual

that i am actually using DataBound DataGridView Control and i want that filterd records to appear in the Grid.

_{Friday, August 20, 2010 6:11 AM}

Do you mean set the SQL transact code on Run Time, I gave you the method to set the Were clause at Run Time.

To set the SQL transact code in a Table Adapter

    Dim da As New NorthwindDataSetTableAdapters.EmployeesTableAdapter
    da.Adapter.SelectCommand.CommandText = "Select Employee from Employees where ID = 1"

This can also be done with parameters.

Be aware that using a rowfilter on a dataview (defaultview) is not the same as using a select clause

With a rowfilter you consume first the complete datatable.

Success
Cor

_{Saturday, August 21, 2010 12:52 PM}

Hello Cor

The second statement is giving following error

Error 1 'YarnTrading.DSetPurchaseSaleTableAdapters.taPurchaseSauda.Private ReadOnly Property Adapter() As System.Data.OleDb.OleDbDataAdapter' is not accessible in this context because it is 'Private'. D:\My Document\Visual Studio 2008\Projects\YarnTrading\YarnTrading\PurchaseSaudaForm.vb 282 9 YarnTrading

_{Saturday, August 21, 2010 3:52 PM}

Hello Sandeep,

I've always trouble with the tableadapters because as soon as you do something that was not yet thought about you are in problem.

I tried this toe, created therefore this code in Visual Studio, but now I see that is not even enough for the table adapter.

Maybe I can find a solution,

But not anymore today and probably as well not tomorrow.

Sorry

Success
Cor

_{Monday, August 23, 2010 3:22 PM | 1 vote}

here is how to change query at runtime for a table adapter

'Add this in your form code outside the form class
Namespace dsPurchasesTableAdapters

  Partial Public Class PurchasesTableAdapter
    Public Property SelectCommand() As SqlClient.SqlCommand()
      Get
        If (Me._commandCollection Is Nothing) Then
          Me.InitCommandCollection()
        End If
        Return Me._commandCollection
      End Get

      Set(ByVal value As SqlClient.SqlCommand())
        Me._commandCollection = value
      End Set

    End Property
  End Class

End Namespace

'add this in your form class where you want to change select command
ta.SelectCommand(0).CommandText = "Select...."
ts.fill(...)

Don’t know if I got the Namespace dsPurchasesTableAdapters or then TableAdapter name PurchasesTableAdapter is correct, just guessing from the code posted so far. Nevertheless I think you can work out what they are supposed to be. ‘ta’ is the instance name of the table adapter defined.

Karl ,
Please 'Mark as Answer' if we helped.
This helps others who have the same problem!

_{Thursday, September 23, 2010 2:01 PM}

I think this article can help

http://www.codeproject.com/KB/database/TableAdapter.aspx

_{Tuesday, March 24, 2015 10:51 PM}

Absolutely brilliant! I have literally been looking all day for how to filter my database - I tried using queries and everything! Thanks for this!!

שתף באמצעות

How to add WHERE Clause to Table Adapter @ Run Time

Question

All replies (12)

משאבים נוספים