Development | Database

Introduction to Pocket PC databases using ADOCE and eVB.
Written by Derek Mitchell  [author's bio]  [read 111933 times]
Edited by Derek

   

Page 1  Page 2  Page 3 
[Download the pdf]

Well this is always a good litmus test of your development aptitude - just how long it takes to try setting up a database in any new application environment. If you are itching to get to that stage this is just the tutorial for you. This simple tutorial will show you how to add and delete a database, create and drop tables and insert data into a table.

What you need to get started

• install eMbedded Visual Tools
• Pocket PC docked

Creating the Project

For this tutorial I'm going to depart from previous formats and make the project source available for download. This way we can fast-track creating the project and I can walk you through the code. I'm presuming at this stage of development you have mastered the techniques of adding Project components and references. For this project we will need to select the CE ADO control and the CE File System control references. In addition you will need to add the CE File System control to the project.

Project Overview

After downloading and running the eVB project on your Pocket PC you should see the following screen:

The general idea here is that each of the command buttons will perform a discrete task. In this tutorial we will:

• create a database
• create a table
• insert rows into the table
• delete the table
• finally delete the database

For each of the above tasks we will walk through the code. There is limited error trapping in this demo, the goal is to gain some familiarity with the basic ADOCE objects and methods. Once you have command of these concepts you can perform the task of bomb-proofing the application by catering for any and all contingencies. Let's get started.

Creating an ADOCE database

It's always advisable to create the database before trying to add any records so click the Create DB button. You should now see the following screen:

Now let's take a look at the code that was executed:

Private Sub cmdCreateDB_Click()
Dim rs, rc

  'if it exists ask re deletion
  If DBExists(gDBFileSpec) = True Then
    rc = MsgBox("Overwrite database " & gDBFileSpec & " ?", vbYesNoCancel, "Database already exists")
    If rc = vbYes Then
      'clean up current database connection
      connClose
      'delete the file
      FileSystem1.Kill gDBFileSpec
      txtDB.Text = gDBFileSpec & " deleted!"
    Else
      Exit Sub
    End If
  End If

The first thing that we do before creating the database is to check whether the database already exists. If the database already exists then we close the database connection and delete the associated database file name. The database file name and path (filespec) is assigned in the general declaration area of the Form: Const gDBFileSpec = "\My Documents\deVBuzz.cdb". Note we could have used the DROP DATABASE syntax to remove the database, but killing it appealed to me in this instance. Six of one! The DBExists routine simply checks to see whether the database file exists in the CE file system. On to creating the database.

  'go ahead and create the database
  On Error Resume Next
  Set rs = CreateObject("ADOCE.Recordset.3.0")
  rs.Open "CREATE DATABASE '" & gDBFileSpec & "'"
  rs.Close
  Set rs = Nothing
  On Error GoTo 0

Now we create the database using the ADOCE recordset object. After we have instantiated the recordset we pass it the filespec of the database we want to create. The filespec includes the path and is mapped from the device root directory. To create the deVBuzz.cdb in the My Documents folder we use '\My Documents\deVBuzz.cdb'. Don't forget the .cdb extension when creating your own databases. To verify the existence of the file use the ActiveSync Explorer to explore the file system on your device. All things being equal you will find the deVBuzz.cdb file in the My Documents folder.

  If DBExists(gDBFileSpec) = True Then
     txtDB.Text = gDBFileSpec & " created!"
  End If

End Sub

Finally we verify that the database file exists and update our status text.

Creating a table

Now click on the Create Tbl button. You will see the following screen:

Private Sub cmdCreateTbl_Click()
  ExecSQL "CREATE table TestTable (fldTxt text, fldInt integer)", "TestTable created.", "Err: TestTable was not created."
End Sub

Looking at the code behind this button we will see that a SQL statement is passed to the ExecSQL routine. We have just created a table in the database using the SQL statement 'CREATE table TestTable (fldTxt text, fldInt integer)'. This table has two fields; the first one called fldTxt will be a text field and the second one an integer field, called strangely enough, fldInt. We now need to look at the ExecSQL routine in order to examine exactly how this request was carried out; but before we do that let's take a quick look at the connOpen routine which opens a connection to the database.

Function connOpen() As Boolean
  On Error Resume Next
  connOpen = True
  If conn Is Nothing Then
    Set conn = CreateObject("ADOCE.Connection.3.0")
    conn.Open gDBFileSpec
    If conn.Errors.Count > 0 Then
      MsgBox "errors in connOpen", vbOKOnly
      DispErrors
      'connClose
      connOpen = False
    End If
  End If
  On Error GoTo 0
End Function

In this project we are opening and closing database connections each time we perform a database operation. Note that this is an expensive operation timewise and you would normally design your app to batch database operations, but given the nature of this tutorial it makes sense to open and close the database, since each time we open the database connection we are also checking that the database still exists! The connOpen routine ensures we have an open active connection to our database. The conn object in question is the public form variable: Public conn As ADOCE.Connection. This ADOCE connection object is used by all our other ADOCE operations and is the conduit to our database. You will also note the reference to the DispErrors routine and the connClose which we will go into later.

Now back to ExecSQL, which in case you have forgotten by now, was the routine that we used to execute out CREATE TABLE SQL.

Function ExecSQL(paramSQL As String, paramSuccess As String, paramErr As String) As Boolean
  If DBExists(gDBFileSpec) = True Then
    connOpen

If the database exists then open a connection to the database.

    On Error Resume Next
    conn.Execute (paramSQL)

Next we execute the SQL statement using the ADOCE connection object. Using the connection object to execute SQL in this fashion is perfect for those database operations for which we do not expect any return data, as we do here in creating our table.

    On Error GoTo 0
    'check for errors
    If conn.Errors.Count > 0 Then
      'DispErrors
      ExecSQL = False
      txtDB.Text = paramErr
    Else
      ExecSQL = True
      txtDB.Text = paramSuccess
    End If

Next we check the connection Errors collection for any errors. To gracefully handle the errors uncomment the DispErrors reference.

    connClose
  Else
    MsgBox "Database " & gDBFileSpec & " does not exist!", vbOKOnly, "No database"
  End If
End Function

Finally we close the connection to the database. Note that we would display the error message parameter passed into the ExecSQL routine if the operation failed. We have created a table called TestTable in our database.

Next Page 

Back to Database | [Article Index]