HomeActive Query BuilderApplying Filter in VB.net 2008

Applying Filter in VB.net 2008


I have a question concerning programmatically adding filters. In my situation, I am bringing in a FoxPro database and everything seems to work fine. I was recently told that I need to lock down the tables so that I can only see 5 out of 175 tables. Therefore, I wrote a routine that would do this, it is pasted below. If I add the filters user the designer everything works great, but when using the code below nothing happens. I check the count of the filter collection and it shows 175 or 172 (depending on the database I open). What am I missing, seems to need like a metafilter.apply method or something. Any help you could give me would be appreciated. Thanks.

Private Sub ComboClient_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboClient.SelectedIndexChanged

If Not MetaDataProviderClosed Then
Dim COATSConnectionString As String = ""
Dim FilterTables As String = ""

QueryBuilder1.Clear()

UltraGrid1.DataSource = Nothing
QueryBuilder1.MetadataProvider = Nothing

Export.Enabled = False

COATSConnectionString = "Provider=VFPOLEDB.1;Data Source=" & ComboClient.SelectedValue

Dim OLEConnection As New OleDb.OleDbConnection(COATSConnectionString)

OLEConnection.Open()

Dim FPTableDataTable As DataTable = OLEConnection.GetSchema("tables")

For Each AllowedTableName As String In My.Settings.AllowedTables
FilterTables &= "TABLE_NAME <> '" & LCase(AllowedTableName) & "' OR "
Next

FilterTables = FilterTables.Remove(Len(FilterTables) - 4)

Dim Filter As New MetadataFilterItem
Dim counter As Integer

For Each FPTableRow As DataRow In FPTableDataTable.Select(FilterTables)
Debug.Print(FPTableRow("TABLE_NAME").ToString)

Filter.ApplyFor = CType((((ActiveDatabaseSoftware.ActiveQueryBuilder.MetadataFilterApplyFor.Tables Or ActiveDatabaseSoftware.ActiveQueryBuilder.MetadataFilterApplyFor.Views) _
Or ActiveDatabaseSoftware.ActiveQueryBuilder.MetadataFilterApplyFor.Procedures) _
Or ActiveDatabaseSoftware.ActiveQueryBuilder.MetadataFilterApplyFor.Synonyms), ActiveDatabaseSoftware.ActiveQueryBuilder.MetadataFilterApplyFor)

Filter.ObjectMask = FPTableRow("TABLE_NAME").ToString
Filter.SchemaMask = "%"
Filter.Exclude = True

QueryBuilder1.MetadataFilter.Add(a)

Next

OledbMetadataProvider1.Connection = OLEConnection

QueryBuilder1.MetadataProvider = OledbMetadataProvider1
QueryBuilder1.SyntaxProvider = UniversalSyntaxProvider1

QueryBuilder1.RefreshMetadata()
End If
End Sub

Dustin Walls | email
July 2 2008, 12:09 PM
Dear Dustin,

Thank you for your bug report. We will check this behavior.

But, if you need to show only 5 of 175 objects, why not to use Inclusion, not Exclusion filters? You may add only 5 inclusion filter items by setting the Exclude property to False:

Filter.Exclude = False

Another way to do what you want is to work with Metadata Container directly. The QueryBuilder.MetadataContainer is a collection of database objects. You may remove unwanted objects from the container, or even fill the container manually:

For Each AllowedTableName As String In My.Settings.AllowedTables
QueryBuilder.MetadataContainer.AddTable("", AllowedTableName, False, "")
' fisrt parameter is a schema name, fourth is a database name
' third parameter is a flag for system objects
Next
' do not call the RefreshMetadata method in this case

Hope this will help you to cope with your task.

Sincerely
Sergey Smagin
Sergey Smagin | email
13 hours, 27 mins since original post
Thank you for your quick response. I will use the metadata.container method you described.

By the way my previous situation was not a bug on your part but poor programming on mine.

The line Dim Filter As New MetadataFilterItem should not be where it is at, instead it should be within the loop. Like Filter = New MetadataFilterItem, I think it was changing the first filter over and over again.

Thanks
~Dustin
Dustin Walls | email
22 hours, 32 mins since original post
This topic is closed