Am trying to implement a search functionality in my movie application. I want to enable people to search the database (MSACCESS 2000) using either the title, actors, genre, language, format, loan_dates etc. Attached is my GUI but I dont know how well I can code inorder to get desired results. On clicking the search button, a new form will pop-up and the movies meeting the criteria are listed in a ListView. Upon which, one can click or double click a selected item and it shows the details of this movie. The search will include AND/OR operators.
Last edited by osemollie; Jul 19th, 2006 at 09:23 AM.
You just need to build the SQL statement based on the control values. As the SQL statement is just a string, you can use normal string manipulation to do that.
For an example, see the "How about a search button?" section of this FAQ thread.
You'll want a criterion choice for each search term. IOW, Genre = 'XXX' AND Country = 'YYY' OR Language = 'ZZZ', and you'll want to be able to group with parentheses - do you want a search on (Genre = 'XXX' AND Country = 'YYY') OR Language = 'ZZZ' or do you want Genre = 'XXX' AND (Country = 'YYY' OR Language = 'ZZZ')?
The GUI to produce a real search statement can be very involved, and beyond the capability of most users to understand. It's always a trade-off - do you leave the user with not very much capability, or do you overwehelm the user with capability he can't understand?
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
Am trying to implement a search functionality in my movie application. I want to enable people to search the database (MSACCESS 2000) using either the title, actors, genre, language, format, loan_dates etc. Attached is my GUI but I dont know how well I can code inorder to get desired results. On clicking the search button, a new form will pop-up and the movies meeting the criteria are listed in a ListView. Upon which, one can click or double click a selected item and it shows the details of this movie. The search will include AND/OR operators.
Si, aren't you going to get an incorrect SQL statement if any of the comboboxes is blank?
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
Nope, the SQL statement will be perfectly valid - but the relevant part of the Where clause will only match with rows where the field is blank (but not Null).
(if the field was Numeric, or a date, a blank value would indeed make it an invalid SQL statement)
In order to ignore any "blank" comboboxes, you need to use an If statement as in the example I keep on refering too.
si_the_geek, I have been to the thread but am a bit slow, am still not able to relate your code above and the one below, which is from the thread. Someone help me understand better. In my case, I dont want to have "empty" textboxes.
VB Code:
Dim strSQL as String, strWhere as String
'build the SQL statement based on what the user typed in txtSearch and txtAnotherSearch
strSQL = "SELECT * FROM tbl_master"
'(find the conditions)
If txtSearch.Text <> "" Then strWhere = strWhere & " AND Field2 = " & Val(txtSearch.Text)
If txtAnotherSearch.Text <> "" Then strWhere = strWhere & " AND Field7 = " & Val(txtAnotherSearch.Text)
If strWhere <> "" Then '(put the conditions into the SQL statement, without the first And)
No problem, this kind of thing can be a bit daunting if you haven't done it before!
It helps if you can write the SQL for each situation, for example if you were only looking for Genre the SQL might look like this:
VB Code:
strSQL = "SELECT * FROM table_name WHERE genre = '" & cboGenre.Text & "'"
The idea is to build the SQL statement based on the control values, only using the conditions that have been specified. To do this you start of with the known part ("SELECT * FROM table_name"), and then add the conditions on to it.
As you don't know which condtions will definitely be used (if any), you cant just place the "Where" into the known part, so we build the Where clause in a separate string, and join it on when it is finished.
BrailleSchool's idea is a great part, and this is how you would implement it all together:
VB Code:
Dim sKeyWord As String 'Find the keyword to use
Select Case [u]cboKeyword[/u].ListIndex '(change to the name of your keyword combo, and change the keywords below to a different order if needed)
Case 0: sKeyWord = " AND "
Case 1: sKeyWord = " OR "
Case 2: sKeyWord = " AND NOT "
End Select
Dim strSQL as String, strWhere as String
'build the SQL statement based on what the user typed in txtSearch and txtAnotherSearch
strSQL = "SELECT * FROM [u]table_name[/u]" '(change to the name of your table)
'(find the conditions)
'(this line is basically the same as the example above, but only if the text has been set)
If cboGenre.Text <> "" Then strWhere = strWhere & sKeyWord & "Genre = '" & cboGenre.Text & "'"
If txtAnotherSearch.Text <> "" Then strWhere = strWhere & sKeyWord & "NumericField = " & Val(txtAnotherSearch.Text)
'(put the conditions into the SQL statement, without the first keyword)
If strWhere <> "" Then
strSQL & " WHERE " & Mid(strWhere, len(sKeyWord) + 1)
End If
The most awkard part is "find the conditions", which you should probably start off with just one condition, and test it. You can then add one at a time, and test each one as you go (on it's own, and then with the ones that are already working).
I decided to take si_the_geek's advice and try out indivudual codes to confirm whether they work or not before attempting the COMBINATIONS. Looking at my GUI graphic above, you will realise that I want to allow the user to search movie eg loaned out between specified dates. How can I code that?
- On clicking the search button, I want to open a new window and inside this window, the Results get shown in a ListView control. How to?
- As Al42 had pointed earlier, I get an error when i try the searching while the combo boxes are empty.
The .Open immediately opens the recordset using the SQL you provide, so you cannot give it part of the SQL, you need all of it. So instead of having:
.Open "SELECT ....
You need to use:
.Open strSQL
Your SQL statement needs to be built in strSQL as I showed you, everything before the WHERE in your .Open line needs to be in the initial setup of strSQL (replacing strSQL = "SELECT * FROM table_name" from my example), and all control values (such as your current Where clause) need to be in If statements as in my example.
All of the conditions (in the example at least, and presumably in your project too) are 'optional', so you cannot tell which will be the first one - so we just put the same keyword before each one (then it will be between each different item, but also at the start instead of "WHERE"). By the time this line of code is reached, strWhere may contain this:
" AND Genre = 'genre' AND NumericField = 7"
..which means that in order to create a proper SQL statement we need to remove the first keyword (which is what the Mid does), and replace it with WHERE, then append that to the end of the existing SQL.
I just build a strWhere. If it's blank it gets " Where ", if not it gets " And ". Then it gets the condition being tested for. After all the conditionals strSQL = strSQL & strWhere.
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
That's basically the same as the approach I've suggested - except my version makes the 'repeated' code half the size (so easier to write/read/maintain), and probably faster (as you dont repeatedly check for a blank strWhere, only do 1 Mid at the end).
The speed isn't really an issue tho, as the whole code will run in a very small amount of time anyway.
Nope, the SQL statement will be perfectly valid - but the relevant part of the Where clause will only match with rows where the field is blank (but not Null).
(if the field was Numeric, or a date, a blank value would indeed make it an invalid SQL statement)
Some fields of mine are numeric and when left blank, I get an error in the SQL code. What is the best way around it?
- Just to mention, On clicking the search button, I want to open a new form and on this form, the results get shown in a ListView control . How to do I do that?
Some fields of mine are numeric and when left blank, I get an error in the SQL code. What is the best way around it?
Again, use If statements as shown in my example - you should not be adding any "blank" conditions to the SQL statement.
If you can't work out how to do that, show us the current code.
Just to mention, On clicking the search button, I want to open a new form and on this form, the results get shown in a ListView control . How to do I do that?
Basically as you posted above (assuming that code works for the same form), but with code to load the form first (eg: Form1.Load), and prefixing the control with the form name (eg: Set li = Form1.ListView1.ListItems.Add ....).
You seem to have ignored several important parts of each line in the "'(find the conditions)" part of my example, and left out the vital "'(put the conditions into the SQL statement, without the first keyword)" section
si_the_geek, I really appreciate your patience and toleranace. Am determined to learn and this is proving a wonderful experience. For real, one cannot find your kind of teaching in a book. Thanks.
- Am faced with another dilema. All the fields except the loan/return controls search a different query (movieInfo, MediaType, genre etc tables). But the loan/return controls search using a different query all together (generated from order and order_details tables). How can I ensure that a specific query runs depending on the controls used?
- Looking at the GUI, my intention of using the field combobox was to search the database using the selected/filled in values. My choice for keyword level search was to enable a user to search using keywords typed in the textbox. For example I might have a movie title as 'Twins' and another one as 'The Siamese Twins', the keyword search should allow one to search using 'Twins' and all the two movies should come up.
- Am faced with another dilema. All the fields except the loan/return controls search a different query (movieInfo, MediaType, genre etc tables). But the loan/return controls search using a different query all together (generated from order and order_details tables). How can I ensure that a specific query runs depending on the controls used?
VB Code:
If chkLoan.Value = 1 Then
...
ElseIf chkReturn.Value = 1 Then
...
Else
<search code for comboboxes>
End If
I'd also disable (<control>.Enabled = False) the controls you're not going to be doing the search on if either one is checked, so the user knows not to set things that won't be searched on.
- Looking at the GUI, my intention of using the field combobox was to search the database using the selected/filled in values. My choice for keyword level search was to enable a user to search using keywords typed in the textbox. For example I might have a movie title as 'Twins' and another one as 'The Siamese Twins', the keyword search should allow one to search using 'Twins' and all the two movies should come up.
Also using the comboboxes, or ignoring the comboboxes?
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
I'm not quite sure what you mean from your "Twins" example, it may be as simple as changing from this:
VB Code:
... & "title = '" & txtSearch.Text & "' "
..to this:
VB Code:
... & "title Like '%" & txtSearch.Text & "%' "
That will find any rows where the Title field contains the contents of txtSearch.Text at any position in the field (so "Twins" would match both of your examples, but it would also match things like "Twinset").
si_the_geek, I really appreciate your patience and toleranace. Am determined to learn and this is proving a wonderful experience. For real, one cannot find your kind of teaching in a book. Thanks.
Thanks.
I aim to help people become better programmers, rather than simply throwing examples at them (but I do that too!). It's good to see people trying to learn.
Looking at my GUI above, my movie application allows one to borrow movies for:
If VCD =3 days
DVD = 2 days
VHS = 4
How can I ensure that if one borrows a VHS for example, the checkout date is increased automatically to 4 days from the day of checkout? And there after I will have an admin form where I want to allow the user to determine the number of days allowed, instead of the default that I have set.
How can I ensure that if one borrows a VHS for example, the checkout date is increased automatically to 4 days from the day of checkout?
Use the Datediff function.
And there after I will have an admin form where I want to allow the user to determine the number of days allowed, instead of the default that I have set.
How can I code that?
Keep the number of days for each in your database - in a table named Expiration, maybe? The program looks up the 3 expiration times when it runs and keeps them in global variables. Another function allows someone with the correct password to change the values, both in the global variables and in the data table (at the same time - change in both or neither).
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
to see the GUI am using. Am a bit not sure of how to implement your solution. The expiry dates are saved in a table Mediatype wshose fields are: mediaTypeId, mediaType, DaysForBorrowing.
Just get DaysForBorrowing and use it in a DateAdd - dtReturnDate = DateAdd("d", DaysForBorrowing, Now) and write a little utility that allows someone with the correct password to change DaysForBorrowing in the table (a simple Update SQL statement).
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
Reply With Quote
Originally Posted by osemollie
now i do
