How to retrieve data based on multiple selection

Question

0.00/5 (No votes)

See more:

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();
        }

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.

Posted 21-Oct-11 18:21pm

udusat13

Updated 23-Oct-11 18:39pm

v3

Add a Solution

Comments

Uday P.Singh 22-Oct-11 1:26am

are you getting any error?

udusat13 24-Oct-11 0:37am

Thanks.....

Actually my question is 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?

3 solutions

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 · Answer 1 · 2012-01-17T19:54:00

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....

raj ch · Answer 2 · 2011-10-23T20:29:00

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

VB

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

AmarSinghRawat · Answer 3 · 2011-10-22T01:21:00

Solution 1

C#

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

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

C#

lstCategory.SelectedValue

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

C#

lstCategory.SelectedItem.Text

Posted 22-Oct-11 1:21am

AmarSinghRawat

Comments

udusat13 24-Oct-11 0:38am

Thanks.....

Actually my 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?

AmarSinghRawat 24-Oct-11 1:39am

For this type of query you have to use IF Else Statement.
Like if you select country and date then you should pass different query and if you select county,status and date then you should pass different query. you have to write all possible queries.

udusat13 24-Oct-11 6:01am

Ok......

But there will be atlist 24 conditions (4*3*2).

How to retrieve data based on multiple selection

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0