Question with LIKE clause in search 1

[VonFranzken]

I have a recordset in dreamweaver, I know!

SELECT *
FROM dbo.activityout
WHERE activityname LIKE '%MMColParam%'

The variable is set to filter by url parameter id which is assigned to MMColParam through a variable.

Lets say I have a record of John O'Conor

And I search for John O'Conor at ABCD, the recordset says it cannot find any matches when it should have found John O'Conor.

Is there any way of making the SQL stronger so it would have found it?

Steve

 

[amrita418]

If MMColParam is a column name/variable then the query should concatenate the '%' and the variable.

Sample code:

create table #ActivityOut (id int, activityname varchar(20))

insert into #ActivityOut values(1, 'Jon O''Conner')
insert into #ActivityOut values (2, 'James O''Baker')

declare
	@v_string varchar(20)
set 	@v_string = '''conner'

select 	* 
from 	#ActivityOut
where 	actiityname like '%' + @v_string + '%'

In your case the code should be:

select * from dbo.activityout where activityname like '%' + MMColParam + '%'

Regards,
AA

 

[VonFranzken]

It says invalid operator for datatype, and thanks for you help btw. I really appreciate it.

STeve

 

[amrita418]

what is the datatype of activityname and MMcolParam?

If either of them is not string (varchar datatype) use the convert function in the query.

Sample code:

select * from dbo.activityout where activityname like '%' + convert(varchar, MMColParam) + '%'

Regards,
AA

 

[VonFranzken]

Ok Getting closer, I cliced a link that said Ahn Trio and recieved the following. It seems to be picking up the second part of the names.



Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'Trio'.
/yasi/activities_details.asp, line 41

Both are varchar in response to your earlier question.

Thanks for your help.

STeve

 

[amrita418]

You will need to post the complete line that is giving you the error. Maybe there is some error with using quotes (in the query) built in asp.

Also, try running the statement on sql server and if the query runs fine then you know the error has something to do with building the stmt in asp.

Regards,
AA

 

[amrita418]

Also I am not sure what you mean when you say this
> It seems to be picking up the second part of the names.

Regards,
AA