如何根据逗号分隔的ID进行搜索 [英] How to search based on comma separed ids

问题描述

专家，

crm_campaign表包含诸如所有者，状态，开始日期，结束日期，事件ID等字段.

event_ids 包含从crm_events表分配给小插曲广告系列的事件ID(半列分隔).像1,2,7


现在在view_campaign.aspx页面中，我正在基于事件ID搜索记录.我使用listview来显示这些记录.

有一个dropdownlist(ddlEvents)包含所有事件ID.

我的搜索字符串是这样的，

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 +"%

这个工作正常.但是假设ddlEvents.selectedValue为1，那么它会搜索包含(15,6,7)之类ID的字段.

请给我建议吗?
请帮我如何避免这种情况?

在此先谢谢您.

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.

推荐答案

您可以尝试这个吗

Can you try this one

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

~Amol

这可以满足您的需要，如果有帮助，请尝试使用SQL参数而不是构造SQL语句来避免SQL注入

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

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

您的查询标题用senicolon(;)分隔，而您提供的示例用逗号(，)表示，并且解决方案基于逗号分隔，如果它是Semicon，则可能需要稍作更改

刚刚注意到AmolPandurangBorkar875已经提供了类似的解决方案，我来晚了

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

将您的查询修改为

Modify your query as

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

Hopw这将为您提供帮助

〜Amol

Hopw this will help you

~Amol

这篇关于如何根据逗号分隔的ID进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！