Search Screen Issue

[SBarnes]

I have found an issue with the search screen when building a custom search where legal SQL syntax works to load the screen but as soon as you type in the search boxes you get the error, the error is that the field that might be say FName if it is surrounded by [] causes the error but the field name on its own does not, the error is when the like clause gets built it can not work the field name so the first bit of code below work fine

Code: Select all

      public void Search(System.Windows.Forms.Form OwnerForm)
        {

         var search = this.Manager.Search;
         

            search.Clear();
            search.Caption = "Salesman Rates";

            var searchOption = new JiwaFinancials.Jiwa.JiwaApplication.JiwaSearch.SearchOption();
            searchOption.Title = "Salesman Rates";
            searchOption.SQLStr = @" SELECT StaffID
                           ,FName
                           ,SName
                           ,DisplayName
                             FROM Attk_HR_SalesmanCommissionRatesHeader ";
            searchOption.OrderBy = @"ORDER BY DisplayName ";
            searchOption.AddColumn("StaffID", Microsoft.VisualBasic.VariantType.String, "", 0, 1);
         searchOption.AddColumn("First Name", Microsoft.VisualBasic.VariantType.String, "", 25, 2);
         searchOption.AddColumn("Surname", Microsoft.VisualBasic.VariantType.String, "", 25, 3);
         searchOption.AddColumn("Display Name", Microsoft.VisualBasic.VariantType.String, "", 25, 4);

            search.AddSearchOption(ref searchOption);

            if (search.Show(OwnerForm) == DialogResult.OK)
            {
                if (search.Results.Count > 0)
                {
                    var idField = (JiwaFinancials.Jiwa.JiwaApplication.JiwaSearch.Field)search.get_Fields(1);
                    Read(idField.FieldValue.ToString());
                }
            }
            else
            {
                throw new JiwaFinancials.Jiwa.JiwaApplication.Exceptions.ClientCancelledException();
            }
        }   

But the following does not even though its legal SQL syntax

Code: Select all

      public void Search(System.Windows.Forms.Form OwnerForm)
        {

         var search = this.Manager.Search;
         

            search.Clear();
            search.Caption = "Salesman Rates";

            var searchOption = new JiwaFinancials.Jiwa.JiwaApplication.JiwaSearch.SearchOption();
            searchOption.Title = "Salesman Rates";
            searchOption.SQLStr = @" SELECT [StaffID]
                           ,[FName]
                           ,[SName]
                           ,[DisplayName]
                             FROM [Attk_HR_SalesmanCommissionRatesHeader] ";
            searchOption.OrderBy = @"ORDER BY DisplayName ";
            searchOption.AddColumn("StaffID", Microsoft.VisualBasic.VariantType.String, "", 0, 1);
         searchOption.AddColumn("First Name", Microsoft.VisualBasic.VariantType.String, "", 25, 2);
         searchOption.AddColumn("Surname", Microsoft.VisualBasic.VariantType.String, "", 25, 3);
         searchOption.AddColumn("Display Name", Microsoft.VisualBasic.VariantType.String, "", 25, 4);

            search.AddSearchOption(ref searchOption);

            if (search.Show(OwnerForm) == DialogResult.OK)
            {
                if (search.Results.Count > 0)
                {
                    var idField = (JiwaFinancials.Jiwa.JiwaApplication.JiwaSearch.Field)search.get_Fields(1);
                    Read(idField.FieldValue.ToString());
                }
            }
            else
            {
                throw new JiwaFinancials.Jiwa.JiwaApplication.Exceptions.ClientCancelledException();
            }
        }