SQL query with parameters and wildcards

[sl0w]

Hi,
what I want to do is to run a query like this one:

SELECT RadID,Leverantor FROM InkopsOrder WHERE Leverantor LIKE '%kompaniet%'

which works fine, but I want the '%kompaniet%' part to be a parameter, like this:

"SELECT RadID,Leverantor FROM InkopsOrder WHERE Leverantor LIKE ?"

And I can't get it to work out! The problem is I don't get anything in my recordset when using the second query.
Here's what my code looks like:

Set comm.ActiveConnection = conn
comm.CommandText = _
    "SELECT RadID,Leverantor FROM InkopsOrder WHERE Leverantor LIKE ?"
sokstrang = "'%" & InputBox("Söksträng?") & "%'"
comm.Parameters(0) = sokstrang
rec.Open comm
ws.[a1].CopyFromRecordset rec
'ws.[d1].Cells(1) = comm.Parameters(0)

The
'ws.[d1].Cells(1) = comm.Parameters(0)
is there so I can check that the parameter actually has the value '%kompaniet%', which it has.

Now, i cant use

"SELECT RadID,Leverantor FROM InkopsOrder WHERE Leverantor LIKE '%?%'"

Or anything like that since It doesnt seem to recognize ? as a parameter when I do.

PLEASE help me out on this one :P
thanks.
Reply With Quote

[Ecniv]

dim strSql as string

sokstrang = "'%" & InputBox("Söksträng?") & "%'"
strSql = "SELECT RadID,Leverantor FROM InkopsOrder WHERE Leverantor LIKE " & sokstrang

'---- shouldn't this be a recordset not a command??
Set comm.ActiveConnection = conn
comm.CommandText = strSql
rec.Open comm
ws.[a1].CopyFromRecordset rec