Filter DBGrid or Table Builder 6

Question

In this form I am using Table1 (three columns “ZEMLJA", "KATEGORIJA" and "NAZIV”) with DataSource1 connected on paradox 7 database. Also DBGrid1 is connected to the DataSource1. Here is also three Edit box (Edit1, Edit2 and Edit3).

In header file I put:

public: // User declarations    
__fastcall TForm4(TComponent* Owner);    
void Filtriraj(AnsiString szZemljaAsterix, AnsiString szKategorijaAsterix, AnsiString szNazivAsterix, AnsiString szNoviAsterix);    

My cpp file looks like this:

#include <vcl.h>
#pragma hdrstop

#include “Unit4.h”
//—————————————————————————
#pragma package(smart_init)
#pragma resource “*.dfm”
TForm4 *Form4;
//—————————————————————————
__fastcall TForm4::TForm4(TComponent* Owner): TForm(Owner)
{
}
//—————————————————————————
void TForm4::Filtriraj(AnsiString szZemljaAsterix, AnsiString szKategorijaAsterix, AnsiString szNazivAsterix, AnsiString szNoviAsterix)
{
    AnsiString szUslov=””;

    if( !Edit3->Text.IsEmpty() )
    {
        szUslov = “ZEMLJA = ‘” + Edit3->Text + szZemljaAsterix + “‘”;
    }


    if( (!Edit4->Text.IsEmpty()) && (!Edit3->Text.IsEmpty()) )
    {
        szUslov = szUslov + “and KATEGORIJA = ‘” + Edit4->Text + szKategorijaAsterix + “‘”;
    }
    else if( (!Edit4->Text.IsEmpty()) && (Edit3->Text.IsEmpty()) )
    {
        szUslov = “KATEGORIJA = ‘” + Edit4->Text + szKategorijaAsterix +”‘”;
    }

    if( !Edit5->Text.IsEmpty() )
    {
        if(szUslov!=””)
            szUslov = szUslov + “and NAZIV = ‘” + Edit5->Text + szNazivAsterix + “‘”;
        else
            szUslov = szUslov + “NAZIV = ‘” + Edit5->Text + szNazivAsterix + “‘”;
    }

    Table3->Filter = szUslov;
    Table3->Filtered = true;
}
//—————————————————————————
void __fastcall TForm4::Edit3Change(TObject *Sender)
{
    Filtriraj(“*”,””,””,””);
}
//—————————————————————————
void __fastcall TForm4::Edit4Change(TObject *Sender)
{
    Filtriraj(“”,”*”,””,””);
}
//—————————————————————————
void __fastcall TForm4::Edit5Change(TObject *Sender)
{
    Filtriraj(“”,””,”*”,””);
}
//—————————————————————————
void __fastcall TForm4::Edit3KeyPress(TObject *Sender, char &Key)
{
    if( Key==13 )
    {
        Edit4->SetFocus();
        if(Edit3->Text==””)
            Edit3->Text =””;
        else
            Edit3->Text = Edit3->Text + “*”;
        Filtriraj(“”,””,””,””);
    }
}
//—————————————————————————
void __fastcall TForm4::Edit4KeyPress(TObject *Sender, char &Key)
{
    if( Key==13 )
    {
        Edit5->SetFocus();
        if(Edit4->Text==””)
            Edit4->Text =””;
        else
            Edit4->Text = Edit4->Text + “*”;
        Filtriraj(“”,””,””,””);
    }
}
//—————————————————————————
void __fastcall TForm4::Edit5KeyPress(TObject *Sender, char &Key)
{
    if( Key==13 )
    {
        DBGrid1->SetFocus();
        if(Edit5->Text==””)
            Edit5->Text =””;
        else
            Edit5->Text = Edit5->Text + “*”;

        Filtriraj(“”,””,””,””);
    }
}    
//—————————————————————————    

This code works great, each edit box filtering DBGrid correcly and also if I wanna put just a part of word which a want it works perfectly, but this filter works from the begining of the cell in DBGrid.

I wanna make a filter which one will find word everywhere in cell. For examle I have a lot of rows that means a lot of cells, in one cell is written “I WILL GO TO THE SCHOOL TOORRROW”, when I type in Edit box SCHOOL it should find me word SCHOOL in my cell and show me that in some way doesn’t matter how (row selector or filter I dont care).

If someone knows easier way to solve this problem.

Answer 1

Try something more like this:

public: // User declarations    
    __fastcall TForm4(TComponent* Owner);    
    void Filtriraj();    

#include <vcl.h>
#pragma hdrstop

#include “Unit4.h”
#include <StrUtils.hpp>
//-------------------------
#pragma package(smart_init)
#pragma resource “*.dfm”
TForm4 *Form4;
//-------------------------
__fastcall TForm4::TForm4(TComponent* Owner)
    : TForm(Owner)
{
}
//-------------------------
String AddWildcardsAndQuotes(String S)
{
    if( !S.IsEmpty() )
    {
        if( !AnsiStartsText("*", S) )
            S = ("*" + S);
        if( !AnsiEndsText("*", S) )
            S += "*";
    }
    return QuotedStr(S);
}
//-------------------------
void TForm4::Filtriraj()
{
    String szUslov;

    if( Edit3->GetTextLen() > 0 )
    {
        szUslov = ("ZEMLJA = " + AddWildcardsAndQuotes(Edit3->Text));
    }

    if( Edit4->GetTextLen() > 0 )
    {
        if( !szUslov.IsEmpty() )
            SzUslov += " and ";
        szUslov += ("KATEGORIJA = " + AddWildcardsAndQuotes(Edit4->Text));
    }

    if( Edit5->GetTextLen() > 0 )
    {
        if( !szUslov.IsEmpty() )
            szUslov += " and ";
        szUslov += ("NAZIV = " + AddWildcardsAndQuotes(Edit5->Text));
    }

    Table3->Filter = szUslov;
    Table3->Filtered = !szUslov.IsEmpty();
}
//-------------------------
// common OnChange handler assigned to Edit3, Edit4, and Edit5
void __fastcall TForm4::EditChange(TObject *Sender)
{
    Filtriraj();
}
//-------------------------
// common OnKeyPress handler assigned to Edit3, Edit4, and Edit5
void __fastcall TForm4::EditKeyPress(TObject *Sender, char &Key)
{
    if( Key == VK_RETURN )
    {
        Key = 0;

        // make sure each control's TabStop is true and its TabOrder
        // is logical, eg: Edit4->TabOrder is Edit3->TabOrder+1,
        // Edit5->TabOrder is Edit4->TabOrder+1, and
        // DBGrid1->TabOrder is Edit5-TabOrder+1
        //
        this->SelectNext(static_cast<TEdit*>(Sender), true, true);

        Filtriraj();
    }
}
//-------------------------  

Update: alternatively:

String AddWildcardsAndQuotes(String S)
{
    if( !S.IsEmpty() )
    {
        S = StringReplace(S, "*", "%", TReplaceFlags() << rfReplaceAll);
        S = StringReplace(S, "?", "_", TReplaceFlags() << rfReplaceAll);
        if( !AnsiStartsText("%", S) )
            S = ("%" + S);
        if( !AnsiEndsText("%", S) )
            S += "%";
    }
    return QuotedStr(S);
}
//-------------------------
void TForm4::Filtriraj()
{
    String szUslov;

    if( Edit3->GetTextLen() > 0 )
    {
        szUslov = ("ZEMLJA LIKE " + AddWildcardsAndQuotes(Edit3->Text));
    }

    if( Edit4->GetTextLen() > 0 )
    {
        if( !szUslov.IsEmpty() )
            SzUslov += " and ";
        szUslov += ("KATEGORIJA LIKE " + AddWildcardsAndQuotes(Edit4->Text));
    }

    if( Edit5->GetTextLen() > 0 )
    {
        if( !szUslov.IsEmpty() )
            szUslov += " and ";
        szUslov += ("NAZIV LIKE " + AddWildcardsAndQuotes(Edit5->Text));
    }

    Table3->Filter = szUslov;
    Table3->Filtered = !szUslov.IsEmpty();
}

Answer 2

AnsiString query;
Query1->Close();
Query1->UnPrepare();
Query1->SQL->Clear();
query="SELECT NAZIV FROM \"C:\\Users\\work\\Desktop\\New folder\\publiks.DB\" Publiks     WHERE NAZIV LIKE'%" +Edit1->Text+ "%' ORDER BY NAZIV";
Query1->SQL->Add(query);
Query1->Prepare();
Query1->Open();

I solved for one Editbox and column from Table using Query it works that does it means paradox support LIKE