Searching database in ADO using 'like' in find method

[atumuluri]

I was trying to search a database with matching operator 'like' using ADO on a Oracle database.

Adodc1.Recordset.Find "[EMPNAME] like '" & _
Text1.Text & "*" & "'".

This works fine soon after you enter a character in Textbox. The trigger I am using is Text1_Change().Suppose empname is "ASHOK", just you can reach this record by entering first letter A in the textbox. But my point is I unable to simulate the same on numeric field i.e EMPNO.

Adodc1.Recordset.Find "[EMPNO] like '" & _
Text1.Text & "'"
In the case above no characters like '*' are allowed for search pattern. I am getting run time error in case I use character '*' or '%'. Also with this there is another problem i.e I have to enter the full empno. For ex say empno is 664. The same logic as I explained in finding empname does not work here by entering just 6. Unless you enter all three numbers 664, the pointer does not go to this record. In that case my search will not be efficient. The same works if you use access database using Findfirst method using DAO.

My second question is I did not find method .findnext in ADOs(even not in ADO 2.1). But the same available in DAO.

Can anyone suggest me?

Thanks
Ashok

[Lothar Haensler]

you cannot use LIKE for numeric fields. But, you can convert the numeric field to a char field.
I don't know about ORACLE, but in SQLServer you could write code like:
...where convert(varchar(6),empno) like "64*"
I guess there is an equivalent function in oracle.
If that doesn't exist you can write code like:
where empno > ...

And yes, there is not FindNext in ADO.

[atumuluri]

There is a convertion facility using function TO_CHAR in Oracle. I will try with this.

But how to implement as you said with covert if it exists in Oracle also.

Since I am using find method with the input text like:

Adodc1.Recordset.Find "[AU_ID] like '" & _
Text1.Text & "'"

Thanks
Ashok

[atumuluri]

Hi,
Here is the trick. I have changed the ADO control's recordsource properties like this:
Opted for adcmdtext and the SQL text is

Select to_char(empno) as empno,empname,to_char(deptno) as deptno from emp;

It works.

Thanks
Ashok