Rendergeek

Building Data Aware Applications with Visual FoxPro Part 3: Universal Data Access

Summary: In this article series, we have demonstrated that Visual Class Libraries are an excellent place to store code that access your data (and by so doing, eliminating duplication of similar code and reducing application complexity). So far in the articles published earlier (Part 1 and Part 2 of this article series), we have assumed that we are building ‘pure fox’ applications and have therefore used the ‘USE’ whenever we needed to access data stored in the FoxPro database. However, the complexity of today’s applications often demand that our VFP applications be flexible enough to access data stores other than our beloved native Fox databases and tables. This article explains how to extend your data-aware classes to perform universal data access Visual FoxPro Style (i.e. accessing data stores other than Visual FoxPro such Advantage Database Server or Microsoft SQL Server). This article utilizes Ms SQL Server 2005.

Introduction: so far in this article series we have assumed that the data access code in our Visual Class Libraries (VCX) will be accessing native Visual FoxPro databases and tables. However, even though the Fox provides excellent storage capabilities, we will sometimes want or need to benefit from increased storage capacity, enhanced security and additional transactional security provided by SQL Servers in enterprise application development.

In this scenario, we can still take full advantage of the tremendous power, speed and performance of the Visual FoxPro database engine and its programming language to build powerful enterprise level applications efficiently and quickly. Visual Class Libraries still represent an excellent location to store data access code to access and process data located in remote and external data stores. This means using a Visual FoxPro front-end to access and process data in other data stores.

Visual FoxPro Universal Data Access: With VFP, there is always more than one way to perform the same task. To access and use remote data in VFP, you could for example use remote views/queries or SQL Pass Through (SPT) technology both of which would make heavy use of Open Data Base Connectivity (ODBC)  as the principal means for data access, retrieval and updates.

The term remote data as used in this article refers to data stored outside a traditional Visual FoxPro database, either on the same computer or on a computer separate from the one in which the Visual FoxPro application runs. For example, data stored in a Advantage Database Server or Microsoft SQL Server that needs to be consumed by a Visual FoxPro application can be considered remote data in this case.

Even though SPT and remove views are very powerful means of making remote data accessible to your application, Visual FoxPro’s Cursor Adapter will most probably remain the most flexible means of doing this because it provides the flexibility to get at remote data through different data source types. Using Cursor Adapters you could access native (VFP) data stores, ODBC Data sources (i.e. data exposed by means of ODBC), ADO Data Sources (i.e. Data exposed by means of ActiveX Data Objects)  or XML Data Sources.

Most VFP programmers or Visual Basic Programmers building enterprise applications will be immediately familiar with ODBC and ADO since these have been the principal means of data access to SQL Servers on the Microsoft Platform for years. Perhaps the XML Dataset could compete with these if you need to consume ADO.Net Datasets from within your VFP application or XML Datasets published by other tools that support and utilize the XML Standard.

The Cursor Adapter provides many capabilities, giving you fine control over how to manage your data sources, retrieve, edit and update data. The data returned from your cursor adapter is rendered into a native Visual FoxPro cursor so you could work with it just as you would do with cursors created from native Visual FoxPro tables. The Visual FoxPro Help pages contain some good information on how cursor adapters and their properties work.

We can use cursor adapters to retrieve data, insert new records, edit existing records and delete unwanted records from remote data databases. What is of key interest to us is the ability of cursor adapters to consume data from OLE DB/ADO data sources, ODBC Data Sources and ADO.Net Data sources (using XML of course).

In this article, we will demonstrate working with data stored in a SQL Server using Cursor Adapters wrapped up in a data access class contained in a Visual Class Library (VCX). Our ubiquitous Employees table that we have always used in previous articles in this series is this time stored in a SQL Server 2005 Express Database called syl-pcsqlexpress.students.dbo. We shall get at our SQL Server database using the SQL Server OLE DB Provider.

Building our Customers Data Access Class: To begin with we must create our data access classes as usual. In Previous articles, we created classes in Visual Class Libraries to which we then added code. The best way to access SQL Server from Visual FoxPro will be by means of its OLE DB Provider. Visual FoxPro already provides extensive support for accessing database servers using OLE DB/ADO. This means you create and use all of the normal ADO Objects that you are used to in Visual Basic. For example:

LOCAL oConn AS ‘ADODB.Connection’

LOCAL oRS AS ‘ADODB.Recordset’

oConn = CREATEOBJECT(“ADODB.Connection”)

oRS = CREATEOBJECT(“ADODB.Recordset”)

etc

would be perfectly valid statements. Now while you can use pure ADO code in this fashion to work with the returned recordset, the result would almost certainly not feel natural. By throwing the cursor adapter into the mix, you get the ability to work with the ADO Recordset as if it were a native Visual FoxPro Cursor. This means that apart from the code that creates and returns the ADO Recordset Object as demonstrated above, you can perform your usual Visual FoxPro commands such as APPEND BLANK, REPLACE, BROWSE, etc. Several steps are involved in putting together this solution:  1) We would have already created our database in SQL Server, 2) We must create a VFP Project, 3) Add a Class Library named ‘Data Access’ 4) Add a ‘Customers’ class and add all of its methods, 5) Add a new Form and Lay it out.

Let us Begin step by step then,

1)      Create the CustMast table in SQL Server 2005 Express: Our solution utilizes an SQL Server 2005 Express Database – ‘Database2SQL’. This database contains a table ‘CustMast’ that is used in this example. The CustMast table has the following structure:

 

Column Name

Data Type

Allow Nulls

Primary Key

CustomerID

Char(20)

N

Y

CustName

Char(50)

Y

 

Contact

Char(50)

Y

 

NoOfAddre

Int

Y

 

NoOfTels

Int

Y

 

 

To create your database, you need a tool that allows you to connect to and create SQL Server 2005 Express databases. Unfortunately, VFP Data Explorer allows you to create connections and query your SQL Server 2005 Databases, not create new databases! If you have Visual Studio 2005, you can create SQL Server 2005 Databases and modify schema. Alternatively, you can use Microsoft Access 2007, the database program in the Microsoft Office Suite to create a Access Project through which you can then create your SQL Server 2005 Database. For more information on how to perform these tasks, see the Microsoft Access documentation or the Visual Studio 2005 documentation.

 

After successfully creating your SQL Server 2005 Database and adding the CustMast table to it, you can use the Data Explorer in Visual FoxPro 9 (accessible by choosing the Data Explorer button on the Task Pane Manager) to browse your SQL Server and other Data resources. If you cannot see your SQL Server on the List, choose the Add Connection button to create a connection to your SQL Server so you can see all the Databases on it.

2)      Create your VFP Project: Now with the SQL Server side of things done, we must create a VFP Project by performing the following action: i) Start VFP and then select New Project  from the Start Page of the Task Pane Manager. The Create dialog box displays, ii) In the Enter Project File box, enter ‘CusAdapter’ (without the quotes) and choose the Save button. VFP creates a new blank project. You are now ready to add objects to your project.

3)      Create Your Visual Class Library: After creating your VFP Project, you must create a Visual Class Library on the Classes tab of the Project Manager and add a class to it. We shall call our class library ‘data access’ and the class added to it ‘CustMast’. We can create the class library by performing the following action: i) Choose the New button on the Classes tab of the Project Manager to display the New Class dialog box, ii) In the Class Name box, enter ‘CustMast’ without the quotation marks, iii) In the Based On list, select Cursor Adapter, iv) In the Store In box, enter ‘DataAccess’ without the quotation marks, v) Choose Ok. The system will create a new Visual Class Library and add a new blank class to it. You are now ready to program your class.

4)      Add Code To your Class: we now need to add useful code to our data access class. We have added three methods, to search for existing records, to delete an existing record and to add new records.

1. Adding a Record: This method is called AddRecord. It allows our program to insert a new record into the SQL Server table. It looks like this:

 

PARAMETERS arrE,intNoOfRecs AS Integer

* Declarate Variabled used locally

PUBLIC oCA as CursorAdapter

LOCAL oConn as ADODB.Connection

LOCAL oRS as ADODB.Recordset

LOCAL oException AS Exception

LOCAL cConnString

LOCAL intRows AS Integer,cMsg As Character

intRows = 0

cMsg = “”

 

IF TYPE(‘arrE’,1) <> ‘A’

      * Exist because we expected an array of valued

      RETURN .F.

ENDIF

 

IF TYPE(‘intNoOfRecs’) <> ‘N’

      * Exit because number of rows passed in array is not supplied

      RETURN .F.

ENDIF

* Handle connections – insert connection code

cConnString = [Provider=SQLOLEDB.1;Data Source=SYL-PCSQLEXPRESS;Initial Catalog=Database2SQL;User ID=;Password=;Integrated Security=SSPI]

IF NOT USED(“CustMast”) && The cursor does not exist…create it

TRY

    oConn  = createobject(‘ADODB.Connection’)

 

    * Ensure that you handle userid and password if not

    * specified in connection string.

    *   ex. oConn.Open(cConnString, userid, password)

    oConn.Open(cConnString)

 

    oRS = CREATEOBJECT(“ADODB.Recordset”)

    oRS.DataSource.CursorLocation = 3   &&adUseClient

    oRS.DataSource.LockType = 3   &&adLockOptimistic

    oRS.ActiveConnection = oConn

 

 

            THIS.DataSourceType =”ADO”

            THIS.DataSource = oRS

            THIS.MapBinary = .T.

            THIS.MapVarchar= .T.

            THIS.Alias = “CustMast”

            THIS.SelectCmd = “SELECT * FROM CustMast”

   

    IF !THIS.CursorFill()

        * Replace with error code here

        LOCAL laError

        DIMENSION laError[1]

        AERROR(laError)

        MESSAGEBOX(laError[2])

    ELSE

        * Replace with user code here. Code below allows for

        * you to edit and send updates to the backend.

        LOCAL laFlds,lcStr,lnFldCount,i

        DIMENSION laFlds[1]

        lnFldCount=AFIELDS(laFlds)

        lcStr=”"

        FOR i = 1 TO lnFldCount

                lcStr = lcStr + laFlds[m.i,1] +  “,”

        ENDFOR

        THIS.UpdatableFieldList = lcStr

        THIS.KeyFieldList=”CustomerID”

       

        *BROWSE NORMAL NOWAIT

    ENDIF

 

CATCH TO oException

    * Replace with exception handling code here

    MESSAGEBOX(oException.Message)

 

ENDTRY

ENDIF

* Add user code here.

* Note: cursors created by CursorAdapter object are closed when object is released.

 

SELECT CustMast

FOR intRows = 1 TO intNoOfRecs

      IF ISBLANK(arrE(intNoOfRecs,1))

            * No Record was supplied so we can as well exit

            oRs.Close

            RELEASE oRS

            RETURN .F.

      ENDIF

      SELECT CustMast

      GO TOP

      LOCATE FOR ALLTRIM(CustMast->CustomerID) = ALLTRIM(arrE(intRows,1))

      IF NOT FOUND()

            APPEND BLANK

            REPLACE CustMast->CustomerID WITH arrE(intRows,1)

      ENDIF

      REPLACE CustMast->CustName WITH arrE(intRows,2)

      REPLACE CustMast->Contact WITH arrE(intRows,3)

      REPLACE CustMast->NoOfAddre WITH arrE(intRows,4)

      REPLACE CustMast->NoOfTels WITH arrE(intRows,5)

ENDFOR

 

 

The first line is the PARAMETERS line that accepts two parameters ‘arrE’ (array containing one or more records to be saved) and ‘intNoOfRecs’ (an integer defining the total number of records contained in the array). Following this line, variables are declared and initialized to their correct values. oCA is a Cursor Adapter Object, oConn is an ADO Data Connection Object while oRS represents an ADO Recordset Object. The TYPE functions are used to ensure that arrE and intNoOfRecs are of the correct types. In Visual FoxPro components, passing parameters of the correct type is key to making or breaking your application. We have found that errors that VFP would ordinarily treat kindly in a Form or Program file, VFP treats rather harshly in components! The variable oConnString then contains the connection string that will be used to connect to our SQL Server OLE DB Provider and through it to our SQL Server Database. Provider=SQLOLEDB.1 indicates that we are using the Ms SQL Server OLE DB Provider. The Data Source=SYL-PCSQLEXPRESS indicates the name of the SQL Server we are connecting to. Initial Catalog=Database2SQL indicates the database that contains the table we want on the SQL Server.

 

The CREATEOBJECT function is now used to create the connection object (oConn = CREATEOBJECT(“ADODB.Connection”)) which is then immediately opened (oConn.Open). Another CREATEOBJECT function creates an ADO Recordset object (oRS = CREATEOBJECT(“ADODB.RecordSet”). The cursor of the recordset is then set to a client-side cursor and the LockType is set to 3 which represents optimistic locking. Its ActiveConnection object is set to the ADO connection object we created earlier – oConn. The next series of statements specify a data source type and alias name (‘CustMast’) for the Cursor Adapter Object. The SelectCmd is a straight forward SQL SELECT command that returns all rows from the table. This simply returns all rows in the Customer Master table and stores the results of this query in a cursor called ‘CustMast’.  (of course in a real-live solution, you will most likely fetch as you need by qualifying the SQL Command with a WHERE clause thus reducing the number of packets that have to travel over the network)

 

The CursorFill method is used to execute the SelectCmd command. If it fails, the errors are stored in the array LaError so that we can parse and deal with the errors else, we can build a list of fields so that we can update the backend. The fields in this list will determine what table fields VFP updates if you decide to send updates back to SQL Server. The comma-delimited list of updateable fields is passed to the UpdatableFieldList. The KeyFieldList must also be set to tell VFP the primary key field of your table. In our case, it is ‘CustomerID’. The FOR intRows TO intNoOfRecs…ENDFOR now loops through each record in the array and then issues the standard APPEND BLANK and REPLACE commands to insert new records and save changes to an existing record.

1. Find a Record: If you want to display an existing record for editing or display a record you want to delete, you will have to search for the record and then display it. The procedure shown here searches for the record that matches the key and then returns it in an array. The code in this method looks like this:

 

* Search for a record that meets the criteria

PARAMETERS cCustomerID As Character,arrE,intNoOfRecs AS Integer

 

PUBLIC oCA as CursorAdapter

LOCAL oConn as ADODB.Connection

LOCAL oRS as ADODB.Recordset

LOCAL oException AS Exception

LOCAL cConnString

LOCAL intRows AS Integer

 

intRows = 0

 

* Make sure that supplied parameters are of the correct types

IF TYPE(‘cCustomerID’) <> ‘C’       && Customer ID must be a Character

      RETURN .F.

ENDIF

 

IF TYPE(‘arrE’,1) <> ‘A’      && arrE must be an array

      RETURN .F.

ENDIF

 

IF TYPE(‘intNoOfRecs’) <> ‘N’ && intNoOfRecs must be a number

      RETURN .F.

ENDIF

 

* Handle connections – insert connection code

cConnString = [Provider=SQLOLEDB.1;Data Source=SYL-PCSQLEXPRESS;Initial Catalog=Database2SQL;User ID=;Password=;Integrated Security=SSPI]

IF USED(“CustMast”)

      * Close it so we can obtain the latest records for display

      USE IN CustMast

ENDIF

TRY

    oConn  = createobject(‘ADODB.Connection’)

 

    * Ensure that you handle userid and password if not

    * specified in connection string.

    *   ex. oConn.Open(cConnString, userid, password)

    oConn.Open(cConnString)

 

    oRS = CREATEOBJECT(“ADODB.Recordset”)

    oRS.DataSource.CursorLocation = 3   &&adUseClient

    oRS.DataSource.LockType = 3   &&adLockOptimistic

    oRS.ActiveConnection = oConn

 

    oCA=CREATEOBJECT(“CursorAdapter”)

    oCA.DataSourceType = “ADO”

    oCA.DataSource = oRS

    oCA.MapBinary = .T.

    oCA.MapVarchar = .T.

 

    oCA.Alias = “CustMast”

    oCA.SelectCmd = “SELECT * FROM CustMast WHERE CustMast.CustomerID = ‘” + cCustomerID + “‘”

   

    IF !oCA.CursorFill()

        * Replace with error code here

        LOCAL laError

        DIMENSION laError[1]

        AERROR(laError)

        MESSAGEBOX(laError[2])

    ELSE

        * Replace with user code here. Code below allows for

        * you to edit and send updates to the backend.

        LOCAL laFlds,lcStr,lnFldCount,i

        DIMENSION laFlds[1]

        lnFldCount=AFIELDS(laFlds)

        lcStr=”"

        FOR i = 1 TO lnFldCount

                lcStr = lcStr + laFlds[m.i,1] +  “,”

        ENDFOR

        oCA.UpdatableFieldList = lcStr

        oCA.KeyFieldList=”CustomerID”

       

        *BROWSE NORMAL NOWAIT

        SELECT CustMast

        DO WHILE NOT EOF()

            IF ALLTRIM(CustMast->CustomerID) = ALLTRIM(cCustomerID)

            intRows = intRows + 1

            DIMENSION arrE[intRows,5]

                          arrE(intRows,1) = CustMast->CustomerID

                          arrE(intRows,2) = CustMast->CustName

                          arrE(intRows,3) = CustMast->Contact

                          arrE(intRows,4) = CustMast->NoOfAddre

                          arrE(intRows,5) = CustMast->NoOfTels

                              EXIT

                              ENDIF

        ENDDO

        intNoOfRecs = intRows

    ENDIF

 

CATCH TO oException

    * Replace with exception handling code here

    MESSAGEBOX(oException.Message)

 

ENDTRY

 

 

 

The FindRecord method bears some resemblance to the AddRecord method. The PARAMETERS line passes the Customer ID to search for along with an empty array to hold all records that meet the criteria so that these can be returned to the calling program (in this case the Form). The SELECT SQL statement is now qualified with a WHERE clause. This is a classic example of the ‘fetch as you need’ paradigm that allows you to build efficient VFP programs that reduce network traffic and perform efficiently. Once the CursorFill method populates the array, a DO WHILE…ENDDO loop populates the array.

1. Delete a Record: This method is called RemoveRecord and permits removal of the record from the SQL Server Table. The code looks like this:

 

* Search for a record that meets the criteria

PARAMETERS cCustomerID As Character

 

PUBLIC oCA as CursorAdapter

LOCAL oConn as ADODB.Connection

LOCAL oRS as ADODB.Recordset

LOCAL oException AS Exception

LOCAL cConnString

LOCAL intRows AS Integer

 

intRows = 0

 

* Make sure that supplied parameters are of the correct types

IF TYPE(‘cCustomerID’) <> ‘C’       && Customer ID must be a Character

      RETURN .F.

ENDIF

 

*!*   IF TYPE(‘arrE’,1) <> ‘A’      && arrE must be an array

*!*         RETURN .F.

*!*   ENDIF

 

*!*   IF TYPE(‘intNoOfRecs’) <> ‘N’ && intNoOfRecs must be a number

*!*         RETURN .F.

*!*   ENDIF

 

* Handle connections – insert connection code

cConnString = [Provider=SQLOLEDB.1;Data Source=SYL-PCSQLEXPRESS;Initial Catalog=Database2SQL;User ID=;Password=;Integrated Security=SSPI]

IF USED(“CustMast”)

      * Close it so we can obtain the latest records for display

      USE IN CustMast

ENDIF

TRY

    oConn  = createobject(‘ADODB.Connection’)

 

    * Ensure that you handle userid and password if not

    * specified in connection string.

    *   ex. oConn.Open(cConnString, userid, password)

    oConn.Open(cConnString)

 

    oRS = CREATEOBJECT(“ADODB.Recordset”)

    oRS.DataSource.CursorLocation = 3   &&adUseClient

    oRS.DataSource.LockType = 3   &&adLockOptimistic

    oRS.ActiveConnection = oConn

 

    oCA=CREATEOBJECT(“CursorAdapter”)

    oCA.DataSourceType = “ADO”

    oCA.DataSource = oRS

    oCA.MapBinary = .T.

    oCA.MapVarchar = .T.

 

    oCA.Alias = “CustMast”

    oCA.SelectCmd = “SELECT * FROM CustMast WHERE CustMast.CustomerID = ‘” + cCustomerID + “‘”

   

    IF !oCA.CursorFill()

        * Replace with error code here

        LOCAL laError

        DIMENSION laError[1]

        AERROR(laError)

        MESSAGEBOX(laError[2])

    ELSE

        * Replace with user code here. Code below allows for

        * you to edit and send updates to the backend.

        LOCAL laFlds,lcStr,lnFldCount,i

        DIMENSION laFlds[1]

        lnFldCount=AFIELDS(laFlds)

        lcStr=”"

        FOR i = 1 TO lnFldCount

                lcStr = lcStr + laFlds[m.i,1] +  “,”

        ENDFOR

        oCA.UpdatableFieldList = lcStr

        oCA.KeyFieldList=”CustomerID”

        SELECT CustMast

        DELETE FOR ALLTRIM(CustMast->CustomerID) = ALLTRIM(cCustomerID)

      

    ENDIF

 

CATCH TO oException

    * Replace with exception handling code here

    MESSAGEBOX(oException.Message)

 

ENDTRY

 

 

 

This code is exactly the same as the code to search for a record with some very minor variations. We use a DELETE FOR statement to remove a record. Because we have not made a setting for the DeleteCmd property of the CursorAdapter, the CursorAdapter automatically generates the DELETE SQL command to perform the deletion as long as the CursorAdapter AllowDelete property is set to true (.T.), the default.

5)      Create your Data Entry Form: Now to complete this example, we must create a new data entry form. Your designed form would contain controls to display all the fields in your SQL Server table. The Data entry Form could contain the following controls:

1. txtCustomerID: This is a text box control. It will be used to enter the Customer ID or display one for editing. When you enter a value in this field, the code ion the Valid event will be used to check if this customer already exists in the application database. In this respect, it will call the FindRecord method of the CursorAdapter class.
2. txtCustName: This is the name of the customer. It will display a customer Name or you can type a new customer name into it.
3. txtContact: Name of principal contact person.
4. txtNoOfAddr: Total number of addresses received from this customer.
5. txtNoOfTels: Total number of Telephone Numbers received from this customer
6. cmdAddRecord: Command button whose click event code calls the AddRecord method of the CursorAdapter class.
7. cmdDelete: Command button whose click event code calls the RemoveRecord method of the CursorAdapter class.
8. cmdBrowse: Display data returned from your SQL Server in Browse mode.
9. cmdNew: Display a blank record template so that you can create a new record.
10. Form Caption: Set the Form Caption to ‘Customer Master’.

 

AS with our visual class library, the form will also contain code as follows:

1. Display an existing record: We will use the same form for adding new records, amending existing records plus saving any changes made, and deleting unwanted records from our SQL Server database table. The code to do this is placed in the Valid event of the txtCustomerID  textbox field. When a user enters an ID into the CustomerID field, the system calls the FindRecord method of our class that performs the search. If the FindRecord method locates the record in the SQL Server table the record is returned in an array to the form to be displayed for editing or deletion as the case may be. Note that the Valid event passes an empty array arrE and a number variable intNoOfRecs. intNoOfRecs is not strictly needed in this example but illustrates that it is possible to return more than one record from the class to the form to be cached locally in a cursor (in this case, we are not using it). The code in the Valid event of the txtCustomerID textbox looks like this:

 

* Search for a Customer Record that meets the criteria and then display the record for editing

IF ISBLANK(THIS.Text)

      RETURN

ENDIF

 

LOCAL oCU As Object ,intNoOfRecs As Integer,lAnswer As Logical 

DIMENSION arrE[1,5]

arrE[1,1] = THISFORM.txtCustomerID.Value

arrE[1,2] = THISFORM.txtCustName.Value

arrE[1,3] = THISFORM.txtContact.Value

arrE[1,4] = THISFORM.txtNoOfAddr.Value

arrE[1,5] = THISFORM.txtNoOfTels.Value

intNoOfRecs = 0

SET UDFPARMS TO REFERENCE

SET CLASSLIB TO  dataaccess ADDITIVE

oCU = CREATEOBJECT(“CustMast”)

 

lAnswer = oCu.FindRecord(THIS.Value,arrE,intNoOfRecs)

IF intNoOfRecs > 0

      THISFORM.txtCustName.Value = arrE(1,2)

      THISFORM.txtContact.Value = arrE(1,3)

      THISFORM.txtNoOfAddr.Value = arrE(1,4)

      THISFORM.txtNoOfTels.Value = arrE(1,5)

ENDIF

 

1. Adding or Saving a Record: To add a record to our SQL Server table or save any changes we have made, we add a command button cmdAddRecord whose click event contains the following code:

 

LOCAL oCU As Object ,intNoOfRecs As Integer,lAnswer As Logical 

DIMENSION arrE[2,5]

arrE[1,1] = THISFORM.txtCustomerID.Value

arrE[1,2] = THISFORM.txtCustName.Value

arrE[1,3] = THISFORM.txtContact.Value

arrE[1,4] = THISFORM.txtNoOfAddr.Value

arrE[1,5] = THISFORM.txtNoOfTels.Value

intNoOfRecs = 1

SET UDFPARMS TO REFERENCE

SET CLASSLIB TO  dataaccess ADDITIVE

oCU = CREATEOBJECT(“CustMast”)

lAnswer = oCu.AddRecord(arrE,intNoOfRecs)

 

 

This code is straight forward. It populates an array arrE with the values entered in the controls of the form, then passes the array to the AddRecord method of our class that connects to the SQL Server table to save the record. It checks the table to see if the record already exists. If it does, it will save any changes you have made else, it will insert the new record using APPEND BLANK. This means that to amend an existing record and save changes to it, enter an existing ID in the CustomerID field to display the record, make changes to the record on the form, and then choose the AddRecord button to save the changes you have made.

1. Deleting an existing record: The cmdDelete button fulfills the ability to remove unwanted records from the SQL Server table. It calls the RemoveRecord method of the class passing to it the Customer ID entered in the txtCustomerID text box field. The code looks like this:

 

To delete a record, you would enter the ID of the customer whose record you want to delete in the txtCustomerID text box field to cause the record to display after which you would then choose or click the Delete button to delete the record.

1. Display a Blank Record Template: We have added the cmdNew button to enable us obtain a blank record template. Clicking this button clears away the currently displayed record thus allowing you to begin working on a new record. The code for the click event of this command button looks like this:

 

THISFORM.txtCustomerID.Value = “”

THISFORM.txtCustname.Value = “”

THISFORM.txtContact.Value = “”

THISFORM.txtNoOfAddr.Value = 0

THISFORM.txtNoOfTels.Value = 0

THISFORM.txtCustomerID.SetFocus

 

You will notice that this same button code is called from both the Save and Delete buttons through the single line: cmdNew.Click

 

Conclusion: This article demonstrates Visual FoxPro’s ability to access multiple external data sources by using the CursorAdapter. In this example, we have demonstrated being able to get at data stored in a SQL Server 2005 database. The data access is done by using a data access component stored in a Visual Class Library (VCX) to connect to the SQL Server 2005 database. Arrays are used to move data between the form and the data access class. While we primarily made us of ActiveX Data Objects (ADO) in our example, CursorAdapters allow you to use native Visual FoxPro (VFP) Data, Open Database Connectivity (ODBC) and Extensible Markup Language (XML). This means that the specific data access method you choose to get at external data would be tailored to the task at hand, the requirements of the particular system you are building and the particular database or type of data you wish to access. Even though we have deliberately kept our example simple and rudimentary to demonstrate the basic concept, you can easily build powerful enterprise level applications by combining other concepts such as local cursors to cache data on the form returned with arrays, implementing buffering with the data created by your CursorAdapter or combining the power and programmability of CursorAdapters to extend your typical data environment.

About the Author

Sylvester Alelele is a Senior Systems Analyst/Programmer and Group Head of Operations for Forest-Elephant Technology & Procurement Group Plc. He lives and works in Addis Ababa Ethiopia. He develops applications with Microsoft Visual FoxPro, Visual Basic and the .Net Framework, Oracle, Advantage Database Server and Ms SQL Server. He has over sixteen (16) years of experience building enterprise database solutions of all sizes

Modelagem e Render de Óculos com Alias Studio / Glass Modeling and Rendering by Alias Studio

Maya Techniques – Rendering 2D Effects in a 3D Environment $88.00 Maya Techniques I Rendering 2D Effects in a 3D Environment will allow you to leverage Mayas node-based architecture to spice up your renders and create effects you never thought possible. Explore several examples step-by-step to learn how you can simulate 2D-type effects and filters within Maya. Originally presented at Siggraph 2001, this - product will broaden your view of Mayas shading architec…

Maya DVD / 3DTrainer’s Lighting and Rendering / Over 2 hours of indepth Maya training. Maya DVD-Rom / Learn Maya the fast and easy way / Maya Training on DVD from the Pros / Step by step Maya tutorials / All Maya lesson are taught by an Autodesk certified instructor Light and shadow always surrounds us, giving form and feeling to the world we live in. Moving away from what looks computer animated into the realm of photo realistic is based within great lighting. This easy to follow DVD-Rom teaches you the same techniques used by Hollywood’s lighting professionals. Learn the complex science of light while also understanding it’s artistic importance. Use Maya’s…

Learning Maya 5: Rendering $1.84 There’s much more to rendering your final 3D image than hitting the “Render” button, especially in an application as deep as Maya. With options for everything from the color of shadows cast by lights to the size of the backplane on the virtual camera, tweaking the settings prior to final output can be daunting. In Learning Maya 5: Rendering, nearly everything that has to do with textures, shaders…

Learning Maya 6 | Rendering $5.65 This official book, produced by Alias, is your solution to creating stunning images in Maya 6. Explore textures, lighting, cameras, shading networks, and other core rendering issues. Each chapter includes a theoretical discussion of the concepts, combined with tested step-by-step tutorials that make complex topics easy to follow. Gain additional insight and assistance from the valuable chapter-by-…

Maya Version 3: Using Maya – Rendering Using Maya: Rendering describes how to give a scene a specific “look” by creating lights, shadows, and light effects, shading and texturing surfaces, setting up cameras and views, and creating a background. It also describes how to prepare for rendering, render a scene, and view rendered images….

Maya 7 the Worls Most Powerful Integrated 3d Modeling , Animation , Rendering Solution Maya 7 Education Maya 7 Continues Alias Tradition 0f 3d innovation this feature packed realise will help you realize your creatived visin faster and easier than ever before It contains : Modeling And texturing, Animation , Visual Effects, Brushed Based Technologies,Rendering , maya api SDk and mel , maya Unlimited 7, Maya fluids Efects , Maya cloth,Maya hair, Maya fur,maya Live, Productivity tools, Numerous pro…

Learning Maya: Nurbs Modeling Learning Maya Nurbs Modeling is your key to mastering organic modeling using NURBS surfaces. Working with a hand model, you’ll learn to create smooth, organic surfaces. This DVD will help you understand recommended workflows when working with NURBS models from the initial use of Image Planes to the final stages of Global Stitching….