Hi,

is there a possibility to filter fields with LIKE ignoring case? I think of a query like this:

SELECT xx
FROM table1
WHERE LOWER(xx) LIKE LOWER('%test%');

In ORACLE this is the only possibility to get data case-insensitive with LIKE; to my knowledge MS SQL-server ignores case per default in LIKE-conditions and therefore this problem does'nt exist there ...

This is not possible at the moment.

If you want to do this, create a similar class as the ORM Support classes' clas FieldLikePredicate by copying that code in your own project and modifying the ToQueryText method. Give it a new name and compile your project. Instead of using a FieldLikePredicate in your code, you now use your own FieldLikePredicate which emits a different query text.

The soucecode for the ORM SUpport classes can be downloaded from the customer area.

OK, I'll try this, but: I loose the comfort of the generated PredicateFactory (PredicateFactory.Like(...)), to be able to use this 'Custom'-Predicate generally.

Also some attributes of Predicate (e.g. _selfServicing) are declared internal, so I can't use them, but I can work around this ...

Yes, that might be a problem, however if you declare it as public it should work. Basicly these flags are for error throwing, so if you're using Adapter, just return false.

You have to create a custom template then (copy original template, make adjustments, create copy of template set config, change binding of template to your file, use that copy of the config to generate code).

With the new include templates coming up (now in beta) you will be able to add the new predicate factory code through the binding of a custom template, without altering existing templates, so you don't loose a lot of work.

Hi, I changed the template predicateFactoryAdapter.template and implemented a new class FieldLikeIgnoreCasePredicate (subclass of FieldLikePredicate - see below). Every time I try to use this filter a get a System.NullReferenceException:|

_ Message: Object reference not set to an instance of an object. Source: SD.LLBLGen.Pro.DQE.Oracle.NET11 at SD.LLBLGen.Pro.DQE.Oracle.OracleSpecificCreator.CreateFieldName(IFieldPersistenceInfo persistenceInfo, String fieldName, Boolean appendAlias) at SD.LLBLGen.Pro.DQE.Oracle.OracleSpecificCreator.CreateFieldName(IFieldPersistenceInfo persistenceInfo, String fieldName) at ORF.SWD.AKMNeu.Utility.FieldLikeIgnoreCasePredicate.ToQueryText(Int32& uniqueMarker) in C:\dev\ORF\SWD\AKMNeu\Utility\FieldLikeIgnoreCasePredicate.cs:line 127 at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker) at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker) at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates) at ORF.SWD.AKMNeu.DataAccess.DatabaseSpecific.DataAccessAdapter.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates) in C:\dev\ORF\SWD\AKMNeu\DatabaseSpecific\DataAccessAdapter.cs:line 393 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn) at ORF.SWD.AKMNeu.DataAccess.SearchSendeterminDAO.search(SearchSendetermin p_searchCriteria) in c:\dev\orf\swd\akmneu\dataaccess\searchsendetermindao.cs:line 108 at ORF.SWD.AKMNeu.DataAccess.Test.SearchSendeterminDAOTest.search() in c:\dev\orf\swd\akmneu\dataaccess.test\searchsendetermindaotest.cs:line 51 _

The way I use the filter is:

RelationPredicateBucket b = new RelationPredicateBucket();
IPredicateExpression expA = new PredicateExpression();
expA.Add(PredicateFactory.LikeIgnoreCase(SendejournalFieldIndex.Sendungstitel, p_searchCriteria.Titel.Value));
b.PredicateExpression.Add(expA);

SendejournalEntity sl = new SendejournalEntity();
EntityCollection sendetermine = new EntityCollection(new SendeterminFactory());
DataAccessAdapter adapter = new DataAccessAdapter(AKMConfig.ConnectionString);

adapter.FetchEntityCollection(sendetermine, b, 100);

When executing the fetch, the exception pops up ...

Class FieldLikeIgnoreCasePredicate:

/////////////////////////////////////////////////////////////
// LLBLGen Pro ORM Support Classes Library
// (c) 2002-2003 Solutions Design. All rights reserved.
// http://www.llblgen.com
// http://www.sd.nl/llblgen
// 
// THIS IS NOT OPEN SOURCE SOFTWARE OF ANY KIND.
// SOURCECODE DISTRIBUTION IS NOT ALLOWED IN ANY WAY.
/////////////////////////////////////////////////////////////
using System;
using System.Text;
using System.Data;

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace ORF.SWD.AKMNeu.Utility
{
    /// <summary>
    /// Implementation of a LIKE predicate expression which ignores case, using the following formats:
    /// IEntityField(Core) LIKE Parameter (f.e. Foo LIKE @Foo )
    /// A specified pattern will be set as the parameters value.
    /// </summary>
    /// <remarks>The DB-query will be UPPER(IEntityField(Core)) LIKE UPPER(Parameter);
    /// class is a subclass of FieldLikePredicate to use these two interchangeably and to guarantee
    /// compliance with the 'surrounding' classes of the framework</remarks>
    [Serializable]
    public class FieldLikeIgnoreCasePredicate : FieldLikePredicate
    {
        private IEntityFieldCore        _field;
        private IFieldPersistenceInfo   _persistenceInfo;
        private string                  _pattern;
        private bool                    _selfServicing;


        
        /// <summary>
        /// CTor
        /// </summary>
        public FieldLikeIgnoreCasePredicate() : base()
        {
            InitClass(null, null, string.Empty, false, true);
        }


        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        public FieldLikeIgnoreCasePredicate(IEntityField field, string pattern) : base(field, field, pattern, false)
        {
            InitClass(field, field, pattern, false, true);
        }


        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        /// <param name="negate">Flag to make this expression add NOT to itself</param>
        public FieldLikeIgnoreCasePredicate(IEntityField field, string pattern, bool negate) : base(field, field, pattern, negate)
        {
            InitClass(field, field, pattern, negate, true);
        }


        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="persistenceInfo">The persistence info object for the field</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        public FieldLikeIgnoreCasePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern) : base(field, persistenceInfo, pattern, false)
        {
            InitClass(field, persistenceInfo, pattern, false, false);
        }


        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="persistenceInfo">The persistence info object for the field</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        /// <param name="negate">Flag to make this expression add NOT to itself</param>
        public FieldLikeIgnoreCasePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern, bool negate) : base(field, persistenceInfo, pattern, negate)
        {
            InitClass(field, persistenceInfo, pattern, negate, false);
        }


        
        /// <summary>
        /// Implements the IPredicate ToQueryText method. Retrieves a ready to use text representation of the contained Predicate.
        /// </summary>
        /// <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
        /// making sure the parameter is unique in the predicate and also in the predicate expression(s) containing the predicate.</param>
        /// <returns>The contained Predicate in textual format.</returns>
        /// <exception cref="System.ApplicationException">When IPredicate.DatabaseSpecificCreator is not set to a valid value.</exception>
        public override string ToQueryText(ref int uniqueMarker)
        {
            if(_field==null)
            {
                return "";
            }

            if(base.DatabaseSpecificCreator==null)
            {
                throw new System.ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.");
            }

            base.Parameters.Clear();

            StringBuilder queryText = new StringBuilder(64);
            
            if(base.Negate)
            {
                queryText.Append("NOT ");
            }

            // create parameter 
            uniqueMarker++;
            IDataParameter parameter = base.DatabaseSpecificCreator.CreateLikeParameter(String.Format("{0}{1}", _field.Name, uniqueMarker), _pattern);
            base.Parameters.Add(parameter);

            queryText.AppendFormat("UPPER({0}) LIKE UPPER({1})", base.DatabaseSpecificCreator.CreateFieldName(_persistenceInfo, _field.Name), parameter.ParameterName);
            return queryText.ToString();
        }

    
        /// <summary>
        /// Field used in the comparison expression (SelfServicing).
        /// </summary>
        /// <exception cref="InvalidOperationException">if this object was constructed using a non-selfservicing constructor.</exception>
        public override IEntityField Field
        {
            get 
            { 
                if(!SelfServicing)
                {
                    // not applicable
                    throw new InvalidOperationException("This object was constructed using a non-selfservicing constructor. Can't retrieve an IEntityField after that.");
                }
                return (IEntityField)_field; 
            }
        }

        /// <summary>
        /// Field used in the comparison expression (IEntityFieldCore).
        /// </summary>
        public override IEntityFieldCore FieldCore
        {
            get 
            { 
                return _field; 
            }
        }

        /// <summary>
        /// Gets / sets persistenceInfo for field
        /// </summary>
        /// <exception cref="InvalidOperationException">When a value is set for this property and this object was created using a selfservicing constructor.</exception>
        public new IFieldPersistenceInfo PersistenceInfo
        {
            get
            {
                return _persistenceInfo;
            }
            set
            {
                if(SelfServicing)
                {
                    // not applicable
                    throw new InvalidOperationException("This object was constructed using a selfservicing constructor. Can't set persistence info after that.");
                }
                _persistenceInfo = value;
            }
        }
        
        /// <summary>
        /// Gets / sets the pattern to use in a Field LIKE Pattern clause. 
        /// </summary>
        public override string Pattern
        {
            get { return _pattern; }
            set 
            { 
                _pattern = value; 
            }
        }
        
        /// <summary>
        /// Gets / sets selfServicing, a flag to signal if this predicate is constructed using a selfservicing constructor (field is of type IEntityField)
        /// Default: true;
        /// </summary>
        internal bool SelfServicing
        {
            get
            {
                return _selfServicing;
            }
            set
            {
                _selfServicing = value;
            }
        }

    
        /// <summary>
        /// Initializes the class
        /// </summary>
        /// <param name="field"></param>
        /// <param name="persistenceInfo"></param>
        /// <param name="pattern"></param>
        /// <param name="negate"></param>
        /// <param name="selfServicing"></param>
        private void InitClass(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern, bool negate, bool selfServicing)
        {
            _field = field;
            _persistenceInfo = persistenceInfo;
            _pattern = pattern;
            base.Negate=negate;
            SelfServicing = selfServicing;
            base.InstanceType = (int)PredicateType.FieldLikePredicate;
        }
    }
}

What's the problem?

You shouldn't produce a subclass, but a new class just copy the code over alter the name, and add the LOWER () statement to the field name in the ToQueryText method.

I think that's the easiest way to do it.

NOPE, does'nt work. I get ClassCastExceptions:

_ Exception: System.InvalidCastException Message: Specified cast is not valid. Source: ORF.SWD.AKMNeu.DataAccess.DatabaseSpecific at ORF.SWD.AKMNeu.DataAccess.DatabaseSpecific.DataAccessAdapter.InsertPersistenceInfoObjects(IPredicateExpression expression) in C:\dev\ORF\SWD\AKMNeu\DatabaseSpecific\DataAccessAdapter.cs:line 273 at ORF.SWD.AKMNeu.DataAccess.DatabaseSpecific.DataAccessAdapter.InsertPersistenceInfoObjects(IPredicateExpression expression) in C:\dev\ORF\SWD\AKMNeu\DatabaseSpecific\DataAccessAdapter.cs:line 249 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.InterpretFilterBucket(IRelationPredicateBucket filterBucket, Boolean& relationsPresent, IPredicateExpression& expressionToPass) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn) at ORF.SWD.AKMNeu.DataAccess.SearchSendeterminDAO.search(SearchSendetermin p_searchCriteria) in c:\dev\orf\swd\akmneu\dataaccess\searchsendetermindao.cs:line 108 at ORF.SWD.AKMNeu.DataAccess.Test.SearchSendeterminDAOTest.search() in c:\dev\orf\swd\akmneu\dataaccess.test\searchsendetermindaotest.cs:line 51 _

If I don't subclass from FieldLikePredicate nobody else in the framework knows about this class (e.g. no PredicateType for FieldLikeIgnoreCasePredicate etc.); I suspect I would have to modify some other templates too to get it to work this way.

What's wrong with subclassing from FieldLikePredicate? Why does this not work? I mean, that's what inheritance is for, isn't it?

Ok, I see the point now. Stay tuned I'll fix the class you posted.

(edit: forgot the class inheritance statement)

/////////////////////////////////////////////////////////////
// LLBLGen Pro ORM Support Classes Library
// (c) 2002-2003 Solutions Design. All rights reserved.
//
//
//
// THIS IS NOT OPEN SOURCE SOFTWARE OF ANY KIND.
// SOURCECODE DISTRIBUTION IS NOT ALLOWED IN ANY WAY.
/////////////////////////////////////////////////////////////
using System;
using System.Text;
using System.Data;

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace ORF.SWD.AKMNeu.Utility
{
    /// <summary>
    /// Implementation of a LIKE predicate expression which ignores case, using the following formats:
    /// IEntityField(Core) LIKE Parameter (f.e. Foo LIKE @Foo )
    /// A specified pattern will be set as the parameters value.
    /// </summary>
    /// <remarks>The DB-query will be UPPER(IEntityField(Core)) LIKE UPPER(Parameter);
    /// class is a subclass of FieldLikePredicate to use these two interchangeably and to guarantee
    /// compliance with the 'surrounding' classes of the framework</remarks>
    [Serializable]
    public class FieldLikeIgnoreCasePredicate : FieldLikePredicate
    {
    
        /// <summary>
        /// CTor
        /// </summary>
        public FieldLikeIgnoreCasePredicate() : base()
        {
        }


        /// <summary>
        /// CTor for Field LIKE Pattern.
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        public FieldLikeIgnoreCasePredicate(IEntityField field, string pattern) : base(field, field, pattern, false)
        {
        }


        /// <summary>
        /// CTor for Field LIKE Pattern.
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        /// <param name="negate">Flag to make this expression add NOT to itself</param>
        public FieldLikeIgnoreCasePredicate(IEntityField field, string pattern, bool negate) : base(field, field, pattern, negate)
        {
        }


        /// <summary>
        /// CTor for Field LIKE Pattern.
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="persistenceInfo">The persistence info object for the field</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        public FieldLikeIgnoreCasePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern) : base(field, persistenceInfo, pattern, false)
        {
        }


        /// <summary>
        /// CTor for Field LIKE Pattern.
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="persistenceInfo">The persistence info object for the field</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        /// <param name="negate">Flag to make this expression add NOT to itself</param>
        public FieldLikeIgnoreCasePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern, bool negate) : base(field, persistenceInfo, pattern, negate)
        {
        }


        
        /// <summary>
        /// Implements the IPredicate ToQueryText method. Retrieves a ready to use text representation of the contained Predicate.
        /// </summary>
        /// <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
        /// making sure the parameter is unique in the predicate and also in the predicate expression(s) containing the predicate.</param>
        /// <returns>The contained Predicate in textual format.</returns>
        /// <exception cref="System.ApplicationException">When IPredicate.DatabaseSpecificCreator is not set to a valid value.</exception>
        public override string ToQueryText(ref int uniqueMarker)
        {
        if(base.FieldCore==null)
            {
        return "";
        }

            if(base.DatabaseSpecificCreator==null)
            {
                throw new System.ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.");
            }

            base.Parameters.Clear();

            StringBuilder queryText = new StringBuilder(64);
            
            if(base.Negate)
            {
                queryText.Append("NOT ");
            }

            // create parameter
            uniqueMarker++;
            IDataParameter parameter = base.DatabaseSpecificCreator.CreateLikeParameter(String.Format("{0}{1}", base.FieldCore.Name, uniqueMarker), base.Pattern);
            base.Parameters.Add(parameter);

            queryText.AppendFormat("UPPER({0}) LIKE UPPER({1})", base.DatabaseSpecificCreator.CreateFieldName(base.PersistenceInfo, base.FieldCore.Name), parameter.ParameterName);
            return queryText.ToString();
        }
    }
}

Not tested, but this should be it.

Thanx for the lesson in C#-programming!

I usually write Java and overlooked that the class FieldLikePredicate publishes some attributes through C#-Properties, which I can use in the subclass.

No problem

nospoftombl wrote:

Hi,

is there a possibility to filter fields with LIKE ignoring case? I think of a query like this:

SELECT xx
FROM table1
WHERE LOWER(xx) LIKE LOWER('%test%');

In ORACLE this is the only possibility to get data case-insensitive with LIKE; to my knowledge MS SQL-server ignores case per default in LIKE-conditions and therefore this problem does'nt exist there ...

An alternative to the implementing this in code is to change the collation sequence that the database is using to a case-insensitive collation sequence.

I believe that SQLServer uses a case-insensitive collation sequence by default and Oracle uses a case-sensitive one.

Your DB documentation should say how to change it. In SQLServer, you can change this at any level: database, table, column, query.

If this doesn't impact the rest of your application then this is definitely the easiest way to go! If you are targeting multiple databases for the same application then you should make them as similar as possible. This is one place where there is a difference by default.

Tristan.

Hi Otis

I am also trying to get a case insensitive search with Oracle.

I have tried the approach of creating the custom predicate Like class based on the current one.

I am not quite sure of the steps involved for me to do this?

IE create the class, in my own project - ok. Then how do i actually use it and reference it correctly. There is also mention of editing templates? What do i need to do here?

It seems better that I go with the new runtime libraries which have the option included. How do I go about using these with my code? Although they are currently BETA, they will be final versions in a short while (i think you mentioned 3 weeks in the post i read.)

I have tried replacing the old .dll files with the new ones, and recompiling, but this gives me 174 build errors! Do I also need to regenerate the code and change the libraries used by the GUI ?

Any help much appreciated.

Thanks - James

JamesW wrote:

Hi Otis

I am also trying to get a case insensitive search with Oracle.

I have tried the approach of creating the custom predicate Like class based on the current one.

I am not quite sure of the steps involved for me to do this?

It's not that hard, really, you have to know the first step and that's looking at the sourcecode for the runtime libraries. THe ORMSupportClasses project contains a FieldLikePredicate class. If you check that class, you can easily create your own using that code. You should just add that class in your own project, and instead of using PredicateFactory.Like() you use new MyFieldLikePredicate(EntityFieldFactory.Create(index....)), see the new beta docs (filtering and sorting) for details, it's described in detail there how to use a predicate class without the predicatefactory class.

It seems better that I go with the new runtime libraries which have the option included. How do I go about using these with my code? Although they are currently BETA, they will be final versions in a short while (i think you mentioned 3 weeks in the post i read.)

Yes, next week if things progress as they're doing now

I have tried replacing the old .dll files with the new ones, and recompiling, but this gives me 174 build errors! Do I also need to regenerate the code and change the libraries used by the GUI ?

Yes, you need to re-generate the code as well using the new templates. Please follow the readme.htm how to setup a beta installment so you can easily use these templates without ruining your official install.

Hi

Thanks for the prompt reply. I have managed to write the class (in vb.net) for my Case Insensitive search as follows (this was via a c# to vb.net translator i have used before which has been ok, so i hope the error has not been introduced there.) :

'///////////////////////////////////////////////////////////
' LLBLGen Pro ORM Support Classes Library
' (c) 2002-2003 Solutions Design. All rights reserved.
'
'
'
' THIS IS NOT OPEN SOURCE SOFTWARE OF ANY KIND.
' SOURCECODE DISTRIBUTION IS NOT ALLOWED IN ANY WAY.
'///////////////////////////////////////////////////////////
Imports System
Imports System.Text
Imports System.Data

Imports SD.LLBLGen.Pro.ORMSupportClasses


Namespace DOH.Contact.LLBL.CustomClasses
    ' [Serializable()]  _
    '/ <summary>
    '/ Implementation of a LIKE predicate expression which ignores case, using the following formats:
    '/ IEntityField(Core) LIKE Parameter (f.e. Foo LIKE @Foo )
    '/ A specified pattern will be set as the parameters value.
    '/ </summary>
    '/ <remarks>The DB-query will be UPPER(IEntityField(Core)) LIKE UPPER(Parameter);
    '/ class is a subclass of FieldLikePredicate to use these two interchangeably and to guarantee
    '/ compliance with the 'surrounding' classes of the framework</remarks>
    Public Class FieldLikeIgnoreCasePredicate
        Inherits FieldLikePredicate


        '/ <summary>
        '/ CTor
        '/ </summary>
        Public Sub New()
        End Sub 'New



        '/ <summary>
        '/ CTor for Field LIKE Pattern.
        '/ </summary>
        '/ <param name="field">Field to compare with the LIKE operator</param>
        '/ <param name="pattern">Pattern to use in the LIKE expression</param>
        Public Sub New(ByVal field As IEntityField, ByVal pattern As String)
            MyBase.New(field, field, pattern, False)
        End Sub 'New



        '/ <summary>
        '/ CTor for Field LIKE Pattern.
        '/ </summary>
        '/ <param name="field">Field to compare with the LIKE operator</param>
        '/ <param name="pattern">Pattern to use in the LIKE expression</param>
        '/ <param name="negate">Flag to make this expression add NOT to itself</param>
        Public Sub New(ByVal field As IEntityField, ByVal pattern As String, ByVal negate As Boolean)
            MyBase.New(field, field, pattern, negate)
        End Sub 'New



        '/ <summary>
        '/ CTor for Field LIKE Pattern.
        '/ </summary>
        '/ <param name="field">Field to compare with the LIKE operator</param>
        '/ <param name="persistenceInfo">The persistence info object for the field</param>
        '/ <param name="pattern">Pattern to use in the LIKE expression</param>
        Public Sub New(ByVal field As IEntityFieldCore, ByVal persistenceInfo As IFieldPersistenceInfo, ByVal pattern As String)
            MyBase.New(field, persistenceInfo, pattern, False)
        End Sub 'New



        '/ <summary>
        '/ CTor for Field LIKE Pattern.
        '/ </summary>
        '/ <param name="field">Field to compare with the LIKE operator</param>
        '/ <param name="persistenceInfo">The persistence info object for the field</param>
        '/ <param name="pattern">Pattern to use in the LIKE expression</param>
        '/ <param name="negate">Flag to make this expression add NOT to itself</param>
        Public Sub New(ByVal field As IEntityFieldCore, ByVal persistenceInfo As IFieldPersistenceInfo, ByVal pattern As String, ByVal negate As Boolean)
            MyBase.New(field, persistenceInfo, pattern, negate)
        End Sub 'New

    


        '/ <summary>
        '/ Implements the IPredicate ToQueryText method. Retrieves a ready to use text representation of the contained Predicate.
        '/ </summary>
        '/ <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
        '/ making sure the parameter is unique in the predicate and also in the predicate expression(s) containing the predicate.</param>
        '/ <returns>The contained Predicate in textual format.</returns>
        '/ <exception cref="System.ApplicationException">When IPredicate.DatabaseSpecificCreator is not set to a valid value.</exception>
        Public Overrides Function ToQueryText(ByRef uniqueMarker As Integer) As String
            If MyBase.FieldCore Is Nothing Then
                Return ""
            End If

            If MyBase.DatabaseSpecificCreator Is Nothing Then
                Throw New System.ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.")
            End If

            MyBase.Parameters.Clear()

            Dim queryText As New StringBuilder(64)

            If MyBase.Negate Then
                queryText.Append("NOT ")
            End If

            ' create parameter
            uniqueMarker += 1
            Dim parameter As IDataParameter = MyBase.DatabaseSpecificCreator.CreateLikeParameter([String].Format("{0}{1}", MyBase.FieldCore.Name, uniqueMarker), MyBase.Pattern)
            MyBase.Parameters.Add(parameter)

            queryText.AppendFormat("UPPER({0}) LIKE UPPER({1})", MyBase.DatabaseSpecificCreator.CreateFieldName(MyBase.PersistenceInfo, MyBase.FieldCore.Name), parameter.ParameterName)
            Return queryText.ToString()
        End Function 'ToQueryText

    End Class

End Namespace

** END CODE **

I call it as follows :

' Build Search Criteria
            If Me.tbxEmail.Text.Length > 0 Then
                ' Email Address
                Dim nameExp = New FieldLikeIgnoreCasePredicate(EntityFieldFactory.Create(LLBL.ApplicantFieldIndex.Email), "%" & Me.tbxEmail.Text & "%")
                bucket.PredicateExpression.Add(nameExp)
            End If

I get an invalid cast error in the Dim nameExp line. So I am doing something wrong still. Probably being dim, but cant see what. And ideas ?

Cheers - James

Dim nameExp As IPredicate = New FieldLikeIgnoreCasePredicate( _ EntityFieldFactory.Create(LLBL.ApplicantFieldIndex.Email), "%" & Me.tbxEmail.Text & "%")

would do the trick I think

Hi

Thanks again for reply.

No, that did not work. I am getting the same error. Cant think why though. Will keep looking.

James

I think the problem is the cast between EntityField2 to EntityField ?

In this code :

Dim field As IEntityField2 = EntityFieldFactory.Create(LLBL.ApplicantFieldIndex.Email) Dim test As New FieldLikeIgnoreCasePredicate(field, "%" & Me.tbxEmail.Text & "%") Dim nameExp As IPredicate = test

At runtime, field is EntityField2.

FieldLikeIgnoreCasePredicate requires a parameter of IEntityField.

Could that be the problem, and if so what have I done wrong?

Cheers - James

JamesW wrote:

I think the problem is the cast between EntityField2 to EntityField ?

In this code :

Dim field As IEntityField2 = EntityFieldFactory.Create(LLBL.ApplicantFieldIndex.Email) Dim test As New FieldLikeIgnoreCasePredicate(field, "%" & Me.tbxEmail.Text & "%") Dim nameExp As IPredicate = test

At runtime, field is EntityField2.

FieldLikeIgnoreCasePredicate requires a parameter of IEntityField. Could that be the problem, and if so what have I done wrong?

ah . Because the selfservicing and adapter constructors are in the same class, the constructor you're calling is the selfservicing constructor. To be sure to call the adapter constructor, you have to pass in Nothing for the persistence info:

Dim field As IEntityField2 = EntityFieldFactory.Create(LLBL.ApplicantFieldIndex.Email) Dim test As New FieldLikeIgnoreCasePredicate(field, Nothing, "%" & Me.tbxEmail.Text & "%") Dim nameExp As IPredicate = test

Aha!!!!

Top man, that fixed it.

Thanks very much, you provide excellent support for your product.

James

Support quality is part of the product quality