Link to home
Start Free TrialLog in
Avatar of Ram Kumar Chellam
Ram Kumar ChellamFlag for Saudi Arabia

asked on

Need Help.. couldn't find my problem

Dear Experts,

I am facing a problem. I wrong a store procedure when i pass the value manually its working fine but when i pass the value thru the string @criteria. its not retrieving the values from the database Could you please help me to figure out where i am doing the mistake. I am inclosing the store procedure and the code as well.. Your prompt action is highly appreicated.

for more informaiton my sCriteria contains at run time the following values

here is the procedure call. where sCriteria is

"'%naz%'"

or it will be like

"'%office%' OR KeyWord_En Like '%naz%'"

where KeyWord_En is the filed name

My StoreProcedure is like this

spGetSearchEn
@Criteria nvarchar(2000)
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, Name_En, Description_En  from tbldata
Where KeyWord_En Like @Criteria
END

Regards
public static DataSet GetSearchResultEn(string sCriteria)
        {
            string qry;
            qry = "SELECT ID, Name_En, Description_En  from tblAlbahaData Where KeyWord_En Like" + sCriteria;
            SqlParameter[] inputArray = new SqlParameter[1];
            inputArray[0] = new SqlParameter("@Criteria", SqlDbType.NVarChar,2000);
            inputArray[0].Value = sCriteria;

            try
            {
                return SqlHelper.ExecuteDataset(Utility.ConnectionString(), CommandType.StoredProcedure, "spGetSearchEn", inputArray);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Your stored procedure should have the following way of checking the string with LIKE keyword.

spGetSearchEn
@Criteria nvarchar(2000)
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, Name_En, Description_En  from tbldata
Where KeyWord_En Like '%@Criteria%'
END
Avatar of Ram Kumar Chellam

ASKER

Yeah Walter,

When i manually pass criteria its workign but when i pass throug the parameter which can be


"'%naz%'"

or it will be like

"'%office%' OR KeyWord_En Like '%naz%'"

its not working.

I am trying to see how angelll is saying lets see.. more replies are highly appreciated.

Regards,
Naaz
Hi Angelllll,

I tried the way you are saying its giving this error.

Invalid object name 'dbo.parmsToList' in the explorer..

any advise.

Regards,
Naaz
In fact when you are using LIKE keyword in Stored Procedure. The format has to be like below, earlier has an error

spGetSearchEn
@Criteria nvarchar(2000)
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, Name_En, Description_En  from tbldata
Where KeyWord_En Like '%'+@Criteria+'%'
END

This will do your need..
>Invalid object name 'dbo.parmsToList' in the explorer..
you will find the function in the article ...

walter: with due respect, but your suggestion CANNOT work.
because, if the parameters is getting:
"  '%office%' OR KeyWord_En Like '%naz%'  "

your sql will result in this:
Where KeyWord_En Like '%''%office%'' OR KeyWord_En Like ''%naz%''%'

which will not at all work like eventually expected.
In fact Mr.Naazq requires to filter LIKE @Criteria in SP. Ain't it possible the way I have wrote the query.
Thanks Angelllll,

You rocks... i didn't read the article first. it was woking perfect. I have a small question related to that.

if the first record contain the keyword_en like : abc hello dear naz
and if the second the keyworlds_en like : abc, hi, naz, myword
and i search abc it will bring two result which is okie but if i search abc naz it will bring 4 result which is abc from the first record naz from the first and than abc from the second and naz from the second as well. is there any way in the query to get the distinct ID only.

Thanks i really like your support and i am going to close this with your solution.

Regards,
Naaz

yes, 2 options:

first one might not work, if there are TEXT data types, for example...


spGetSearchEn
@Criteria nvarchar(2000)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT t.ID, t.Name_En, t.Description_En  
from tbldata t
JOIN dbo.parmsToList(@Criteria, '^') l
  ON t.KeyWord_En Like l.Value
END

Open in new window

this will work anyhow


spGetSearchEn
@Criteria nvarchar(2000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @l TABLE ( Criteria varchar(1000) )
INSERT INTO @l 
 SELECT * 
   FROM dbo.parmsToList(@Criteria, '^') 
 
SELECT t.ID, t.Name_En, t.Description_En  
from tbldata t
WHERE EXISTS( SELECT NULL FROM @l l WHERE t.KeyWord_En Like l.Criteria  )
END

Open in new window

Thanks angellllll.. you are the best. your first option i tried and it works. the datatype is nvarchar. i didn't try the second one. this is the small search engine i am trying to build. hope this storeprocedure are not making the search engine slower.

I might need a support in writing another store procedure which is little bit a complex one. if you think its okie please send me a test email on naazg <at > hotmail i will ask you for a help if required.

Naaz
Great Help
Dear Angellll,

Thanks i accepted your solution and its working great. But the problem is its not searching in Arabic words. I tried with english its searching when i type something in arabic it didn't bring any result. Could you please tell me is there any modification i have to do for my arabic store procedure.. which is like

spGetSearchAr
@Criteria nvarchar(2000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @l TABLE ( Criteria varchar(1000) )
INSERT INTO @l  
 SELECT *  
   FROM dbo.parmsToList(@Criteria, '^')  
 
SELECT t.ID, t.Name_Ar, t.Description_Ar  
from tbldata t
WHERE EXISTS( SELECT NULL FROM @l l WHERE t.KeyWord_Ar Like l.Criteria  )
END

Many Thanks
please change all VARCHAR to NVARCHAR ...
Thanks Angellll,

for the prompt reply.. its already nvarchar(MAX). and when i type and check the Criteria its showing the correct thing is pass to store procedure like ''D('" .. any more suggestion please..
may be this will help you tooo. i am using in web.config file this string

    <globalization culture="auto" uiCulture="auto" enableClientBasedCulture="true" fileEncoding="utf-8" requestEncoding="utf-8" responseEncoding="utf-8"/>

is there any error or problem? in this or no
Agnelllll,

As you are always the best to support me. I have tested something and here is what i get...

when try the query
SELECT ID, Name_Ar, Description_Ar  from tbldata Where KeyWord_Ar Like  ''D(''

it didn't bring any result but when i try the query
SELECT ID, Name_Ar, Description_Ar  from tbldata Where KeyWord_Ar Like  N''D(''
it will bring the result. so only if i Add N beside the arabic work it will bring the result. Can you please quide me how to do that in your store procedure or what i have to change.

Also i have another small thing to add but tell me how to reopen give you more points for this support.

Regards,
Naaz