Page 1
Page 2
Page 3
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