sql; selecting strings from combolists

[smartglass]

Hi:
I have a MySql table 'customer' containing 2 columns: code (integer) and name (String).
I want users to find details about the customer by calling from the drop down comboBox, but if the combo box is a list of names the SELECT query returns a SQL error; if the combobox comprises codes then I am fine.
THus "SELECT * FROM customer WHERE name=" + jComboBox1.getselectedItem()); fails
whereas "SELECT * FROM customer WHERE code=" + jComboBox2.getselectedItem()); succeeds
where comboBox 1 is a list of names and comboBox2 codes.
It will be unhelpful if I force users to use codes as a reference; is there anything I am missing that allows insertion of text into the select statement?
Thanks!!

 

[chrissie1]

appart from the fact that MVC seems to be forgotten.

"SELECT * FROM customer WHERE name='" + jComboBox1.getselectedItem()) + "'";

your codes are probably something numeric.

For the rest I only see bad practice.

Christiaan Baes
Belgium

"My new site" - Me

 

[Diancecht]

Try to print the actual recovered code and the actual SQL sentence.

Cheers,
Dian

 

[jstreich]

Also when posting code, please use code tags.
When saying "it returns an SQL error" please tell us what the error your getting is.

chrisse is right, your missing the quotes and esacpes from the strings being passed to the SQL server. Even though you may be fairly sure that there aren't any speical chars, you'll still want to run it through an escape algo which escapes chars that SQL would try and act on (mainly quotes).

If you want to use codes internally, you could create a lookup table that maps the strings from the combobox or getting the selected index as a feild instead of the selected String (which is what you'd still display to the user (the String)).

SELECT * FROM customer WHERE [red]index[/red]=" + [green]jComboBox1.getSelectedIndex()[/green] ;

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[timw]

Don't build SQL up in this way. Use a PreparedStatement like

PreparedStatement ps = connection.prepareStatement("SELECT * FROM customer WHERE name=?;");

and set your name string as the first parameter.

ps.setString(1, stringFromCombo);

You can get a ResultSet from the PreparedStatement in the same way you would from a Statement.

Tim