sql problem in vb 6.0

[JackCrackers]

Hello

I have a database with one table, the table has 3 fields, including the key index field.

I wrote a query in the Access query designer. The sql view of the query looks like this:

SELECT tblFolders.FolderName, tblFolders.Note
FROM tblFolders
WHERE (((tblFolders.Note) Like "*fish*"));

When I run this query from Access it works as expected returning all the records that have "fish" in the Note field.

I wrote some code in VB 6.0 so that I could type the replaceable parameter in a text box and see the results in a datagrid control.

The code is based on a single form that has the following controls on it:
1 adoc
1 datagrid
1 textbox
1 commandbutton

Here is the code for the command button:

Private Sub CommandFetch_Click()
filestring = "c:\temp\jobs.mdb"
v$ = Trim(TextSQL.Text)

'sq$ = "SELECT tblFolders.FolderName, tblFolders.Note From tblFolders WHERE (((tblFolders.Note) Like '" & "*" & v$ & "*" & "'));"

sq$ = "SELECT tblFolders.FolderName, tblFolders.Note From tblFolders WHERE (((tblFolders.Note) Like " & Chr(39) & "*" & v$ & "*" & Chr(39) & "));"

db = filestring
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db
Set cn = New ADODB.Connection
cn.Open strconn
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filestring & ";"
Adodc1.RecordSource = sq$
Set DataGrid1.DataSource = Adodc1
Form1.Adodc1.Refresh
End Sub

Neither of the 2 variations of sq$=.... return any results. I have also tried various combinations of chr(34) and string literals using " etc.

There is no error, just no results

All help will be much appreciated

Thank you

Jack

 

[JackCrackers]

>>> SELECT tblFolders.FolderName, tblFolders.Note From tblFolders WHERE (((tblFolders.Note) Like '*fish*'));

This is what was in the box, but no returned records
John

 

[pbaldy]

In ADO you want % as a wildcard instead of *.

 

[JackCrackers]

The solution is % instead of *.

Note is a text field and there are 2 instances of fish, both records are now being returned to the datagrid

Thank you both very much
John

 

[pbaldy]

No problem; welcome to the site by the way!