wood burning stoves 2.0*
The moose likes JDBC and the fly likes need help with searching in sql using a combobox Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "need help with searching in sql using a combobox" Watch "need help with searching in sql using a combobox" New topic
Author

need help with searching in sql using a combobox

Karen Wilson
Ranch Hand

Joined: Nov 19, 2012
Posts: 68
I am creating a project that is connected to a database. The database contains details about houses i.e address, number of rooms, price etc.
I have two frames in the project - first frame has a text box for the user to enter the location of the house they want to search for and a combobox with different price ranges (Any, €100,000 - €150,000, €150,000 - €200,000, €200,000 - €250,000, €250,000 - €350,000, €350,000 - €500,000, over €500,000).
The second frame then displays all houses with the location that the user entered.

I have the search function working for when the user enters a location, but now i need to add code for the combobox.

I am not sure how to display the houses within the price range selected from the combobox

here is the code from the first frame


and here is the code to display the results of the search in the second frame:


i tried adding some code for price but it does nothing

i couldnt paste all the code here because you can only post 80 characters in each code block
Kemal Sokolovic
Bartender

Joined: Jun 19, 2010
Posts: 825
    
    5

If you want to list all the houses in the given city with a price in the given range then your database query is wrong. What your existing query does is that it selects all the houses in the given city with exactly the same price you specified.
So, for example, listing all the houses in Paris with the price between 150 000 and 200 000 you would have something like this:

So you need to specify both lower and upper limits for the price.


The quieter you are, the more you are able to hear.
Karen Wilson
Ranch Hand

Joined: Nov 19, 2012
Posts: 68
ok but how do i set each option in the combobox to an sql query?
will i need to write a different sql query for each option?
Kemal Sokolovic
Bartender

Joined: Jun 19, 2010
Posts: 825
    
    5

No need to write separate sql query for each option. You should first change the signature of search method to support making a query I mentioned:

To get minPrice and maxPrice you will have to play around a bit with the selected item from combo box in order to extract these two values. For start, if the format in the combo box is: $100,000 - $150,000 splitting around '-' would get you two String values [$100,000 and $150,000] (check String#split for more details about method). Now it's up to you to figure out how would you parse these values a bit more so you can get the values desired.
Karen Wilson
Ranch Hand

Joined: Nov 19, 2012
Posts: 68
i am still not sure exactly what to do.
i have tried this so far:


and i changed the query to this:

but im getting an error saying unknown column minPrice in where clause
Kemal Sokolovic
Bartender

Joined: Jun 19, 2010
Posts: 825
    
    5

If the error message you get is this:

then you've missed to create a valid query for the database. Your query compares 'price' with 'minPrice' as it is a known value (column from database). You should make something like this:
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

It would be good to use a PreparedStatement with parameters.

Your query would then look like (example pseudocode, untested):


OCUP UML fundamental and ITIL foundation
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: need help with searching in sql using a combobox
 
Similar Threads
problem in sql syntax (method calling)
Insert query from java program
Connection from DataSource only allows "selects"!
Communications link failure
need help with jcombobox