Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.

In this tutorial we will create a CA that will be fully updatable. If your fields have a large number of tables then you need the excellent CABuilder created by Mark McCasland and found at: http://www.foxite.com/downloads/default.aspx?id=161.

Note: If you don’t have SQLServer, install the free MSDE and then upsize your NORTHWIND.DBC found in \Samples\Northwind\ of the vfp8 install directory, as this tutorial uses this database.

Step 1
From the menu select File/New Class – Create a new class as shown below.

When the class opens in the class designer, Rt. Click it and select “Builder”. Enter a dummy name for the alias. We will change this later. Make sure the check box “Use DataEnvironment…” is ticked.

Step 2
Now we need to make some generic changes to the baseclass. Add the following new properties to the base class:

cFilterClause – This is where you will add your WHERE clause
cOrderBy – This is the ORDER BY clause of our select
cSelectCmd – This is where the guts of the Select command will go
lNoDataOnLoad – The flag whether we want data to be loaded. Set this to .T.

Open the BeforeCursorFill method and insert the following code:

LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd

* set the no data on load from our property
lNoDataOnLoad = This.lnodataonload 

* set the select command
IF NOT EMPTY(This.cselectcmd)
     cSelectCmd = This.cselectcmd

* set the where clause/s
IF NOT EMPTY(This.cfilterclause)
     cSelectCmd = cSelectCmd + [ WHERE ]+ This.cfilterclause

* set the orderby clause
IF NOT EMPTY(This.corderby)
     cSelectCmd = cSelectCmd + [ ORDER BY ]+ This.corderby

Close and save the caBaseClass. We will subclass it for our other classes. Since we are using the DE, the datasource will NOT be setup. This is OK. We will be creating a single connection in our app and using this throughout, rather than different connections.

So in the INIT of the class add this code as in the black box:

oConn is a public variable setup earlier in your code. If just running the form as a test, you can create it by entering at the command prompt:

oConn=SQLSTRINGCONNECT([Driver={Sql Server};Server=localhost;Database=Northwind;Int Security;]) 

Close and save the class.

Step 3

Now create a subclass from this for each table in the database. Here we will deal with the Orders table.
Create a new class and base it on the caBaseclass created above. This is important so that the new class inherits our settings.

Click OK to open it in the Class Designer. Rt Click and select Builder. Give the cursor a name. I suggest using CA + table name. So for orders our name is CAOrders and name the resultant ALIAS of the cursor returned as crsOrders. At this stage we need to make our CA updatable. So if “Use DataEnvironment.” is ticked, untick it and tick the “Use connection settings in builder only” checkbox. This will ensure that no connection settings are saved in this class.

You can select “Use a connection string” radio and enter the connection string as shown below. Click “Test Connection” to test the connection.

You could also select the first option “Use existing connection handle”. To use this a connection must already exist. So before opening the builder for this class, you can enter at the command line:

oConn=SQLSTRINGCONNECT([Driver={Sql Server};Server=localhost;Database=Northwind;Int Security;]) 

Now oConn has a handle to your connection, and you enter in the text area below the “Use existing connection handle”, oConn.

Click the DataAccess tab and then the Build button so that you can build fields with the correct syntax. You can select all fields as I have done or individual fields. When you click OK the CA builds the select command and the Schema.

Click the Auto-Update tab and set up as shown:

This will ensure that your cursor will now be updatable. Click OK to save your changes. At this time, take some time to examine the code placed in the INIT of the class by the builder, but do NOT make any changes to this code. Close and save the class

Let’s test this out.

Step 4: Testing the Class
Create a new form. Open the DE and RT click. Select Builder. Do NOT add any tables to the DE(click cancel). Make selections to the Data Source tab as shown if needed:

Select the Cursors Tab of the builder. Click Add and navigate to your class you just created – caOrders. Rename it from Cursor1 to caOrders. Click OK to close the CA builder and OK again to close the DE builder. crsOrders has been added to the DE.

To see the form in action you need a grid on the form. Since we had the schema generated we can now just drag individual fields or the whole cursor and drop it on the form. Add a couple of buttons as shown.

Note: We have opted for Optimistic Row Buffering as shown below when we setup our CA in the DE of the form.

In this case we will not need a save button as changes will be sent to the server whenever we move to a different row. I have added the save button to show what will be needed to save the changes. With “Optimistic table buffering” this button will be needed for saves.

Add this code in the cmdGetData.Click()

WITH Thisform.dataenvironment.caOrders
     .lnodataonload = .F.
     .cSelectCmd = [SELECT Orders.* from orders]
     ThisForm.grdorders.RecordSource = ""
     IF !.CursorFill()
     ThisForm.grdorders.RecordSource = "crsOrders"

And this code to the Save CLICK()

SELECT crsOrders
     WAIT WINDOW "Could not save. "+aex[2]
     WAIT WINDOW "Data Saved" NOWAIT

Now run the form and see that the grid is blank. Click “GetData” and the grid fills with data. Make some changes and if table buffering is on, click save. Your changes will be updated on the server.

You can easily verify this by closing and re running the form.

There are many improvements that can be made to this of course. Conflict checking, abstracting the code for data handling etc. but that is beyond the scope of this article.

I have created this tutorial to aid the development of CursorAdapters after spending many frustrating hours trying to set them up. This is aimed squarely at the newbie to CursorAdapters.

Additional Notes
1. If for some reason the CA. KeyFieldList is not setup or blank the UPDATE WILL NOT BE DONE
2. The CA.WhereType should be set to 3 – DBKEYANDMODIFIED or there will be NO update of changed fields except the KEYFIELD and hence no SQLERROR for handling conflicts.

Have fun and may the Fox live long.


Bernard Bout Bernard Bout is a long time member of the Foxite.COM Community and based in Brisbane, Australia. Bernard's weblog is here: http://weblogs.foxite.com/bernardbout/


Ronan @ 11/20/2006 6:24:51 AM
It works Bernard! Thank you


Ulises Zamora Ramirez @ 10/13/2015 3:23:07 AM
Dear Sir:

I followed your guide to creating CursorAdapter, and I think it's great. However, in some form when I include more than 5 boards do not recognize.

Could you give me a guide to where the problem would be.

Thank you

Ulises Zamora Ramirez @ 10/13/2015 3:24:32 AM
Dear Sir:

I followed your guide to creating CursorAdapter, and I think it's great. However, in some form when I include more than 5 tables into dataenviroment do not recognize someones.

Could you give me a guide to where the problem would be.

Thank you

Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: