Using SQL and a cursor to validate a textbox. 1

[AndrewMozley]

I need to validate a textbox (The customers P.O. number) when I am entering a sales order.

I have been very set in my ways, and would usually have written some code to scan through my table of sales order headers XSORD (which is already open in the form with alias SORD), and see whether there is already a record with that ‘customer reference’; then display a warning message if that is the case.

But I wondered whether it would be better style to use SQL to do the job. Something like this.
WITH Thisform is in force. Their_ref and sales_order are fields in the XSORD.DBF table.

   *  November 2020.  Check that the customer's order number is unique
   *  and warn the user if it isn't.
   SELECT * FROM sord WHERE Account = .txtcustomer.value AND ;
         Their_ref = This.Value .AND. sales_order <> .txtsalorder.value ;
         INTO CURSOR T1
   IF RECCOUNT("T1") > 0
      MESSAGEBOX("This P.O. number duplicates that on our " + CHR(13) + ;
            "Sales order " + T1.Sales_order)
      ENDIF

This appears to do the trick, but is it correct practice to use a cursor in this way? And does it matter that I am leaving the cursor lying around in the data session. I am not very experienced in using SQL.

Thank you. Andrew

 

[Mike Lewis]

Andrew,

I've often used that same technique myself, although I usually prefer to use an array rather than a cursor. Something like this:

   SELECT COUNT(*) FROM sord WHERE Account = .txtcustomer.value AND ;
         Their_ref = This.Value .AND. sales_order <> .txtsalorder.value ;
         INTO ARRAY laCount
   IF laCount(1) > 0
      * your "duplicate order" message here
   ENDIF

Using a cursor is perfectly OK as well, but I suspect that there is (slightly) less overhead with an array.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads