Naomi,

Because you may want to query using more parameters than just the Customer ID.

For instance, you might have an option on a particular form to show "past" addresses, or addresses that are only good for a certain period of time (ex. - many elderly snowbirds have two addresses, one for summer and one for winter) -- so you'd add a check for an "active" boolean or possibly a range of effective dates. You might want to filter the addresses for a single ZIP code. You might want to show related family member's addresses -- so you might want to send a list of CustomerID values -- or use LIKE instead of = to bring back surrounding addresses (like for a mailing list to neighbors).

I can think of **LOTS** of different permutations of this...and you can handle them all with a single CA object. Using your approach, you'd now need *several* additional CA's, all indexed in different ways, to handle all the particular options. Managing those additional objects can get cumbersome if you're dealing with lots of child tables, as Matt stated.

Also, with my suggestion, you could (if desired) store all the necessary SelectCmd possibilities in a SQL table of their own and choose the one you wanted as needed or required by using a "keyword" -- thereby, your entire data querying mechanism is itself "data driven".

What I showed Matt was just one approach. Personally, I don't use DE's, so my personal approach is a bit different -- but Matt uses them, so I was tailoring my answer to his methodology.

Evan

>Hi Evan,
>
>I disagree with this approach for this particular scenario. Why not just use a parameter and CursorRefresh instead? Why do you need to change the whole SelectCmd instead?
>
>>Matt,
>>
>>You don't need indexes on the client side.
>>
>>In your DE, establish all the necessary CA's that you need for the child tables in SQL -- but don't *fill* them yet. Then, when you need the data for a child, parameterize the statement in the SelectCmd property of that child CA and fill that cursor.
>>
>>For example, let's say you need the address of the customer that's selected, let's assume that you have a char or varchar column called CustomerID in your Customers table in SQL, and that you have a CustAddr table in SQL which also has the CustomerID column.
>>
>>So, now, when a user hits the Next button in your VCR control, the code that fires would skip to the next row in your Customers CA cursor; then it would change the SelectCmd property of the **CustomerAddresses** CA in your DE to something like
>>

>>lo.CustomerAddresses.SelectCmd = ;
>>  [select * from dbo.custaddr where CustomerID = '] + ;
>>  lo.Customers.CustomerID + ;
>>  [']
>>

>>and then issue the lo.CustomerAddresses.CursorFill() statement. Voila -- only the address you want. Now you can populate (or bind) the desired columns of CustomerAddresses to your form controls.
>>
>>You can do the same thing with your other child tables as needed. Obviously, you're going to want indexes on the *SQL* side for increased query speed, but you can do that sort of tuning as required.
>>
>>Evan
>>
>>>Me again... So I've got my DataEnvironement class created (though the VFP IDE and builder) and I added two CursorAdapters (again through the IDE Builder tool
>>>
>>>For now I am just testing from the command window, so I create a DE oject, and open the two CursorAdatpers like this:
>>>
>>>

>>>Set classlib to f:\work\lmsql\lmdata
>>>lo=NewObject("lmdata")
>>>
>>>lo.Jobs.CursorFill()
>>>lo.Customers.CursorFill()
>>>

>>>... and WOW, I get two VFP-looking cursors from my SQL Server (which is running on some other test machine I have running on my network).
>>>
>>>
>>>Now, my big question is "What about my indexes" that I need for my one-to-many navigation?
>>>
>>>You see, in the current native VFP architure of my app, I use free tables, and I call a method in the form.load() event to open the tables and set the Relations: (example)
>>>
>>>

>>>*-- Open child tables ----
>>>select 0
>>>use Customers order custno
>>>
>>>select 0
>>>use Materials order jobno
>>>select 0
>>>use TimeRecords order jobno
>>>
>>>
>>>*-- Open Parent Table -----
>>>select 0
>>>use Jobs order jobno
>>>Set relation to custno into Customers
>>>Set Relation to jobno into Materials additive
>>>Set Relation to jobno into TimeRecords additive
>>>

>>>
>>>
>>>Now I have the pointer in each of the Child tables table moving when I move the Jobs pointer...I rely on this very much in the navigation structure and processing and display of data in my app. I use a VCR control to move around the Jobs table, as well as a lookup textbox to jump straight to a certain Job, but then I need all the Child cursors to be in synch also. In total I may have about 6 to 8 child tables that I need to have in synch with the Jobs pointer.
>>>
>>>So how do you get this part working? Surely I do not have to create indexes on each child every time I launch the forms.
>>>
>>>
>>>
>>>
>>>
>>>>
>>>>I've been slowly moving systems from VFP over to MSSQL I use cursor adapters to mimic as much as possible the original DBF and to reduce the amount of recoding necessary.
>>>>
>>>>I do use upsizing wizard to convert the data originally.
>>>>
>>>>On the C# side just use ToList() on the query and you will then have an index. You already know how the relationships work.