ADO AND LIKE

[ASMA]

I`M OPENING A RECORDSET USING THE FOLLOWING SQL:

SQL$ = "SELECT COD FROM DESCRIPTION WHERE TEXT IS LIKE '*H*'

WHEN I USE IT WITH DAO I GET ANSWERS FOR TEXT LIKE AHU, ADHE ETC

BUT USING ADO, RS.BOF AND RS.EOF ARE BOTH TRUE. ANY IDEA?

 

[culshaja]

What RDBMS are you talking to? That statement will only work with Access. You will need to change the * to % for SQL Server (and I think Oracle).

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[ASMA]

SORRY, IT'S AN ACCESS DB, BUT DOESN'T WORK WITH '*' THOUGH ACCORDING TO THE DOCUMENTATION IT SHOULD. TKS.

 

[culshaja]

Can you post the code you are using?

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[MatsHulten]

Cutting and pasting the query to my Access gives a syntax error at the 'IS' statement. When I remove it (...TEXT LIKE '*H*'...) the query executes just fine.

The fact that you don't get an error when you populate the recordset indicates that the query you run simply does not return any records.
I would check the query itself, so that you are not querying the wrong table, fields etc.

Good Luck
-Mats Hulten

 

[ASMA]

HERE´S THE CODE

Dim CN As ADODB.Connection, RS As ADODB.Recordset

Set CN = New ADODB.Connection
Set RS = New ADODB.Recordset

CN.CursorLocation = adUseServer

STRCON = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBPATH & ";" & "Persist Security Info=False;" & "Jet OLEDBatabase Password=" & PASSWD

SQL = "SELECT COD FROM DESCRIPTION WHERE TEXTO LIKE '*H*'

(JUST ONE TABLE WITH COD (STRING), TEXTO (STRING) AND SITUATION(BYTE)


CN.Open STRCON

RS.Open SQL, CN

IF RS.EOF AND RS.BOF THEN ..... (ARE ALWAYS TRUE)

THE SQL QUESTION WORKS CORRECTLY USING DAO:
SET DB=OPENDATABASE(DBPATH)
SET RS=DB.OPENRECORDSET(SQL)
(IN THIS CASE RS.RECORDCOUNT IS 155). (I KNOW WITH THE SENTENCES IN ADO I'M GETTING JUST THE FIRST RECORD)



THANK YOU EVERYBODY IN ADVANCE.

 

[dpaulson]

SQL$ = "SELECT COD FROM DESCRIPTION WHERE TEXT IS LIKE '%H%'

This should return a recordset with an H anywhere in it.

To narrow this search down
SQL$ = "SELECT COD FROM DESCRIPTION WHERE TEXT IS LIKE '_H%'

This will return a recordset with the H as the second character

This is for access DB.
* and ? for DAO
% and _ for ADO
David Paulson

 

[ASMA]

Ok, it works.
Thank you very much David and everybody.