Using Visual Basic Data Controls with the SQL Engine 2.0
     Introducing the R:BASE SQL Engine 2.0
     With the release of the R:BASE SQL Engine 2.0, programming R:BASE 
     applications in a Windows environment using Visual Basic is easier 
     than ever. The new release of the SQL Engine is ODBC Level 1 
     compliant and supports Visual Basic's data control. The data control 
     makes it possible to create Visual Basic applications that connect to 
     an R:BASE database and retrieve records without programming. The data 
     control performs the following functions:
     Connects an R:BASE database
     Retrieves a group of records based on any valid SQL query
     Passes data to bound controls for display or update
     Facilitates built-in error checking
     Disconnects from the database on exit
     In the past, these tasks required programming code. The data control 
     eliminates much of the code and therefore reduces development time 
     significantly. Accessing R:BASE is very straightforward - you can 
     spend time developing the look of your application rather than writing 
     the code. You need the Professional edition of Visual Basic 3.0, since 
     the Standard edition does not have the data control. 
     This article demonstrates techniques for using the data control and 
     shows forms you can create with little or no Visual Basic code.
     Set Up the ODBC Administrator
     In order to use the data control, the data source (the R:BASE database 
     name), must be added through the ODBC Administrator. The ODBC 
     Administrator icon was installed into the SQL Engine program group. 
     Start the ODBC Administrator and select Add. Choose the driver named 
     "R:BASE45_16". Then enter the database name as prompted. Be sure to 
     specify the complete drive and path name for the database; for 
     The data source (the R:BASE database name) is added to the file 
     ODBC.INI. During installation of the SQL Engine, the driver 
     information was added to the ODBCINST.INI file. Refer to page 3-7 of 
     the SQL Engine User's Manual for more information about setting up 
     Define Properties for the Visual Basic Data Control
     The data control is the Visual Basic object that connects to your 
     R:BASE database and identifies the data to retrieve. Text boxes are 
     the objects that hold the column data - one text box per column. The 
     properties of the text box refer back to the data control. A form can 
     have one or more data controls. You can even connect multiple R:BASE 
     databases by using different data controls.
     Place a data control object on your form by double-clicking on the 
     data control icon on the tool bar. The database and table are set 
     through data control properties. To display the properties for a data 
     control, click on the data control, then choose Windows|Properties 
     from the Visual Basic menu. Or, if the Properties window is already 
     displayed, select it. 
     You must set three properties for the data control: Connect, Options 
     and RecordSource. These properties can be set at design time or 
     runtime. However, if they are not set at design time, some 
     programming code needs to be written. Refer to the Visual Basic 
     reference manuals for examples. The DatabaseName property is left 
     blank; it is not applicable to connecting an R:BASE database.
     The Connect Property
     The Connect property passes information to the Engine that is 
     necessary for connecting to the database, which is connected through 
     ODBC. The following arguments can be included in the Connect 
     ODBC - Indicates that the database is an ODBC data source
     DSN - The path and name of the database 
     UID - The userid of the person using the database
     PWD - The userid password, if applicable
     For example:
     Note that each argument is separated by a semi-colon (;). The only 
     argument that is required is ODBC;. Any of the remaining arguments 
     that are not specified at design time can be explicitly set during 
     run time. The arguments that are not set at design time or run time 
     will be prompted for at run time.
     When you enter just "ODBC;" in the Connect property, the menu of 
     installed Data Sources displays and you can pick the database name. 
     When the userid/password box displays, enter a userid if applicable. 
     To set up your application so that it will not prompt but 
     automatically connect to a database, include all parameters as part 
     of the Connect property. If no userid or password is defined, enter 
     Since the RecordSource property specifies a table name, you should 
     include at least the database name in the Connect property. That 
     makes sure your users don't select the wrong database from the ODBC 
     Data Sources list. If you have no passwords assigned on the database, 
     include the UID and PWD arguments as well so that your users won't 
     be prompted at all. 
     The RecordSource Property
     The RecordSource property defines a group of records from a table. 
     Valid record sources consist of the name of a database table or the 
     text of a SQL query. One of the nice features about the RecordSource 
     property is that it can be dynamically changed while an application is 
     running. This, of course, takes a couple of lines of code, but is 
     fairly straightforward.
     The easiest way to set up the RecordSource is to simply enter a table 
     name. Alternatively, you can use a SELECT command. For example:
     SELECT * FROM employees
     You can use any SELECT command that you can enter at the R>, although 
     some require use of the SQL pass-through that is set with the Options 
     property. For complex, multi-table queries, it is better to create a 
     view in R:BASE and then select from the view in the RecordSource 
     property. A view must be used with the SQL pass-through.
     The Options Property
     The Options property sets properties for the data (recordset) 
     retrieved by the data control. The default value for the Options 
     property is 0. This value allows the recordset to be modified. Make 
     sure the Options property is set to 0 when designing an application 
     where users will insert new rows, delete rows, or edit data. 
     Setting the Options property to 64 enables SQL pass-through 
     (DB_SQLPASSTHROUGH). The SQL command is passed directly to the 
     underlying database engine for processing; Visual Basic does not try 
     to process or interpret the command. Using the SQL pass-through allows 
     you to build more complex SELECT commands, use R:BASE SuperMath 
     functions and even use R:BASE command abbreviations (such as SEL 
     instead of SELECT). Accessing data from an R:BASE view requires the 
     Options property be set to 64. The recordset cannot be modified when 
     the Options property is set to 64.
     Bound Controls
     Bound controls are Visual Basic objects that can receive information 
     passed to them via the data control. Bound control means that the 
     object is connected to another object. If the data displayed in these 
     objects is changed, the table is automatically updated when a new 
     record is selected (when Options property of the data control is set 
     to 0). This makes it possible to design forms that view and edit data 
     without program code. A text box is the bound control that is 
     generally used with a data control. The properties that must be set 
     for the text box are the DataField and the DataSource. Unlike the 
     properties of the data control, these properties must be set at 
     design time.
     DataSource Property
     The DataSource property contains the name of the data control 
     supplying the data. Specifying the DataSource binds the text box to 
     the data control. If the data control has been placed on the form, 
     double-clicking on the DataSource property will automatically fill in 
     a valid data control name. If more than one data control exists, 
     successively double-clicking will display additional data controls 
     associated with the form. 
     DataField Property
     The DataField property contains the name of one of the columns 
     returned by the table specified in the RecordSource property of the 
     data control. Each bound object (text box) can contain only one field 
     from the record source. If the DataSource property is filled in, 
     double-clicking on the DataField produces a list of columns from the 
     table and database specified by the data control. 
     Example 1: A Multi-Table Form with a Two-Table Select
     This first example is a multi-table form based on the customer and 
     contact tables in the Concomp sample database. This form contains 
     one data control and is easy to create with a multi-table SQL query 
     using SQL pass-through (Options=64). The best part about this example 
     is that it requires absolutely no programming code. Just follow the 
     steps below.
     1. Begin Visual Basic and start with a new form.
     2. Place a data control object on the form and define the properties 
     as follows:
     Connect         ODBC;DSN=C:\rbfiles\Concomp;UID="none";PWD="none"
     Options         64 (Numeric option specifying DB_SQLPASSTHROUGH)
     RecordSource    SEL t1.custid, t1.company, t1.custaddress, +
     t1.custcity,t1.custstate, t1.custzip, t1.custphone, +
     (t2.contlname + ',' & t2.contfname), t2.contphone, t2.continfo FROM +
     customer t1, contact t2 WHERE t1.custid = t2.custid
     Notice the RecordSource property looks just like the SELECT command 
     you enter at the R> prompt in R:BASE, including command abbreviations, 
     expressions, and correlation names.
     3. Add text boxes for each field returned by the query and adjust the 
     properties as follows:
     DataSource              Data1 (the Name of the data control)
     DataField               one of the fields returned by the query, for 
                             example custid.
     The DataField property indicates which field displays in the located 
     text box. Locate a text box and specify the appropriate DataField 
     property for each column selected in the RecordSource. When done, 
     your form should look similar to figure 1.
     That's all there is to it. Run the form and test it out by scrolling 
     through the records. Because SQL pass-through was used, the data is 
     not editable. 
     Example 2: A Multi-Table Form that Links Two Data Controls
     The first form example was fairly limited. In order to change to a new 
     customer and view the contacts associated with it, you must scroll 
     through all of the records. This second form, on the other hand, 
     allows the user to change to a different customer and view all 
     contacts associated with that customer. This second example is more 
     complex in that it requires two data controls to be set and a few 
     lines of program code. As each new company is selected by clicking on 
     the data control arrows, a new value for the RecordSource property is 
     set by using the Visual Basic REFRESH method. When the user clicks on 
     the data control associated with the customer table, the text box 
     containing the customer id number, custid, changes. To capture the new 
     id and retrieve the matching records from the contact table, enter 
     the following code as a change procedure for the custid text box. This 
     code executes whenever the custid value changes.
     'Capture the new value of custid for the next record in the customer 
     Vcustid$ = Text1.text
     'Redefine the RecordSource for Data2 using the new value of the 
     Data2.Recordsource = "SEL * FROM contact WHERE custid = " + Vcustid$
     'Create the new recordset
     1. Set up the form as in figure n and add the above code to the Change 
     action on the text box for custid
     2. Define the RecordSource and Options for each data control:
     Data1.RecordSource = customer
     Data1.Options = 0
     Data2.RecordSource = SEL (contlname + "," & contfname), contphone, 
     continfo FROM contact
     Data1.Options = 64
     The data in the Customer table can be modified; the data in the 
     Contact table is read only.
     Example 3: A Visual Basic Gateway 
     For many years, users of R:BASE have wanted to be able to 
     simultaneously connect to two R:BASE databases and be able to transfer 
     data between them, but there was no way to connect to two databases at 
     the same time. The Visual Basic data control now makes this easy to 
     This third example uses a data control to connect to one database and 
     custom code to connect to a second database. Using the data control, a 
     record in the source database is selected and subsequently inserted 
     into a destination table of the same name in a second database. Since 
     this form uses SQL Engine function calls, the HEADER.BAS and 
     SQLAPI.BAS files that come with the SQL Engine must be included in the 
     project. For information about the SQL Engine functions, refer to your 
     SQL Engine documentation. Follow the steps below to set up the form.
     Set Up The Source Database
     1. Starting with a new form, add the SQLAPI.BAS and HEADER.BAS files 
     to the project.
     2. Set up a data control and edit the properties as described above in 
     Examples 1 and 2 to specify the database and table.
     3. Set up the text boxes and edit the properties to bind the text 
     boxes to columns returned by the RecordSource.
     Set Up The Destination Database
    1. Add text boxes for each field that will be transferred to the 
    destination database. These text boxes are not associated with a data 
    control, so no special properties need to be set.
     2. Add command buttons to connect the database, insert the row and 
     then disconnect the database. Add the associated code as shown below.
     Code For Form Load action
     Panel3D1.Caption = "C:\rbfiles\concomp OPEN"
     Command2.Enabled = False
     Command3.Enabled = False
     Code for Connect command button
     Sub Command1_Click ()
       Mousepointer = 11
     ' First allocate an environment handle
       retcode = SQLAllocEnv(phenv&)
       If retcode <> SQLSuccess Then
         MsgBox "SQLAllocEnv Failed"
       End If
     'Next allocate a connection handle
       retcode = SQLAllocConnect(phenv&, hdbc&)
       If retcode <> SQLSuccess Then
         MsgBox "SQLAllocConnect Failed"
       End If
     'Get the name of the database and create the connect string
       szDSN$ = InputBox$("Enter database name", "Connect Database", 
       szUID$ = "NONE"
       cbUID% = Len(szUID$)
       szAuthStr$ = "NONE"
       cbAuthStr% = Len(szAuthStr$)
       cbDSN% = Len(szDSN$)
       Mousepointer = 0
     'Connect the database
       retcode = SQLConnect(hdbc&, szDSN$, cbDSN%, szUID$, cbUID%, 
       szAuthStr$, cbAuthStr%)
       If retcode <> SQLSuccess Then
         retcode = SQLError(phenv&, hdbc&, phstmt&, szSqlState$, 
         pfNativeError&, szErrorMsg$, cbErrorMsgMax%, pcbErrorMsg%)
         Command1.Enabled = False
         Command2.Enabled = True
         Command3.Enabled = True
         Panel3D2.Caption = "c:\rbfiles\transfer\concomp2 OPEN"
       End If
       Mousepointer = 0
     End Sub
     Code For Insert command button
     Sub Command2_Click ()
     'If user selects "Insert Row", the values are displayed in the
     'destination database section of the form
       Text5.Text = Text1.Text
       Text6.Text = Text2.Text
       Text7.Text = Text3.Text
       Text8.Text = Text4.Text
     'Build the Values list used in the INSERT command. The current 
     'record in the source database is placed into variables
       vcustid$ = "(" + Text1.Text + ","
       vcontlname$ = Text2.Text + ","
       vcontfname$ = Text3.Text + ","
       vcontphone$ = Text4.Text + ")"
       vresp% = MsgBox("Insert new row...are you sure?", 4)
     If vresp% = 6 Then
     'Allocate the statement handle
       retcode = SQLAllocStmt(hdbc&, phstmt&)
       If retcode <> SQLSuccess Then
         MsgBox "SQLAllocStmt Failed"
     'Build the command  
       szSqlStr$ = "INSERT INTO CONTACT (custid, contlname, contfname, 
       contphone) VALUES " + vcustid$ + vcontlname$ + vcontfname$ + 
       vcontphone$ cbSqlStr& = Len(szSqlStr$)
     'Insert the row
         retcode = SQLExecDirect(phstmt&, szSqlStr$, cbSqlStr&)
         If retcode <> SQLSuccess Then
           MsgBox "INSERT Failed", 48
           retcode = SQLError(phenv&, hdbc&, phstmt&, szSqlState$, 
           pfNativeError&, szErrorMsg$, cbErrorMsgMax%, pcbErrorMsg%)
           MsgBox "Successful Insert of 1 Row", 64
         End If
     'Free the statement handle
         retcode = SQLFreeStmt(phstmt&, SQL_DROP)
         If retcode <> SQLSuccess Then
           MsgBox "SQLFreeStmt Failed"
           Text5.Text = ""
           Text6.Text = ""
           Text7.Text = ""
           Text8.Text = ""
         End If
       End If
     End If
     End Sub
     Code For Disconnect command button
     Sub Command3_Click ()
       retcode = SQLDisconnect(hdbc&)
       If retcode <> SQLSuccess Then
         MsgBox "SQLDisConnect Failed"
       End If
       retcode = SQLFreeConnect(hdbc&)
       If retcode <> SQLSuccess Then
         MsgBox "SQLFreeConnect Failed"
         Command1.Enabled = True
         Command2.Enabled = False
         Command3.Enabled = False
         Panel3D2.Caption = "c:\rbfiles\transfer\concomp2 CLOSED"
       End If
     End Sub
     3. Add error checking as necessary. The SQL Engine sample application 
     has an error checking sub-routine you can use as a template.
     At runtime, the data control builds a recordset and binds the columns 
     to the text boxes. Code connects to the second database when the "Open 
     DB" command button is clicked. When the "Insert Row" command button is 
     clicked, values are transferred to the destination text boxes. Based 
     on these values, an SQLExecDirect command string is built that inserts 
     the row into the destination database. Of course, many different 
     variations of this form may be used to transfer data from a source 
     database to a destination database. Evaluate your business situation 
     and change the form and code accordingly.
     Review the R:BASE SQL Engine 2.0 README.TXT file for additional 
     information on using Visual Basic data controls.