VWP - dropDown bound to DB question

[Paul Belleville]

What if I have a dropDown bound to a DB table so it provides the choices.

However, I also want to add an empty selection since null is a valid choice for this field in which this data will eventually be saved.

I guess I can add a null to the selection DB table.. is there a better way?

 

Paul

[Don Albertson-2]

Paul Belleville said (on or about) 02/14/2007 14:39:
> What if I have a dropDown bound to a DB table so it provides the choices.
> However, I also want to add an empty selection since null is a valid
> choice for this field in which this data will eventually be saved.
> I guess I can add a null to the selection DB table.. is there a better way?
>  
> Paul

I usually begin my dropDown lists and comboBoxes with a String
that says "<Select Something From This List>".  If that is
the selected Object from the list, then somewhere in my code
there will be a way to handle the fact that what was selected
(or not selected) isn't a value that matches in the db and
my code will ensure that the db value is set to NULL.

I find NULL values need extra care in many places.  For example
if the db column is a number column and the value is NULL
ResultSet.getLong() will return 0 which is not quite the same
thing as NULL.

[Paul Belleville]

So, if the dropDown is bound to a DB table, how do you add in this extra option "select something from list" into the dropDown choices?

Paul

Don Albertson <Don.Albertson@...> wrote:

Paul Belleville said (on or about) 02/14/2007 14:39:
> What if I have a dropDown bound to a DB table so it provides the choices.
> However, I also want to add an empty selection since null is a valid
> choice for this field in which this data will eventually be saved.
> I guess I can add a null to the selection DB table.. is there a better way?
>
> Paul

I usually begin my dropDown lists and comboBoxes with a String
that says "". If that isthe selected Object from the list, then somewhere in my codethere will be a way to handle the fact that what was selected(or not selected) isn't a value that matches in the db andmy code will ensure that the db value is set to NULL.I find NULL values need extra care in many places. For exampleif the db column is a number column and the value is NULLResultSet.getLong() will return 0 which is not quite the samething as NULL.

[titan]

Add the extra option in your prerender method, i.e.

        if (dropDown.getSelected() == null) {

// add value to dataprovider
            dropDownDataProvider.setCursorRow(dropdownDataProvider.appendRow());
            dropDownDataProvider.setValue("VALUE", -1);
            dropDownDataProvider.setValue("DISPLAY_NAME", "Select something from list");

// set selected row for the dropdown list
            dropDown.setSelected(-1);
        }

====================================================

Paul Belleville wrote:

So, if the dropDown is bound to a DB table, how do you add in this extra option "select something from list" into the dropDown choices?
  Paul

Don Albertson <[e-mail]> wrote:
  Paul Belleville said (on or about) 02/14/2007 14:39:
> What if I have a dropDown bound to a DB table so it provides the choices.
> However, I also want to add an empty selection since null is a valid
> choice for this field in which this data will eventually be saved.
> I guess I can add a null to the selection DB table.. is there a better way?
>
> Paul

I usually begin my dropDown lists and comboBoxes with a String
that says "". If that isthe selected Object from the list, then somewhere in my codethere will be a way to handle the fact that what was selected(or not selected) isn't a value that matches in the db andmy code will ensure that the db value is set to NULL.I find NULL values need extra care in many places. For exampleif the db column is a number column and the value is NULLResultSet.getLong() will return 0 which is not quite the samething as NULL.

[Don Albertson-2]

Paul Belleville said (on or about) 02/14/2007 16:17:
> So, if the dropDown is bound to a DB table, how do you add in this extra
> option "select something from list" into the dropDown choices?
> Paul
>

I wouldn't use a bound control when a true db NULL was a valid option.

> */Don Albertson <Don.Albertson@...>/* wrote:
>
>     Paul Belleville said (on or about) 02/14/2007 14:39:
>      > What if I have a dropDown bound to a DB table so it provides the
>     choices.
>      > However, I also want to add an empty selection since null is a valid
>      > choice for this field in which this data will eventually be saved.
>      > I guess I can add a null to the selection DB table.. is there a
>     better way?
>      >
>      > Paul
>
>     I usually begin my dropDown lists and comboBoxes with a String
>     that says "
>

--
When you're all locked up in pain
be careful who you blame
This too will pass
--R.Crowell

[yossarian]

Paul Belleville wrote:

What if I have a dropDown bound to a DB table so it provides the choices.
  However, I also want to add an empty selection since null is a valid choice for this field in which this data will eventually be saved.
  I guess I can add a null to the selection DB table.. is there a better way?
   
  Paul

Another approach is to use a UNION in the SQL statement eg:

select null,'' union
select aircraftid, shortreg from aircraft

[Sergei Mutovkin]

On 2/14/07, titan <lawndale99@...> wrote:

>
> Add the extra option in your prerender method, i.e.
>
>         if (dropDown.getSelected() == null) {
>
> // add value to dataprovider
>
> dropDownDataProvider.setCursorRow(dropdownDataProvider.appendRow());
>             dropDownDataProvider.setValue("VALUE", -1);
>             dropDownDataProvider.setValue("DISPLAY_NAME", "Select something
> from list");
>
> // set selected row for the dropdown list
>             dropDown.setSelected(-1);
>         }
>

There are two problems with this approach it seems:
1. Item is added at the very end of the list (not a big issue but
really inconsistent with the rest of the pages on the Internet).
2. If you hit the browser's Refresh button on just loaded page, you
would see your item two times.. click more - more items are added to
the data provider.

I do not yet know how to resolve #1 (I'm just a few days into a whole
JEE stuff), but for #2 it can be easily avoided by adding a property
to the SessionBean which will indicate if it is the first time page is
loaded during the session.

--
Sergei

[Sergei Mutovkin-2]

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]

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.

[1world1love]

I wonder how hard it would be to build this in to the dropdown component.
You could have a boolean property called "includeNotSelectedItem". If it is
true, then the component would render a "Not Selected" item as the first
item. If this item were selected, it would simply return null.

It seems that this sort of thing is common enough that it would be easier to
manage on the rendering side than mixing it up with all of your data logic,
especially where a simple binding to a datasource would be much cleaner.

j  

-----Original Message-----
From: msergei@... [mailto:msergei@...] On Behalf Of Sergei
Mutovkin
Sent: Friday, February 23, 2007 8:30 AM
To: nbusers@...
Subject: Re: [nbusers] VWP - dropDown bound to DB question

On 2/14/07, titan <lawndale99@...> wrote:

>
> Add the extra option in your prerender method, i.e.
>
>         if (dropDown.getSelected() == null) {
>
> // add value to dataprovider
>
> dropDownDataProvider.setCursorRow(dropdownDataProvider.appendRow());
>             dropDownDataProvider.setValue("VALUE", -1);
>             dropDownDataProvider.setValue("DISPLAY_NAME", "Select
> something from list");
>
> // set selected row for the dropdown list
>             dropDown.setSelected(-1);
>         }
>

There are two problems with this approach it seems:
1. Item is added at the very end of the list (not a big issue but really
inconsistent with the rest of the pages on the Internet).
2. If you hit the browser's Refresh button on just loaded page, you would
see your item two times.. click more - more items are added to the data
provider.

I do not yet know how to resolve #1 (I'm just a few days into a whole JEE
stuff), but for #2 it can be easily avoided by adding a property to the
SessionBean which will indicate if it is the first time page is loaded
during the session.

--
Sergei

[Chris Kutler]

Another approach is to add the extra option in the query, such as

select person.personid, person.name from person union select -1, 'Select
Something' from person...

Sergei Mutovkin wrote:

> On 2/14/07, titan <lawndale99@...> wrote:
>
>>
>> Add the extra option in your prerender method, i.e.
>>
>>         if (dropDown.getSelected() == null) {
>>
>> // add value to dataprovider
>>
>> dropDownDataProvider.setCursorRow(dropdownDataProvider.appendRow());
>>             dropDownDataProvider.setValue("VALUE", -1);
>>             dropDownDataProvider.setValue("DISPLAY_NAME", "Select
>> something
>> from list");
>>
>> // set selected row for the dropdown list
>>             dropDown.setSelected(-1);
>>         }
>>
> There are two problems with this approach it seems:
> 1. Item is added at the very end of the list (not a big issue but
> really inconsistent with the rest of the pages on the Internet).
> 2. If you hit the browser's Refresh button on just loaded page, you
> would see your item two times.. click more - more items are added to
> the data provider.
>
> I do not yet know how to resolve #1 (I'm just a few days into a whole
> JEE stuff), but for #2 it can be easily avoided by adding a property
> to the SessionBean which will indicate if it is the first time page is
> loaded during the session.
>
> --
> Sergei

--

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

[Kishm]

Hi Folks,

Please see this new techtip which describes how to accomplish what you are looking for.

http://www.netbeans.org/kb/55/dropdowncomp.html

Thanks
Kish

yossarian wrote:

Paul Belleville wrote:

What if I have a dropDown bound to a DB table so it provides the choices.
  However, I also want to add an empty selection since null is a valid choice for this field in which this data will eventually be saved.
  I guess I can add a null to the selection DB table.. is there a better way?
   
  Paul

Another approach is to use a UNION in the SQL statement eg:

select null,'' union
select aircraftid, shortreg from aircraft