Ram Kumar Chellam
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
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;
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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..
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.
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.
ASKER
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
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...
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
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
ASKER
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
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
ASKER
Great Help
ASKER
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
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 ...
ASKER
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..
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..
ASKER
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
<globalization culture="auto" uiCulture="auto" enableClientBasedCulture="
is there any error or problem? in this or no
ASKER
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
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
spGetSearchEn
@Criteria nvarchar(2000)
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, Name_En, Description_En from tbldata
Where KeyWord_En Like '%@Criteria%'
END