Searching Data grid bound to a cursor 2

[Tengo1]

I'm new to VFP9. I'm actually a VB programmer who drew the short straw and got stuck supporting a VFP9 program.

I have a data grid that contains several columns. The main ones involved in this question are UnitNo and Inactive (which is a 1 char flag). I need to do some validation to make sure that each UnitNo is entered no more than 2 times. One with an Inactive flag set to "Y" and one with "N".

I've got my cursor that gets declared on page load (crea curs tmpgridu (unitno C(8),inactive C(1)). After the cursor is declared an index is set (inde on inactive+unitno tag unitno addi).

The cursor is loaded and used as the recordsource for the data grid. On the validate routine for the Unitno field I run a query against the cursor (SELECT inactive FROM (thisform.pageframe1.page2.grdunits.RecordSource) WHERE unitno = This.Value INTO ARRAY aTemp) and if 2 records are returned I assume that there's already a record with "Y" and "N", and if one is returned I handle it accordingly. The UnitNo field is validating perfectly.

I have 4 records in my grid currently. The UnitNo values are ("TEST", "TEST 1", "TEST 2", and "1")

I tried using the same query method when validating the Inactive field. but I get junk returned. (SELECT inactive FROM (thisform.pageframe1.page2.grdunits.RecordSource) WHERE ALLTRIM(unitno) = UPPER(ALLTRIM(tmpgridu.unitno)) INTO ARRAY aTemp) When I tab through the Inactive field on the "TEST" record, it returns 3 records. The Array contains "TEST", "TEST 1" and "TEST 2".

The only difference between these two situations is that In the UnitNo Validation routine, I'm querying based on this.value and in the INactive validation routine, I'm quering based on tmpgridu.unitno from the cursor.

I'm sorry for being long winded but I wanted to give enough information so that some VFP guru can show me where I'm going wrong.

Thanks in advance for your help.

 

[Mike Lewis]

Tengo1,

Welcome to VFP.

I'm not completley sure I've understood the problem, but at first glance it loooks as if there is definitely something wrong with the WHERE clause in the query that validates Inactive:

...ALLTRIM(unitno) = UPPER(ALLTRIM(tmpgridu.unitno))

It looks as if tmpGridu is both the source of the query (the FROM clause) and part of the condition. If you think about, that doesn't really make sense.

If you are tying to check that at most two records contain the UnitNo from the current grid row, you need to do something like this:

lnUnitNo = this.value  && store in a local variable
SELECT COUNT(*), UnitNo FROM tmpGridu ;
  GROUP BY UnitNo HAVING Count(*) > 2 ;
  WHERE UnitNo = lnUnitNo ;
  INTO ARRAY aTemp

If the test fails, you should see one row in the array (containing the unit number in question and the number of times it's there). If the validation succeeds, then the array won't exist or will be empty.

I haven't tested the above code, so it might not be completely correct, but it should point you in the right direction.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

I think you may be running into one of the tricky things about VFP, the way strings are matched. By default, in a query (SELECT), strings are matched to the end of the shorter string. So, when you test UnitNo = "TEST", you get any UnitNo that begins with "TEST".

There are a couple of ways to deal with this. If you want exact string matching in all your queries (and there are lots of times when you don't), you can SET ANSI ON to turn off the default behavior. If you'd prefer to handle it locally within the query, use == ("exactly equals") rather than =.

Also, be aware that the same thing applies to non-SQL code in VFP, except that it's controlled by SET EXACT rather than SET ANSI.

Tamar

 

[Tengo1]

Thank you very much Mike and Tamar. Both of your suggestions were very helpful. After I posted my question, I kept researching further and found the "SET ANSI ON" solution. That worked for me.

 

[Olaf Doschke]

I'd rather suggest using == and padding the value to the field length for an exact match. ANSI ON will let you loose the advantage of simple "begins with" filtering.

And another very simple solution to your validation would be a candidate index:

index on inactive+unitno tag unitno candidate additive

Then a third record with "Y/N"+unitno would raise an error.
And you could handle that error, if you'd wrap the insert or tableupdate in a try-catch block, or use the .Error event or an ON ERROR routine.

Bye, Olaf.