Data Access Using ADOCE

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.