Like in SQL returns Null recordset 1

[RonRepp]

Hi all:

I have an app that writes SQL strings from criteria that the user chooses. Everything works great except the "Like" filter. If I copy and paste the SQL directly into Access, it works, but doesn't work with the ADO Recordset to datagrid.

[code>>SQL Statement]
SELECT
[ImportedTable1].[ID],
[ImportedTable1].[Keywords]
FROM ImportedTable1
WHERE
[ImportedTable1].[Keywords] LIKE '*bulb*'
[/code]

[code>>to populate grid]

Private Sub PopulateGrid()
Dim Con As ADODB.Connection
Dim myRS As ADODB.Recordset

Set Con = New ADODB.Connection
Set myRS = New ADODB.Recordset

On Error GoTo ErrHandler

Con.CursorLocation = adUseClient
Con.ConnectionString = sCon

Con.Open sCon

Dim sSQL As String

If txtSQL.SelLength > 0 Then
sSQL = Mid(txtSQL.Text, NoValue(txtSQL.SelStart, 1), txtSQL.SelLength + 1)
Else
sSQL = txtSQL.Text
End If

sSQL = Replace(Replace(sSQL, Chr(10), " "), Chr(13), " ")
If Right(Trim(sSQL), 1) <> ";" Then sSQL = sSQL & ";"

Set DG1.DataSource = Nothing

myRS.Open sSQL, Con, adOpenDynamic, adLockOptimistic

If (InStr(1, LCase(sSQL), "select") > 0) Then
Set DG1.DataSource = myRS.DataSource
End If

SSTab1.Tab = 3
DG1.SetFocus
'myRS.Close
'Con.Close
'Set myRS = Nothing
'Set Con = Nothing


Exit Sub

ErrHandler:

Select Case Err.Number
Case -2147217865
MsgBox "Table does not exist in DB!", _
vbCritical, "HOA"
Case -2147217900
MsgBox "Syntax error in SQL expression!", _
vbCritical, "HOA"
Case Else
MsgBox "Unknown error #" & Err.Number & ":" & vbCrLf & _
vbCrLf & Err.Description, vbCritical, "HOA"
End Select


End Sub
[/code]

Any help will be greatly appreciated.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[gmmastros]

In ADO, the wildcard character is %, not *.

try:

SELECT [ImportedTable1].[ID],
       [ImportedTable1].[Keywords]
FROM   ImportedTable1
WHERE  [ImportedTable1].[Keywords] LIKE '[!]%[/!]bulb[!]%[/!]'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RonRepp]

George:

It's been awhile, but you're still pulling my hind end out of trouble.

Thanks...a star for you.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[genomon]

George strikes again - another home run!

Cogito eggo sum – I think, therefore I am a waffle.