Click here to Skip to main content
14,265,813 members
Rate this:
Please Sign up or sign in to vote.
See more:
I'm creating an application for a front end side of a database that allows users to log in and buy tickets/vouchers for special events. I've got everything done, but im having trouble on the search page.

Basically I have a few combo boxes that have options within them that allow the user to specify their search. the combo boxes are named like , category, genre, location, price range etc...
all of which have drop down menus for different options.

I want the user to chose these options then click the search button to narrow their search down.

I'm having trouble with this. I can get the search to narrow it down if the user picks an option for one combobox, but when they chose another option in another combobox it resets the search and primiarly searching for the combo box.

Do i keep creating Select statements for them, or do i just put the search criteria on the dataset.?

Here is the code for the search button, im sure most of you experience programmers will be able to understand it, as im a very simple way of working and naming.

public void searchButton_Click(object sender, RoutedEventArgs e)
        {
            //    SqlConnection _connection = SMUMomentsDB.GetConnection();
            using (SqlConnection _connection = SMUMomentsDB.GetConnection())
            {
                _connection.Open();
                using (SqlDataAdapter _momentsTableAdapter = new SqlDataAdapter("Select * from SpecialMoment", _connection))
                {
                    DataSet _ds = new DataSet();
                    _momentsTableAdapter.Fill(_ds);

                    momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
                    momentsDataGrid.IsReadOnly = true;

                    if (categoryComboBox.SelectedIndex == 0)            // Entertainment Option
                    {
                        SqlDataAdapter _entertainmentCategory = new SqlDataAdapter("Select * from SpecialMoment WHERE GenreID  in ('2',  '3', '11',  '14')", _connection);
                   //     DataRow[] result = _ds.Tables[0].Select("WHERE GenreID in ('2', '3', '11', '14')");
                  //      DataSet _ds = new DataSet();
                        _entertainmentCategory.Fill(_ds);

                        momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
                        momentsDataGrid.IsReadOnly = true;
                        
                    }

                    if (locationComboBox.SelectedIndex == 0)
                    {
                        
                         SqlDataAdapter locationAdapter = new SqlDataAdapter("Select * from SpecialMoment WHERE VenueLocation = 'Wales'", _connection);
                            {
                              //  DataSet _ds = new DataSet();
                                _momentsTableAdapter.Fill(_ds);

                                momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
                                momentsDataGrid.IsReadOnly = true;
                            }
                        }
                    }
                }
            }
        }
        if (categoryComboBox.SelectedIndex == 0)            // Entertainment Option
        {
            using (SqlConnection _connection = SMUMomentsDB.GetConnection())
            {
                _connection.Open();
                using (SqlDataAdapter _momentsTableAdapter = new SqlDataAdapter("Select * from SpecialMoment WHERE GenreID  in ( '2',  '3', '11',  '14')", _connection))
                {
                    DataSet _ds = new DataSet();
                    _momentsTableAdapter.Fill(_ds);

                    momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
                    momentsDataGrid.IsReadOnly = true;
                }
            }
        }

        if (genreComboBox.SelectedIndex == 0)
        {
            using (SqlConnection _connection = SMUMomentsDB.GetConnection())
            {
                _connection.Open();
                using (SqlDataAdapter _momentsTableAdapter = new SqlDataAdapter("Select * from SpecialMoment WHERE GenreID = '3'", _connection))
                {
                    DataSet _ds = new DataSet();
                    _momentsTableAdapter.Fill(_ds);

                    momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
                    momentsDataGrid.IsReadOnly = true;
                }
            }
        }

        if (locationComboBox.SelectedIndex == 0)
        {
            using (SqlConnection _connection = SMUMomentsDB.GetConnection())
            {
                _connection.Open();
                using (SqlDataAdapter _momentsTableAdapter = new SqlDataAdapter("Select * from SpecialMoment WHERE VenueLocation = 'Wales'", _connection))
                {
                    DataSet _ds = new DataSet();
                    _momentsTableAdapter.Fill(_ds);

                    momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
                    momentsDataGrid.IsReadOnly = true;
                }
            }
        }
        if (categoryComboBox.SelectedIndex == 1)
        {
            MessageBox.Show("Food and Drink");              // Food and Drink Option
        }
Posted
Updated 25-Feb-14 3:42am
v3
Comments
ZurdoDev 24-Feb-14 12:32pm
   
Where are you stuck? I don't follow.
Chris_Burns 24-Feb-14 12:38pm
   
I cannot narrow the search down, when i click a category, then click a location, instead of searching for a category of events within the location chosen... It instead searches for the location chosen, even if the chosen category is on there or not. So basically instead of combining the search with the options chosen, it just searches based on the last combobox clicked.
ZurdoDev 24-Feb-14 12:53pm
   
You have to write your SQL to take into account all search fields.

SELECT * FROM table WHERE (condition1 = @condtion1 OR @condtion1 = '') AND (condition2 = @condition2 OR @condition2 = '') etc.
Member 11087953 1-May-15 9:01am
   
if fiter data date range ,Then what's the query look like
Rate this:
Please Sign up or sign in to vote.

Solution 1

You can do a search with multiple items using AND in the WHERE clause.
For e.g. select * from specialmomnet where genreid in (...) AND secondcolumn in (..)
   
Comments
Sergey Alexandrovich Kryukov 24-Feb-14 13:37pm
   
Captain Obvious at work; a 5. :-)
—SA
Abhinav S 24-Feb-14 13:49pm
   
Thanks
Chris_Burns 25-Feb-14 9:38am
   
but you have to understand, i know how to search the database via SQL but im also using C# and have to use comboboxes to attach the search to.. So would i have to attach a select statement in every combobox code?

I have updated the question with the rest of the code.
Maciej Los 25-Feb-14 12:36pm
   
Have a look at my answer.
Rate this:
Please Sign up or sign in to vote.

Solution 2

You should loop through the collection of controls (search criteria) and store it into comma delimited string variable. After that you can pass it into stored procedure[^] (SP). Inside SP, you can split it and use to search database. Check OriginalGriff's tips to find out how to do that: Using comma separated value parameter strings in SQL IN clauses[^] and Converting comma separated data in a column to rows for selection[^]. If you're familiar with CTE[^], you can use it too.

For further information, please see:
How to: Access Controls by using the Controls Collection[^]
How to: Create and Execute an SQL Statement that Returns No Value[^]
How to: Create and Execute an SQL Statement that Returns a Single Value[^]
How to: Create and Execute an SQL Statement that Returns Rows[^]

By The Way: i would strongly recommend to read about SQL Injection[^].
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
   
Comments
Abhinav S 25-Feb-14 14:04pm
   
5!
Maciej Los 25-Feb-14 14:47pm
   
Thank you, Abhinav ;)

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100