using 'Like'...wierd results

[Bruce Duncan]

I'm calling a stored proc in SQL server from an ASP
page. I'm not getting all the records when I call from
the ASP page, but if I run in SQL Analyzer I get the
correct number. Can someone help point out my mistake?

Below is my code that I'm calling from ASP page.
ASP Code:
lcSearchPhrase = Request.QueryString("searchfor")
if not isempty(lcSearchPhrase) then
'lcSearchPhrase = "%"+lcSearchPhrase+"%"
lcDisplayResults = "Y"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Session("strConn")
Set oCmd = Server.CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oConn
oCmd.CommandText = "spSearchProducts"
oCmd.commandtype = AdCmdStoredProc
oCmd.Parameters.Append oCmd.CreateParameter
("@SearchField", adChar, adParamInput,50)
oCmd.Parameters("@SearchField") = lcSearchPhrase
set oRsSR = oCmd.Execute
if oRsSR.EOF then
lcDisplayResults = "N"
end if
set oCmd.ActiveConnection = nothing
set oCmd = nothing
end if


SQL Code:
PROCEDURE spSearchProducts @SearchField varchar (50) AS

select qcnproduct.productid, qcnproduct.productname,
qcnproduct.prodpicture,
qcnproditem.qcnprice,
qcnproditem.supplycount,qcnproditem.typeid,
qcnsupplytype.typedesc
from qcnproduct
inner join qcnproditem
inner join qcnsupplytype on
qcnproditem.typeid = qcnsupplytype.typeid
on qcnproduct.productid = qcnproditem.productid
where qcnproduct.productname like @SearchField
or qcnproduct.proddesc like @SearchField
or qcnproduct.miscfacts like @SearchField
order by qcnproduct.productname, qcnproduct.productid,
qcnproditem.supplycount

My problem is that if the var I send from ASP isn't in
the productname field, the result set is empty...but if I
run this in SQL Query Analyzer it will return the corect
record set??????

TIA
-Bruce

[Aaron Bertrand [MVP]]

You need to include single quotes. So that your query ends up

LIKE '%something%'

rather than

LIKE %something% or LIKE something

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Bruce Duncan" <anonymous@discussions.microsoft.com> wrote in message
news:1482e01c3f8c9$7bcdddb0$a501280a@phx.gbl...

> I'm calling a stored proc in SQL server from an ASP
> page. I'm not getting all the records when I call from
> the ASP page, but if I run in SQL Analyzer I get the
> correct number. Can someone help point out my mistake?
>
> Below is my code that I'm calling from ASP page.
> ASP Code:
> lcSearchPhrase = Request.QueryString("searchfor")
> if not isempty(lcSearchPhrase) then
> 'lcSearchPhrase = "%"+lcSearchPhrase+"%"
> lcDisplayResults = "Y"
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open Session("strConn")
> Set oCmd = Server.CreateObject("ADODB.Command")
> set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "spSearchProducts"
> oCmd.commandtype = AdCmdStoredProc
> oCmd.Parameters.Append oCmd.CreateParameter
> ("@SearchField", adChar, adParamInput,50)
> oCmd.Parameters("@SearchField") = lcSearchPhrase
> set oRsSR = oCmd.Execute
> if oRsSR.EOF then
> lcDisplayResults = "N"
> end if
> set oCmd.ActiveConnection = nothing
> set oCmd = nothing
> end if
>
>
> SQL Code:
> PROCEDURE spSearchProducts @SearchField varchar (50) AS
>
> select qcnproduct.productid, qcnproduct.productname,
> qcnproduct.prodpicture,
> qcnproditem.qcnprice,
> qcnproditem.supplycount,qcnproditem.typeid,
> qcnsupplytype.typedesc
> from qcnproduct
> inner join qcnproditem
> inner join qcnsupplytype on
> qcnproditem.typeid = qcnsupplytype.typeid
> on qcnproduct.productid = qcnproditem.productid
> where qcnproduct.productname like @SearchField
> or qcnproduct.proddesc like @SearchField
> or qcnproduct.miscfacts like @SearchField
> order by qcnproduct.productname, qcnproduct.productid,
> qcnproditem.supplycount
>
> My problem is that if the var I send from ASP isn't in
> the productname field, the result set is empty...but if I
> run this in SQL Query Analyzer it will return the corect
> record set??????
>
> TIA
> -Bruce

I tried using this line below:
lcSearchPhrase = "'%"+lcSearchPhrase+"%'"

and passing lcSearchPhrase to the SP but I'm still not
getting the results???

-Bruce

>-----Original Message-----
>You need to include single quotes. So that your query

ends up

>
>LIKE '%something%'
>
>rather than
>
>LIKE %something% or LIKE something
>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>
>
>
>"Bruce Duncan" <anonymous@discussions.microsoft.com>

wrote in message

>news:1482e01c3f8c9$7bcdddb0$a501280a@phx.gbl...

>> I'm calling a stored proc in SQL server from an ASP
>> page. I'm not getting all the records when I call from
>> the ASP page, but if I run in SQL Analyzer I get the
>> correct number. Can someone help point out my mistake?
>>
>> Below is my code that I'm calling from ASP page.
>> ASP Code:
>> lcSearchPhrase = Request.QueryString("searchfor")
>> if not isempty(lcSearchPhrase) then
>> 'lcSearchPhrase = "%"+lcSearchPhrase+"%"
>> lcDisplayResults = "Y"
>> Set oConn = Server.CreateObject

("ADODB.Connection")

>> oConn.Open Session("strConn")
>> Set oCmd = Server.CreateObject("ADODB.Command")
>> set oCmd.ActiveConnection = oConn
>> oCmd.CommandText = "spSearchProducts"
>> oCmd.commandtype = AdCmdStoredProc
>> oCmd.Parameters.Append oCmd.CreateParameter
>> ("@SearchField", adChar, adParamInput,50)
>> oCmd.Parameters("@SearchField") = lcSearchPhrase
>> set oRsSR = oCmd.Execute
>> if oRsSR.EOF then
>> lcDisplayResults = "N"
>> end if
>> set oCmd.ActiveConnection = nothing
>> set oCmd = nothing
>> end if
>>
>>
>> SQL Code:
>> PROCEDURE spSearchProducts @SearchField varchar (50) AS
>>
>> select qcnproduct.productid, qcnproduct.productname,
>> qcnproduct.prodpicture,
>> qcnproditem.qcnprice,
>> qcnproditem.supplycount,qcnproditem.typeid,
>> qcnsupplytype.typedesc
>> from qcnproduct
>> inner join qcnproditem
>> inner join qcnsupplytype on
>> qcnproditem.typeid = qcnsupplytype.typeid
>> on qcnproduct.productid = qcnproditem.productid
>> where qcnproduct.productname like @SearchField
>> or qcnproduct.proddesc like @SearchField
>> or qcnproduct.miscfacts like @SearchField
>> order by qcnproduct.productname, qcnproduct.productid,
>> qcnproditem.supplycount
>>
>> My problem is that if the var I send from ASP isn't in
>> the productname field, the result set is empty...but

if I

>> run this in SQL Query Analyzer it will return the

corect

>> record set??????
>>
>> TIA
>> -Bruce

>
>
>.
>

I also tried this in my SQL SP:

CREATE PROCEDURE spSearchProducts @SearchField varchar
(50) AS

declare @SearchFld varchar (50)
set @SearchFld = "'%"+@SearchField+"'%"

select qcnproduct.productid, qcnproduct.productname,
qcnproduct.prodpicture,
qcnproditem.qcnprice,
qcnproditem.supplycount,qcnproditem.typeid,
qcnsupplytype.typedesc
from qcnproduct
inner join qcnproditem
inner join qcnsupplytype on
qcnproditem.typeid = qcnsupplytype.typeid
on qcnproduct.productid = qcnproditem.productid
where qcnproduct.productname like @SearchFld
or qcnproduct.proddesc like @SearchFld
or qcnproduct.miscfacts like @SearchFld
order by qcnproduct.productname, qcnproduct.productid,
qcnproditem.supplycount

This didn't work either...man...I know this should be
easy.

-Bruce Duncan

>-----Original Message-----
>You need to include single quotes. So that your query

ends up

>
>LIKE '%something%'
>
>rather than
>
>LIKE %something% or LIKE something
>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>
>
>
>"Bruce Duncan" <anonymous@discussions.microsoft.com>

wrote in message

>news:1482e01c3f8c9$7bcdddb0$a501280a@phx.gbl...

>> I'm calling a stored proc in SQL server from an ASP
>> page. I'm not getting all the records when I call from
>> the ASP page, but if I run in SQL Analyzer I get the
>> correct number. Can someone help point out my mistake?
>>
>> Below is my code that I'm calling from ASP page.
>> ASP Code:
>> lcSearchPhrase = Request.QueryString("searchfor")
>> if not isempty(lcSearchPhrase) then
>> 'lcSearchPhrase = "%"+lcSearchPhrase+"%"
>> lcDisplayResults = "Y"
>> Set oConn = Server.CreateObject

("ADODB.Connection")

>> oConn.Open Session("strConn")
>> Set oCmd = Server.CreateObject("ADODB.Command")
>> set oCmd.ActiveConnection = oConn
>> oCmd.CommandText = "spSearchProducts"
>> oCmd.commandtype = AdCmdStoredProc
>> oCmd.Parameters.Append oCmd.CreateParameter
>> ("@SearchField", adChar, adParamInput,50)
>> oCmd.Parameters("@SearchField") = lcSearchPhrase
>> set oRsSR = oCmd.Execute
>> if oRsSR.EOF then
>> lcDisplayResults = "N"
>> end if
>> set oCmd.ActiveConnection = nothing
>> set oCmd = nothing
>> end if
>>
>>
>> SQL Code:
>> PROCEDURE spSearchProducts @SearchField varchar (50) AS
>>
>> select qcnproduct.productid, qcnproduct.productname,
>> qcnproduct.prodpicture,
>> qcnproditem.qcnprice,
>> qcnproditem.supplycount,qcnproditem.typeid,
>> qcnsupplytype.typedesc
>> from qcnproduct
>> inner join qcnproditem
>> inner join qcnsupplytype on
>> qcnproditem.typeid = qcnsupplytype.typeid
>> on qcnproduct.productid = qcnproditem.productid
>> where qcnproduct.productname like @SearchField
>> or qcnproduct.proddesc like @SearchField
>> or qcnproduct.miscfacts like @SearchField
>> order by qcnproduct.productname, qcnproduct.productid,
>> qcnproditem.supplycount
>>
>> My problem is that if the var I send from ASP isn't in
>> the productname field, the result set is empty...but

if I

>> run this in SQL Query Analyzer it will return the

corect

>> record set??????
>>
>> TIA
>> -Bruce

>
>
>.
>

[Aaron Bertrand [MVP]]

> set @SearchFld = "'%"+@SearchField+"'%"

Why are you using double quotes here?

Try

SET @SearchFld = '%' + @SearchField + '%'
....
WHERE something LIKE @SearchFld

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

>
> select qcnproduct.productid, qcnproduct.productname,
> qcnproduct.prodpicture,
> qcnproditem.qcnprice,
> qcnproditem.supplycount,qcnproditem.typeid,
> qcnsupplytype.typedesc
> from qcnproduct
> inner join qcnproditem
> inner join qcnsupplytype on
> qcnproditem.typeid = qcnsupplytype.typeid
> on qcnproduct.productid = qcnproditem.productid
> where qcnproduct.productname like @SearchFld
> or qcnproduct.proddesc like @SearchFld
> or qcnproduct.miscfacts like @SearchFld
> order by qcnproduct.productname, qcnproduct.productid,
> qcnproditem.supplycount
>
> This didn't work either...man...I know this should be
> easy.
>
> -Bruce Duncan
>

> >-----Original Message-----
> >You need to include single quotes. So that your query

> ends up

> >
> >LIKE '%something%'
> >
> >rather than
> >
> >LIKE %something% or LIKE something
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >[url]http://www.aspfaq.com/[/url]
> >
> >
> >
> >
> >"Bruce Duncan" <anonymous@discussions.microsoft.com>

> wrote in message

> >news:1482e01c3f8c9$7bcdddb0$a501280a@phx.gbl...

> >> I'm calling a stored proc in SQL server from an ASP
> >> page. I'm not getting all the records when I call from
> >> the ASP page, but if I run in SQL Analyzer I get the
> >> correct number. Can someone help point out my mistake?
> >>
> >> Below is my code that I'm calling from ASP page.
> >> ASP Code:
> >> lcSearchPhrase = Request.QueryString("searchfor")
> >> if not isempty(lcSearchPhrase) then
> >> 'lcSearchPhrase = "%"+lcSearchPhrase+"%"
> >> lcDisplayResults = "Y"
> >> Set oConn = Server.CreateObject

> ("ADODB.Connection")

> >> oConn.Open Session("strConn")
> >> Set oCmd = Server.CreateObject("ADODB.Command")
> >> set oCmd.ActiveConnection = oConn
> >> oCmd.CommandText = "spSearchProducts"
> >> oCmd.commandtype = AdCmdStoredProc
> >> oCmd.Parameters.Append oCmd.CreateParameter
> >> ("@SearchField", adChar, adParamInput,50)
> >> oCmd.Parameters("@SearchField") = lcSearchPhrase
> >> set oRsSR = oCmd.Execute
> >> if oRsSR.EOF then
> >> lcDisplayResults = "N"
> >> end if
> >> set oCmd.ActiveConnection = nothing
> >> set oCmd = nothing
> >> end if
> >>
> >>
> >> SQL Code:
> >> PROCEDURE spSearchProducts @SearchField varchar (50) AS
> >>
> >> select qcnproduct.productid, qcnproduct.productname,
> >> qcnproduct.prodpicture,
> >> qcnproditem.qcnprice,
> >> qcnproditem.supplycount,qcnproditem.typeid,
> >> qcnsupplytype.typedesc
> >> from qcnproduct
> >> inner join qcnproditem
> >> inner join qcnsupplytype on
> >> qcnproditem.typeid = qcnsupplytype.typeid
> >> on qcnproduct.productid = qcnproditem.productid
> >> where qcnproduct.productname like @SearchField
> >> or qcnproduct.proddesc like @SearchField
> >> or qcnproduct.miscfacts like @SearchField
> >> order by qcnproduct.productname, qcnproduct.productid,
> >> qcnproditem.supplycount
> >>
> >> My problem is that if the var I send from ASP isn't in
> >> the productname field, the result set is empty...but

> if I

> >> run this in SQL Query Analyzer it will return the

> corect

> >> record set??????
> >>
> >> TIA
> >> -Bruce

> >
> >
> >.
> >