Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 To: Bernard Bout
  Where is Bernard Bout?
 Brisbane
 Australia
 Bernard Bout
 Tags
Subject: RE: Concatenation in a CA
Thread ID: 112255 Message ID: 112340 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 Category: Classes and Objects
Date: Tuesday, November 07, 2006 1:59:21 PM         
   


> > I have this Lookup class that I am trying to create that uses a CA. (This is my first real attempt to play with CA's - in the past, I simply used SPT.) In SPT, I could easily create a cursor like:
> >
> > TEXT TO lcSqlScript NOSHOW TEXTMERGE PRETEXT 15
> >    SELECT nSupporterId, 
> >       PADR(ALLTRIM(cTitle-([ ]+cFirstName)-([ ]+cMiddleName)-([ ]+cSurname)),40) AS cSupName
> >    FROM supporter
> >    ...
> > ENDTEXT
> > SQLEXEC(lnHndl,lcSqlScript,[ResultCursor])
> > 

> > When I ran this, I would get a cursor that contained two fields: nSupporterID and cSupName. I want to do the same thing in a CA so I create the SELECT statement using the same as above and modify the schema to show nSupporterID I, cSupName C(30) but it doesn't work. Obviously, I am doing something wrong. Any suggestions?
> >
> > Ken
> > You shall know the truth - and the truth shall set you free. (John 8:33)

>
>
> Hi Ken
>
> I am assuming that you are going out to VFP tables. This is because I know that SQLServer does not know what ALLTRIM() is.
>
> Never underestimate the power of the VFP Data Explorer
>
> DO c:\vfp9\dataexplorer.app.
>
> Now in the DataExplorer (dx) click add to add a connection and select ADO connection from the list. Opt to BUILD THE CONNECTION and create the connection and test that it is working.
>
> I chose the testdata.dbc in the Samples\data directory.
>
> Once that is done, you can expand the connection and you will see the tables there. Explore the Right Click menu here. Lots of goodies.
>
> Now from the menu create a new form.
>
> Back to the DX, select the Customer table. Drag this table and drop it on the form. You have created a Cursor adapter!
>
> Open the DE of the form and select the CA there and Rt. click and select build. YOu will find that the connection stuff has been filled in for you. From the 2nd tab of the CA Builder, rebuild the Schema by clicking the Build button and check the box for Using the schema. Close the builder.
>
> Now open the CA code and properties and examine them to see what and how it has been set.
>
> Even in SPT it was not possible to construct a SQL statement like yours shown above unless the backend understood it (like VFP).
>
> I would suggest grabbing the appropriate data from the backend (if not VFP) and do a subselect into another cursor or learn the backend substitutes for ALLTRIM() and PADR.
>
> In the CA I created above the relevant props were:
>
> CursorSchema = "CONTACT C(30), COMPANY C(40), CUST_ID C(6), GASX C(34)"
> SelectCmsd = "Select customer.contact, customer.company, customer.cust_id, customer.phone + customer.postalcode as gasx from customer"
>
> I dropped the CA onto the form to create a bound grid that is filled with data on running.


Bernard,

You are correct, I was going to a VFP backend. I am still quite new to CA's so I am trying it out on VFP tables first. When I ran into this problem, I wasn't really thinking of the SQL equivalent, so when I tried the same thing over a SQL table, I ran into SQL not understanding the ALLTRIM() function. I therefore came to the same conclusion as you did - run a secondary query.

In my SPT data classes, all I need do is tell the class what kind of backend I am using and it builds the SELECT using the correct syntax all by itself (check out Andy's article on Class Based Data Management on his website - what I do is based on that article) so I want to do the same sort of thing using CA's. Obviously, having an ALLTRIM() in the query will not work for me - chalk it up to old age.

Thanks for the tip on the DataExplorer - had forgotten all about it - again, old age.

Ken
You shall know the truth - and the truth shall set you free. (John 8:33)

ENTIRE THREAD

Concatenation in a CA Posted by Ken Murphy @ 11/6/2006 6:37:13 PM
RE: Concatenation in a CA Posted by Ciprian Zaharia @ 11/6/2006 10:22:57 PM
RE: Concatenation in a CA Posted by Ken Murphy @ 11/6/2006 11:34:48 PM
RE: Concatenation in a CA Posted by Ciprian Zaharia @ 11/6/2006 11:54:14 PM
RE: Concatenation in a CA Posted by Ken Murphy @ 11/7/2006 1:51:19 AM
RE: Concatenation in a CA Posted by Ciprian Zaharia @ 11/7/2006 2:57:29 AM
RE: Concatenation in a CA Posted by Bernard Bout @ 11/7/2006 8:21:27 AM
RE: Concatenation in a CA Posted by Ken Murphy @ 11/7/2006 1:59:21 PM
RE: Concatenation in a CA Posted by Bernard Bout @ 11/8/2006 5:15:09 AM