ADOCE and the recordset find method [Archive]

View Full Version : ADOCE and the recordset find method

JasonLJames

03-08-2003, 07:31 PM

Dear all,

I have successfully imported my database into my PPC and attached to it. I can navigate through the records and display their contents on the form. I am however having some difficulty in finding a particular record based on a text entry from the user. The code below demonstrates what I am trying to do:

Private Sub Command5_Click()
' Find specified record using text1.text
Dim cSQL As String
cSQL = "cTitle like " & Chr(39) & Text1.Text & Chr(39)
myRS.Find cSQL
DisplayRecord
End Sub

The database and recordset are defined and attached to as:

Public myDB As ADOCE.Connection
Public myRS As ADOCE.Recordset

and

Set myDB = CreateObject("ADOCE.Connection.3.0")
myDB.ConnectionString = "data source = \My Documents\cd.cdb"
myDB.Open
cSQL = "SELECT ID, cTitle FROM tblCD ORDER BY cTITLE;"
Set myRS = CreateObject("ADOCE.RecordSet.3.0")
myRS.Open cSQL, myDB, adOpenDynamic, adLockOptimistic

Everything works fine until I attempt to execute the find. I then get an application error An error was encounteered while running this program: Unspecified Error

I am running the code on my PPC, not the emulator.

Any help or thoughts would be greatly appreciated.

Many thanks,

Jason.

Doug Haacke

03-08-2003, 08:52 PM

Jason:
I'm a C++ programmer, so I'm not really qualified to answer your question, but I see some potential pitfalls that I'll pass along...

If the user was to enter the text: Jason's program
The SQL statement will fail with the message you mentioned because the string has a quote in it. You'll need to write a little function that converts single quotes to two quotes for it to work. Example: "Jason's program" would become "Jason''s program". Note that the double quotes is actually two apostrophes and is two characters, not chr(34).

Secondly, is it possible the find is executing, but not returning any records? I see you call DisplayRecord after the find. Does it test to see if myRS.EOF and myRS.BOF are false?

Lastly, the string: cTitle LIKE 'Jason'
will only return records where the field cTitle has the word, and only the word, Jason in it.
The string: cTitle LIKE 'Jason%'
will return records where the field cTitle BEGINS with the word Jason and...
The string cTitle LIKE '%Jason%'
will return records where the field cTitle has the word Jason anywhere in it.

As I finished typing that, I seem to remember you use an asterisk instead of the percent sign in VB... but you get the idea...

Here's a routine to pad those quotes I was talking about:

Function padQuotes( instring )
Dim bodybuild
Dim bodystring
Dim Length
Dim i

bodybuild = ""
bodystring = instring
Length = Len(bodystring)
For i = 1 to length
bodybuild = bodybuild & Mid(bodystring, i, 1)
If Mid(bodystring, i, 1) = Chr(39) Then
bodybuild = bodybuild & Mid(bodystring, i, 1)
End If
Next
bodystring = bodybuild
padQuotes = bodystring
End Function

To use it (using your code), try:

cSQL = "cTitle like " & Chr(39) & PadQuotes(Text1.Text) & Chr(39)

Hope that helps, and good luck!

-Doug

Peter Foot

03-10-2003, 07:27 PM

As far as it is documented (which is not very well unfortunatly) the ADOCE Find method does not work with Pocket Access .cdb files, only with other OLEDB providers such as SQL Server CE.

The alternative is to open a recordset with a SELECT FROM WHERE LIKE query.

Peter

JasonLJames

03-10-2003, 07:40 PM

Hi Peter,

I have spent all weekend trying to sort this out and I had kind of reached the same conclusion. Thanks for the info. Where can I get SQL Server CE from? Where can I find out about how it works and how to use it? I have no experisnce with SQL Server only MS Access databases?

Many thanks again,

Jason.

Peter Foot

03-13-2003, 08:04 PM

JasonLJames

03-13-2003, 11:15 PM

Thanks for the help. I have bought a book on eVB and also eVB and databases so I should be able to get where I want with all of that. I need to get a couple of downloads but after that I should be on my way.

Regards,

Jason.