Modifying the Application
We'll modify our eMbedded Visual Basic StatCalc application so that it now stores values in a database as well as the list box. We'll also rely on the power of SQL to perform some of our calculations; specifically, the arithmetic mean can now be calculated easily using the SQL AVG() function.
Previously, StatCalc trapped the btnAddValue click event and added that value to the list box. In this example, StatCalc will also need to do a database insert before calling the displayMean and displayStdDeviation procedures. The new btnAddValue_Click sub looks like this:
Private Sub btnAddValue_Click() lstValues.AddItem (txtValue.Text) addRecord( CDbl(txtValue.Text) ) displayMean displayStdDeviation End Sub
Note two things in the code snippet just above. First, we've added a new addRecord function that will accept the input data and insert it into the database. Second, note the typecasting of the input data value. Because this is a simple example, we'll assume that the user is only adding floating-point numbers. In a real world app, better data-handling logic would be required to prevent the user from entering bad data.
The following listing for the addRecord subroutine illustrates the first use of the ADOCE library in this application:
Private Sub addRecord( ByVal newValue ) Dim statConn Set statConn = CreateObject("ADOCE.Connection.3.0") statConn.Provider = "ASAProv" statConn.ConnectionString = "Data Source=STATCALC" statConn.Open statConn.Execute "insert into STATDATA (DATA) values (" & newValue & ")" statConn.Close End Sub
Likewise, our calculateMean function can be modified to access the database (as opposed to examining the list and performing the calculation manually):
Private Function calculateMean() Dim ValuesCount Dim Mean Dim SQL Dim statConn Dim statRS Set statConn = CreateObject("ADOCE.Connection.3.0") statConn.Provider = "ASAProv" statConn.ConnectionString = "Data Source=STATCALC" statConn.Open SQL = "select AVG(DATA) from STATDATA" Set statRS.ActiveConnection = statConn statRS.Open SQL, statConn, 0, 1, 1 If Not statRS.EOF Mean = statRS.Fields(0) Else Mean = 0 End If statRS.Close statConn.Close calculateMean = CDbl(Mean) End Function
If you've done any ADO programming (as a result of VC++, VB, or ASP development projects), you'll recognize that the code looks nearly identical to the ADO you're familiar with. Above, we basically execute a query in order to retrieve the sample set's average value. Both queries were executed against the STATCALC data source, which in our case was a Sybase ASA database running under Windows CE.