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.