Re: VWP - dropDown bound to DB question

Sergei,

There is a sample app for Creator from which you can get ideas: Vehicle Incident Report at http://developers.sun.com/prodtech/javatools/jscreator/reference/code/sampleapps/index.jsp

Here is the query:

        searchVehicleRowSet.setCommand("SELECT ALL VIR.VEHICLE.STATEID, \n                    VIR.VEHICLE.LICENSEPLATE, \n                    VIR.VEHICLE.MAKE, \n                    VIR.VEHICLE.MODEL, \n                    VIR.VEHICLE.COLOR, \n                    VIR.STATE.STATEID, \n                    VIR.STATE.STATENAME \nFROM VIR.VEHICLE\n          INNER JOIN VIR.STATE ON VIR.VEHICLE.STATEID = VIR.STATE.STATEID\nWHERE VIR.VEHICLE.STATEID  LIKE  ?\n          AND VIR.VEHICLE.LICENSEPLATE  LIKE  ?\n          AND VIR.VEHICLE.MAKE  LIKE  ?\n          AND VIR.VEHICLE.MODEL  LIKE  ?\n          AND VIR.VEHICLE.COLOR  LIKE  ? ");


Here is the essential code from the search page:

   public String searchbutton_action() {     
        String stateId = (String) getStatedropdown().getSelected();
       
        if (stateId == null) {
            error("Select a State.");
            return null;
        }
       
        // wildcard state
        if (stateId.equals("xx")) {
            stateId = "%";
        }
       
        String licensePlate = (String) getLicenseplatefield().getText();
        if (licensePlate == null || licensePlate.equals("")) {
            licensePlate = "%";
        } else {
            licensePlate = licensePlate.trim();
            if (licensePlate.length() > 0) {
                if (licensePlate.indexOf('%') == -1) {
                    // user did not specify pattern match
                    licensePlate = "%" + licensePlate + "%";
                }
            }
        }
       
        String make = (String) getMakefield().getText();
        if (make == null || make.equals("")) {
            make = "%";
        } else {
            make = make.trim();
            if (make.length() > 0) {
                if (make.indexOf('%') == -1) {
                    // user did not specify pattern match
                    make = "%" + make + "%";
                }
            }
        }
               
        String model = (String) getModelfield().getText();
        if (model == null || model.equals("")) {
            model = "%";
        } else {
            model = model.trim();
            if (model.length() > 0) {
                if (model.indexOf('%') == -1) {
                    // user did not specify pattern match
                    model = "%" + model + "%";
                }
           }
        }
                       
        String color = (String) getColorfield().getText();
        if (color == null || color.equals("")) {
            color =  "%";
        } else {
            color = color.trim();
            if (color.length() > 0) {
                if (color.indexOf('%') == -1) {
                    // user did not specify pattern match
                    color =  "%" + color + "%";
                }
            }
        }
               
        try {
            CachedRowSet searchVehicleRowSet = getSessionBean1().getSearchVehicleRowSet();
           
            searchVehicleRowSet.setObject(1, stateId);
            searchVehicleRowSet.setObject(2, licensePlate);
            searchVehicleRowSet.setObject(3, make);
            searchVehicleRowSet.setObject(4, model);
            searchVehicleRowSet.setObject(5, color);
           
            searchVehicleRowSet.execute();
            searchVehicleRowSet.last();
            int currentRow = searchVehicleRowSet.getRow();
            if (currentRow == 0) {
                error("No vehicles found.");
                return null;
            } else if (currentRow == 1) {
                return "report";
            } else {
                return "select";
            }
        } catch (SQLException sqe) {
            error(sqe.getMessage());
        }
        return null;
    }




Sergei Mutovkin wrote:

May I ask an additional question?
My situation is similar but a bit more elaborate. What I have is a number of
drop down lists which will filter out data in the table as user
selects the options.
By default I want to show all data from the SQL query in the DB table, however
once user selects one of the options I would like to update the table with only
data containing chosen data. Once he selects another drop down option, I want
to combine that one together with previously chosen, so forth... I wonder what
are the possible approaches to this problem?

First, I have thought of parametrized queries (to Derby DB for now), however,
this does not work for me since I have to immediately provide some default
parameters as it does not look like I can say something like:
"WHERE t."Name" = '*' " so that it would ignore the filter all
together, and show
all data from the query without WHERE. It seems, it not possible to
have a number
of parameters in SQL query that will be ignored if desired.

-- 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chris Kutler
Sun Java Studio Creator Tutorials
http://blogs.sun.com/divas