asp.net listview searching with SQL query

Sant Roy

10y1.3k.NET

i have a listview with search options with three fileds name,comment,description.i have implement the search options using this query.

SELECT OrganizationID,

Name,

DescriptionShort,

DescriptionLong, Comment,

DateUpdated,

FROM t_Organization

where t_Organization.Name like '%'+@name+'%'

or t_Organization.DescriptionShort like '%'+@name+'%'

or t_Organization.DescriptionLong like '%'+@name+'%'

or t_Organization.Comment like '%'+@name+'%'

ORDER BY Name

it works perfectly.but if when i add a extra string then no records are fetching.

like 'player' search perfectly

like 'play' search perfectly

like 'pla' search perfectly

like 'pl' search perfectly

like 'p' search perfectly

but 'Players' , not work

like 'playerw' not work

like 'playing' not work.

means when we add a extra string it does not work .

.how can i do this?

i need all.

search will happen because play is common..

please help me....how can i do this?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sant Roy10y ago
finally i have done this using this query.thanks to you for giving me your valuable time.

create PROC [dbo].[GetDetails](@SearchName VARCHAR(200))
AS
BEGIN
IF ISNULL(@SearchName,'') = ''
BEGIN
SELECT * FROM t_Organization
END
ELSE
BEGIN
DECLARE @Count INT;
DECLARE @i INT = 0

WHILE (@i) < LEN(@SearchName)
BEGIN
SELECT @Count = COUNT(*) FROM t_Organization
WHERE (Name LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (DescriptionShort LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (DescriptionLong LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName)- @i) + '%')
OR (Comment LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')

IF @Count > 0
BEGIN
SELECT * FROM t_Organization
WHERE (Name LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (DescriptionShort LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (DescriptionLong LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (Comment LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
order by Name

END
SET @i = @i + 1

END
END
--DROP TABLE t_Organization
END
Sant Roy10y ago
thanks for the reply.tell me where i can add the constrains keyword.please modify my SQL query.

SELECT OrganizationID,
Name,
DescriptionShort,
DescriptionLong, Comment,
DateUpdated,
FROM t_Organization
where t_Organization.Name like '%'+@name+'%'
or t_Organization.DescriptionShort like '%'+@name+'%'
or t_Organization.DescriptionLong like '%'+@name+'%'
or t_Organization.Comment like '%'+@name+'%'
ORDER BY Name
Vishal Jadav10y ago
Hi ,

What you are trying to achieve is checking existence of string in table value you might need to use contains keyword in your sql statement.

Thank you.