如何基于多选检索数据 [英] How to retrieve data based on multiple selection

问题描述

专家，

我是Uday Satardekar，

我正在asp.net中开发网站.我已经使用mysql作为后端.

在我的view_event.aspx页中，我根据选择显示所有事件.

为此，有 4个基本标准. 3 下拉列表(状态，类别，国家/地区).

和日期之间(这里有2个日期控件).

现在单击按钮，我必须显示数据库的结果.

在列表1的4个条件中是必需的.用户还可以选择多个条件.

我已经尝试了以下代码来选择国家和类别.但是无法正常工作.

Hi Expert,

I am Uday Satardekar,

I am developing website in asp.net.I have used mysql as backend.

In my view_event.aspx page, i am displaying all events based on selection.

For this there are 4 basic criteria. 3 dropdownlist(status,category,country).

and Between Dates(here 2 date controls).

Now on button click i have to display results from database.

Amoung the 4 criteria atlist 1 is necessary.user can select more than one criteria also.

I have tried following code for country and category selection.But not working properly.

MySqlConnection connect = null;
        try
        {
            int country = Convert.ToInt32(lstCountry.SelectedIndex);
            int category = Convert.ToInt32(lstCategory.SelectedIndex);
            string status = lstStatus.SelectedItem.Text;
            string startdate = txtStartDate.Text.Trim();
            string enddate = txtEndDate.Text.Trim();

            string connectStr = ConfigurationManager.ConnectionStrings["ExpoCrmConnectionString"].ToString();
            connect = new MySqlConnection(connectStr);
            connect.Open();

            string members = "SELECT crm_event.event_id,crm_event.event_name,crm_event.event_status,crm_countries.country_name,crm_event.venue,crm_category.category,crm_event.start_date,crm_event.end_date FROM crm_event LEFT JOIN crm_category ON crm_event.cat_id=crm_category.cat_id LEFT JOIN crm_countries ON crm_event.country=crm_countries.country_id " +
            "WHERE" +
            "((country IS NULL) OR (country=?country)) AND "+
            "((category IS NULL) OR (crm_event.cat_id=?cat_id))";
            
            MySqlCommand command = new MySqlCommand(members, connect);
            command.Parameters.AddWithValue("?country", country);
            command.Parameters.AddWithValue("?cat_id", category);
            
            
            MySqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            DataTable dt = new DataTable();
            dt.Load(reader);
            listvwEventResult.DataSource = dt;
            listvwEventResult.DataBind();
            connect.Close();
        }
        catch (Exception ex)
        {

        }
        finally
        {
            connect.Close();
        }

问题是关于如何编写涵盖所有4条条件的选择查询...
有时用户可以选择国家，有时可以选择国家和日期，有时可以选择国家和状态以及日期.

如何在一个查询中管理这些条件?


请帮帮我.

预先感谢.

question is regarding how to write select query which cover all 4 criteria...
Sometimes user can select country, sometimes country and date,some times country and status and date.

How to manage these condition in one query?


Please help me .

Thanks in advance.

推荐答案

我在Where条件中使用IS Null解决了这个问题.

例如.

I solved this using IS Null in Where Condition.

eg.

select * from COMPANY


WHERE

((@m_currCat IS NULL) OR (COMPANY.category = @m_currCat)) AND
--((@m_user IS NULL) OR (COMPANY.userid = @m_user)) And
((@m_country IS NULL) OR (COMPANY.country = @m_country)) And
((@m_region IS NULL) OR (COMPANY.region = @m_region)) And
(((@m_status IS NULL) AND((@m_isRemovesSelected IS NULL) OR (COMPANY.ID !=8)  )  ) OR (COMPANY.status = @m_status))

还有另一个选择.
您可以在条件中使用

There is another Option.
You can use

COALESCE

.
您可以检查

in where Condition.
you can check performence of

is null vs coalesce

的性能
但是根据我的说法

But according to me

IS NUll is faster than COALESCE

谢谢....

Thanks....

将您的代码分为两部分，这是我的代码示例

Devide ur code into 2 parts here is an example of my code

Public Function getQuery(  ByVal objPatientEnquiryObjects As PatientEnquiryObjects) As String
sqlstr = "SELECT DISTINCT IPAIPNO_REG,IPAIPNO,REGPATNAME ||REGPATLASTNAME as PATNAME,REGHEADOFFAMILYNAME, T3.REGHNO, T3.REGSTREET,T3.REGCITY, BEDTRNOCCUSEX,BEDTRNPATAGE,IPADUMMY1,IPAADMDATE,IPAADMTIME,  "
            sqlstr = sqlstr & "WRDDESC,BEDTRNRMCODE,BEDTRNBEDNO,IPAPAYTYPE,IPASPCLINSTRU,IPAPOLCOMPCODE,IPAOPRID,' ' From TBL_IPA_TN_IPADMS T1,TBL_IPA_TN_BEDTRN T2, TBL_REG_TN_NEW T3,TBL_ADM_MS_DOCTOR T4,TBL_IPA_MS_WARD T5"
            sqlstr = sqlstr & " WHERE T1.IPAIPNO_REG=T2.BEDTRNIPNO_REG AND T1.IPAIPNO_REG=T3.REGNO AND T1.IPAIPNO=T2.BEDTRNIPNO AND BEDTRNOCCBYPAT='Y' AND BEDTRNSTATUS='O' And T4.DOCCODE=T2.BEDTRNDOCCODE"
            sqlstr = sqlstr & " AND T2.BEDTRNWRDCODE=T5.WRDCODE AND IPAIPNO IN (SELECT MAX(IPAIPNO) FROM TBL_IPA_TN_IPADMS GROUP BY IPAIPNO_REG)"
        
If objPatientEnquiryObjects.MRNo <> 0 Then
            sqlstr = sqlstr & " AND IPAIPNO_REG LIKE '" & objPatientEnquiryObjects.MRNo & "%'"
        End If
        If objPatientEnquiryObjects.ipno <> 0 Then
            sqlstr = sqlstr & " AND IPAIPNO LIKE '" & objPatientEnquiryObjects++.ipno & "%'"
        End If
        If objPatientEnquiryObjects.patName <> Nothing Then
            sqlstr = sqlstr & " AND REGPATNAME LIKE '%" & objPatientEnquiryObjects.patName & "%'"
        End If
        If objPatientEnquiryObjects.BillPatType <> Nothing Then
            sqlstr = sqlstr & " AND IPAPAYTYPE LIKE '" & objPatientEnquiryObjects.BillPatType & "%'"
        End If
        If objPatientEnquiryObjects.WARD <> Nothing Then
            sqlstr = sqlstr & " AND WRDDESC LIKE '" & objPatientEnquiryObjects.WARD & "%'"
        End If
        If objPatientEnquiryObjects.room <> Nothing Then
            sqlstr = sqlstr & " AND BEDTRNRMCODE LIKE '%" & objPatientEnquiryObjects.room & "%'"
        End If
        If objPatientEnquiryObjects.doctor <> Nothing Then
            sqlstr = sqlstr & " AND IPADUMMY1 LIKE '" & objPatientEnquiryObjects.doctor & "%'"
        End If
        If objPatientEnquiryObjects.admissionFrom <> 0 Then
            sqlstr = sqlstr & " AND IPAADMDATE >= '" & objPatientEnquiryObjects.admissionFrom & "'"
        End If
        If objPatientEnquiryObjects.admissionTo <> 0 Then
            sqlstr = sqlstr & " AND IPAADMDATE <= '" & objPatientEnquiryObjects.admissionTo & "'"
        End If
        If objPatientEnquiryObjects.village <> Nothing Then
            sqlstr = sqlstr & " AND REGSTREET LIKE '%" & objPatientEnquiryObjects.village & "%'"
        End If
        If objPatientEnquiryObjects.district <> 0 Then
            sqlstr = sqlstr & " AND REGCITY LIKE '%" & objPatientEnquiryObjects.district & "%'"
        End If
        If objPatientEnquiryObjects.gender <> Nothing Then
            sqlstr = sqlstr & " AND BEDTRNOCCUSEX LIKE '" & objPatientEnquiryObjects.gender & "%'"
        End If
        If objPatientEnquiryObjects.company <> Nothing Then
            sqlstr = sqlstr & " AND IPAPOLCOMPCODE LIKE '%" & objPatientEnquiryObjects.company & "%'"
        End If
        sqlstr = sqlstr & " order by IPAIPNO desc"

return sqlstr
End Function

pass this quwry to ur code in place of members 

int country = Convert.ToInt32(lstCountry.SelectedIndex);
int category = Convert.ToInt32(lstCategory.SelectedIndex);

通过此行"lstCategory.SelectedIndex"，您将获得选定的索引.表示1,2,3等.

如果要在Dropdownlist值字段中绑定CategoryID，则应使用

by this line "lstCategory.SelectedIndex" you are getting the selected index. means 1,2,3 etc.

If you are binding your CategoryID in Dropdownlist value field then you should use

lstCategory.SelectedValue

或者，如果您要在Dropdownlist文本字段中绑定CategoryID，则应使用

Or if you are binding your CategoryID in Dropdownlist text field then you should use

lstCategory.SelectedItem.Text

这篇关于如何基于多选检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！