How to search based on comma separed ids

Question

1.00/5 (1 vote)

See more:

Hi Expert,

crm_campaign table contains fields like owner,status,start_date,end_date,event_ids etc.

event_ids contains event ids assigned from crm_events table to perticuler campaigns with semi column separate. like 1,2,7

Now in view_campaign.aspx page i am searching records based on event id.I used listview to display these records.

There is one dropdownlist(ddlEvents) which contains all event id.

my search string is like this,

select * from crm_campaign where event_ids like %"+ddlEvents.selectedValue +"%

This working fine.But suppose ddlEvents.selectedValue is 1, then it serches field which contain ids like (15,6,7) .

please give me suggestion?
Please help me how to avoid this?

Thanks in advance.

Posted 3-Nov-11 23:07pm

udusat13

Updated 4-Nov-11 18:16pm

v2

Add a Solution

4 solutions

Solution 2

Can you try this one

C#

string strSearchValue1 = ddlEvents.selectedValue + ",";
string strSearchValue2 = "," + ddlEvents.selectedValue;
string strSearchValue3 = "," + ddlEvents.selectedValue + ",";

select * from crm_campaign where event_ids like %"+ strSearchValue3 +"% or event_ids like "+ strSearchValue1 +"% or event_ids like %"+ strSearchValue2 +";

~Amol

Posted 4-Nov-11 1:09am

Amol_27101982, India

Updated 4-Nov-11 20:50pm

v2

Comments

udusat13 4-Nov-11 8:43am

Thanks...
I ll try tommorrow
Thanks .................

udusat13 5-Nov-11 0:14am

its working in certain condition.means if selected value is 1 then it searches 5,11,2 because of
event_ids like "," + ddlEvents.selectedValue

Amol_27101982, India 5-Nov-11 12:47pm

You may require to do few changes in the search values.

Solution 3

This does what you need and if this helps, try to use SQL paramters instead of constructing the SQL statement to avoid SQL injection

SQL

"select *
from employees
where
Event_id  like '"+ddlEvents.selectedValue+",%'
or Event_id  like '%,"+ddlEvents.selectedValue+",%'
or Event_id  like '%,"+ddlEvents.selectedValue+"'"

Your questin heading says senicolon (;) seperated and the example you have provided says comma (,) and the solution is based on comma seperatation and you may have to slightly change the query if it is semicon

Just noticed AmolPandurangBorkar875 has provided the similar solution already, I was late

Posted 4-Nov-11 2:15am

Bala Selvanayagam

Updated 4-Nov-11 2:20am

v3

Comments

udusat13 4-Nov-11 8:43am

Thanks...
I ll try tommorrow
Thanks .................

udusat13 5-Nov-11 0:20am

its working in certain condition.means if selected value is 1 then it searches 5,11,2 also because of event_ids like "," + ddlEvents.selectedValue

11,21,31,,41 also ends up with 1 which also satisfy like value,% this condition,
Please give me other solution
Thanks....

Solution 1

Modify your query as

SQL

select * from crm_campaign where event_ids in ("+ddlEvents.selectedValue +")

Hopw this will help you

~Amol

Posted 3-Nov-11 23:18pm

Amol_27101982, India

Updated 3-Nov-11 23:20pm

v2

Comments

udusat13 4-Nov-11 5:35am

Thanks...
But its working if selected id is 2 and events ids from database that begin with 2 like (2,4,5) not working when for (4,2,5) or (5,7,2)

BobJanova 4-Nov-11 6:11am

Can you use 'in' in reverse, i.e.:

select * from crm_campaign where @value in event_ids

I'm not sure if that is legal though.

Also, use a parameterised query, don't build the query string up from parts like that, particularly if that is something which comes directly from user input. It is trivial to submit a HTTP request or modify the page so the dropdown value is "3; drop table crm_campaign; ".

udusat13 4-Nov-11 6:30am

Thanks
But its workin if @ @value=3 and database field contains only 3 . When fields contains 4,3 or 3,4 or 3,5,6. its not working.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

udusat13 · Accepted Answer · 2011-11-04T20:48:00

Finnally I got solution,

i use concat ',' in where condition,
Eg

SELECT *
FROM `crm_campaign`
WHERE concat(',', `event_ids`, ',') LIKE '%,ddlEvents.SelectedValue(),%'

Now its searching only 1 if i selected value is 1 even if there are fields like 11,21,51 etc.

Thanks to above persons who help me......