Hard One > Default Value from a Combox Box, shown in a JList based on a prepared statement parameter

[Declan Dee]

Hi Gents,

In desperate need of help

I need to do the following

I need to select from a range of values (via a regular resultset) and show this information in a Drop Down List - I succeded

Now I need to show in a JList ONLY values that are linked to what is currently selected in the combox, that are coming out of a MySQL database and I am already over 6 hours doing it

So basically when I select an option for example 'dance' or 'circus' in the drop down list. I need to get ONLY what is linked with 'Circus' in the MySQL database (via the code I have shown)

The MySQL code is fine. Currently the Prep statement is 'prepStatement.setString (1,"Dans");' This means that no matter what I select in the main drop down list, I will only get what is linked 'Dans' and not the other genres.

I tried with this but to no avail:

prepStatement.setObject(1, combobox.getSelectedItem());

Here is the code, showing the two methods. The first method works fine when called. It makes a dropdown list with just Genres.

The second however...........

    

//=================================================================================Regular Statement
    public void getGenreValues () {
        try {
            statement = databaseConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            resultset = statement.executeQuery("select Genre from genres");
            String s = "";
            try {
                while (resultset.next()) {
                    combobox.addItem(resultset.getString("Genre"));
                }
            } catch (SQLException ex1) {
                {
                    System.out.println(ex1 + " ==>Problem with getting Database Values");
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(CultuurHuis1.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
//================================================================================Prepared Statement
    public void getGenreElements () {
        Vector v = new Vector();
        try {
              String sql = "Select voorstellingen.titel, voorstellingen.Uitvoerders, voorstellingen.Prijs, voorstellingen.VrijePlaatsen, " +
                        "genres.genre from cultuurhuis.voorstellingen, cultuurhuis.genres " +
                        "where genre = (?)";
              prepStatement = databaseConnection.prepareStatement(sql);
              
              prepStatement.setString (1,"Dans");

              //prepStatement.setObject(1, combobox.getSelectedItem());

              resultset2 = prepStatement.executeQuery();

              while(resultset2.next()) {
                  v.addElement(resultset2.getString("Titel") +
                    " / " + resultset2.getString("Uitvoerders") + " / "
                    + "€" + resultset2.getBigDecimal("Prijs") + " / " + " Places Available: " + resultset2.getInt("VrijePlaatsen"));
              }
              listSelectedGenre.setListData(v);
              

        } catch (SQLException ex) {
            {System.out.println(ex + " ==>Problem with Connection Manager");}
        }
        

    }

I would be very grateful if someone could help

Declan D

[Declan Dee]

Just getting back. The code is actuall this:

    

String choice = (String)((JComboBox)combobox).getSelectedItem();

This gives you the value of what is currently selected in the Combobox. That was the problem all along.

The rest

   

public void getGenreElements () {
        Vector v = new Vector();
        try {
              String sql = "Select voorstellingen.titel, voorstellingen.Uitvoerders, voorstellingen.Prijs, " +
                      "voorstellingen.VrijePlaatsen, genres.genre " +
                      "from cultuurhuis.voorstellingen, cultuurhuis.genres " +
                      "where voorstellingen.genrenr = genres.genreNr and genre = (?)";
              
                prepStatement = databaseConnection.prepareStatement(sql);
                String choice = (String)((JComboBox)combobox).getSelectedItem();
                prepStatement.setString(1, choice);
                resultset2 = prepStatement.executeQuery();

                  while(resultset2.next()) {
                      v.addElement(resultset2.getString("Titel") +
                        " / " + resultset2.getString("Uitvoerders") + " / "
                        + "€" + resultset2.getBigDecimal("Prijs") + " / " + " Places Available: " + resultset2.getInt("VrijePlaatsen"));
                  }
                  listSelectedGenre.setListData(v);

            } catch (SQLException ex) {
                {System.out.println(ex + " ==>Problem with Connection Manager");}
            }
    }