Creating Applications for Handheld Devices Using eMbedded Visual Basic

Data Access with eVB

Next, we're going to learn how to create a Database Connection and retreive data using an SQL query enabling you to create a simple data access program for a PDA.

Before you start you must create an Access database on the desktop PC called Products.mdb.  Create a table called Products with three fields Desc, Price and ProdID where ProdID is the primary key of type numeric.

Drag and drop this mdb file into the MyMobileDevice folder and you will see the ConfirmFileConvert Dialog, follow the instructions to convert your database into PocketAccess format and synchronise it with the database you have created on your desktop.

Form Layout

We shall now create a simple application for the PocketPC which will access this data and display it on a form.

1.  Open Microsoft eMbedded Visual Basic and choose “New Windows CE For Pocket PC 2002 Project”.

2.  Add a Label to the form and change it’s name property to lblName, it’s caption property to Simple Data Access Example, it’s backcolor property to &H00E0E0E0& and it’s alignment property to 2 - vbCenter

3.  Add another label to the form and set it’s name = lblEnter, caption = Enter a Product ID, backcolor = &H00E0E0E0& and alignment = 1 - vbRightJustify

4.  Add a textbox to the form and set it’s name = txtEnter and clear it’s text property

5.  Change the forms caption property to something like “Product Inventory” and it’s backcolor property to &H00E0E0E0&

6.  Add a frame component to the form and change it’s caption to “Details” and it’s visible property to false

7.  Add another label but instead of placing it anywhere on the form, make sure to drag it onto the frame.  Change it’s backcolor to white, alignment to vbRightJustify and it’s caption property to “Description”.  Repeat this process but change the label’s caption property to “Price”

8.  Add a textbox to the frame clearing it’s text property and changing it’s name to txtDesc.  Repeat this process for txtPrice. 

9.  Add a Button to the Form changing it’s style property to 1 – vbButtonGraphical, it’s BackColor to &H00FF8080& and it’s caption to “GO”.  The form should look something like:

 

The Code

10.  Add an event handler for the button – double click on your button; you should see the code editor with the following text:

Option Explicit 

Private Sub Command1_Click()

End Sub

Private Sub Form_OKClick()
App.End
End Sub

11.  Declare global variables - After the “Option Explicit” statement insert the following declarations:

Dim paramSQL As String
Public conndb As ADOCE.Connection
Dim rs As ADOCE.Recordset
Public desc As String
Public price As String
Dim tempno as String

12.  Open a connection to the database and point the recordset object at it by inserting the following code underneath the line - Private Sub Command1_Click():

Set conndb = CreateObject("ADOCE.Connection.3.0")
conndb.ConnectionString = "data source = \My Documents\Products.cdb"
conndb.Open
Set rs = CreateObject("ADOCE.RecordSet.3.0")

13.  Now that the connection and recordset have been created we can generate an SQL statement to retrieve records:

tempno = txtEnter.Text
paramSQL = "select Desc, Price from Products where ProdID = '" & tempno & "'"
rs.Open paramSQL, conndb, adOpenForwardOnly, adLockReadOnly

14.  Check that the record has been found, if it has then make the Frame visible and display the details in our textboxes:

If Not ((rs.EOF = True) And (rs.BOF = True)) Then
Frame1.Visible=True
txtDesc.Text = rs.Fields(0)
txtPrice.Text = rs.Fields(1)
End If
Rs.Close
conndb.Close
Set conndb = Nothing

The whole code should look something like this:

Option Explicit
Dim paramSQL As String
Public conndb As ADOCE.Connection
Dim rs As ADOCE.Recordset
Public desc As String
Public price As String
Dim tempno as String

Private Sub Command1_Click()
tempno = txtEnter.Text
Set conndb = CreateObject("ADOCE.Connection.3.0")
conndb.ConnectionString = "data source = \My Documents\Products.cdb"
conndb.Open
Set rs = CreateObject("ADOCE.RecordSet.3.0")
paramSQL = "select Desc, Price from Products where ProdID = '" & tempno & "'"
rs.Open paramSQL, conndb, adOpenForwardOnly, adLockReadOnly
If Not ((rs.EOF = True) And (rs.BOF = True)) Then
Frame1.Visible = True
txtDesc.Text = rs.Fields(0)
txtPrice.Text = rs.Fields(1)
End If
Rs.Close
conndb.Close
Set conndb = Nothing
End Sub

Private Sub Form_OKClick()
App.End
End Sub

15.  To run this application and see the results on your handheld device select  File -> Make Project1.vb…, the standard Windows dialog box will appear and you can save your application in the chosen folder.  If you are running the PPC 2003 O/S it is not possible to run your application using the emulator.  It is necessary therefore to open windows Explorer and navigate to the Mobile Device folder, ensuring your PDA is in its cradle.  Drag and drop the Project1.vb application from its current location onto this Explorer Window, the application is now ready to use.  To view this first application select Start -> Programs -> File Explorer on your hand-held device, navigate to the My Documents folder and click the Project1 icon.

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.” - Antoine de Saint Exupéry