PDA

View Full Version : Populating-cascaded-combo-boxes-from-SQL-database


merlbemo
Dec 5, 2015, 12:16 AM
Using java application I tried to fill 3 combo boxes (A,B and C) from my SQL database. A is populated in initial components, B is populated when an item in A is selected and C is populated when B is populated. I used a below way and its working only for two combo boxes, but when I tried to do it for three combo boxes its failed and it just initialized one value on each combo box. Can any one check my code and determine the issue?
and I would like to confirm this code working for two combo boxes successfully?
---some notes
that's not whole code I removed generated codes by NetBeans,and I made it on jfram as sample to be concentrated for my point

database table name is (MySQLTable)
consists of three columns ( A_Items, B_Items, C_Items)
my class name (fillthreecombo)
I am using (Netbeans 8.0.2)(SQL Server Management 2008)

and for who concerned I uploaded whole sample in this link

TinyUpload.com - best file hosting solution, with no limits, totaly free (http://s000.tinyupload.com/?file_id=05686086189317079642)

but with this names
class name (ManageUserTasksNew)

combo boxes A, B and C will be jComboBox1, jComboBox2 and jComboBox3
database table name is tasks
consists of three columns (TeamName, UserName, TaskName)



package PackageName;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;

public class fillthreecombo extends javax.swing.JFrame {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rst = null;

String comboBox1text;
String comboBox2text;
String comboBox3text;

public fillthreecombo() {
initComponents();
fillComboA();
conn = Connect.connect();
}
// fill first combo box
private void fillComboA(){
try{
conn = Connect.connect();
String sql = "SELECT DISTINCT A_Items FROM MySQLTable ";
pst = conn.prepareStatement(sql);
rst = pst.executeQuery();

while(rst.next()){
String AItems = rst.getString("A_Items");
jComboBox1.addItem(AItems);
}
} catch(Exception ex) {
JOptionPane.showMessageDialog(null, ex.getMessage());
}
}

private void jComboBox1ActionPerformed(java.awt.event.ActionEve nt evt) {
// remove items on B combo box for each selection in A combobox
int itemCount = jComboBox2.getItemCount();
for(int i=0;i<itemCount;i++){
jComboBox2.removeItemAt(0);
}
//fill second combo box
comboBox1text = String.valueOf(jComboBox1.getSelectedItem());
if (!comboBox1text.isEmpty() || comboBox1text != null) {
// get data from DataBase with Distinct

try {
String sql =
"SELECT Distinct B_Items FROM MySQLTable WHERE A_Items = '"
+ comboBox1text + "' ";
pst = conn.prepareStatement(sql);
rst = pst.executeQuery();
while(rst.next()){
String BItems = rst.getString("B_Items");
jComboBox2.addItem(BItems);
}
} catch(Exception ex) {
JOptionPane.showMessageDialog(null, ex.getMessage());
}
}
}

private void jComboBox2ActionPerformed(java.awt.event.ActionEve nt evt) {
// remove items from last selection
int itemCount = jComboBox3.getItemCount();
for(int i=0;i<itemCount;i++){
jComboBox3.removeItemAt(0);
}
//fill third combo box
comboBox2text = String.valueOf(jComboBox2.getSelectedItem());
if (!comboBox2text.isEmpty() || comboBox2text != null) {
// get data from DataBase with Distinct
try{
String sql =
"SELECT Distinct C_Items FROM tasks WHERE B_Items = '"
+ comboBox2text + "' ";
pst = conn.prepareStatement(sql);
rst = pst.executeQuery();
while(rst.next()){
String CItems = rst.getString("C_Items");
jComboBox3.addItem(CItems);
}
}catch(Exception ex){
JOptionPane.showMessageDialog(null, ex.getMessage());
}
}
}

private void jComboBox3ActionPerformed(java.awt.event.ActionEve nt evt) {

}
}

ScottGem
Dec 5, 2015, 07:15 AM
I know almost nothing about Java coding. But I do know about Cascading comboboxes and database design. In a cascading combo the selection list is filtered by the selection in a previous combo. So I'm not sure whether you deal with all three combos in the same code procedure. After the first selection, you need to set the selection of the second combo and after the selection of the second combo you need to then set the selection list for the third combo. Note also that only the selection in the third combo should be stored. Since that selection is dependent on the selection of the other two, you can get to the other two from the third.

For example, selection a car model. Lets say say you are trying to indicate the selection of a 2015 Ford Edge SE.

So first you select Ford as your manufacturer. The SQL for your that combo would be:

SELECT MfgID, MfgName FROM tblMfgs ORDER BY MfgName

After that selection, you set the SQL for the second combo to:

SELECT MakeID, MakelName FROM tblMakes WHERE MfgID = Selection1

So the user only sees Ford makes and selects Edge. Then you adjust the Year combo to:

SELECT ModelYear FROM tblModelYears WHERE MakeID = Selection2

Here the user selects 2015, which then sets the list for the final combo:

SELECT ModelID, ModelName, from tblModels WHERE ModelYear = Selection3

So the final list will show Edge models From which the user would select SE.

merlbemo
Dec 5, 2015, 12:34 PM
That's what I did, you can find it in post, but it did not work, I reviewed code a lots and I wrote it again to be insure that is no mistake. Any way thank you

ScottGem
Dec 5, 2015, 01:58 PM
Like I said, I don't know Java coding so I can't tell whether the coding is correct or not. I can only explain generally how cascading combos work.

merlbemo
Dec 6, 2015, 12:29 PM
sorry, I think I missed something here

Is there a difference between

your statement
SELECT MakeID, MakelName FROM tblMakes WHERE MfgID = Selection1

my statement
SELECT MakeID, MakelName FROM tblMakes WHERE MfgName = Selection1

without MfgID

i mean did you mean something specific by using id instead of the column name

ScottGem
Dec 6, 2015, 12:32 PM
You should not be repeating the MfgName in other tables. It should be in the Mfgs table only. You should be using a numeric code as the foreign key in your other tables.

merlbemo
Dec 6, 2015, 12:41 PM
But it wouldn't be difference in our example here without MfgID

ScottGem
Dec 8, 2015, 06:19 AM
It is a design issue within your database.. Repeating the name uses more storage then a coded ID.

CravenMorhead
Dec 8, 2015, 08:14 AM
I Think this might be a java issue. You select something in combobox1, it populates combobox2. And you're done. You don't perform any action that would cause jComboBox2ActionPerformed to be called. If it was triggered (Put debug code in here to confirm this) then chances are NOTHING is select in combobox2 so when you execute the statement you're select where is empty.

The bottom line is that you're relying too much on the Java framework to do what you need to do. You need to simplify your code to do what you want it to do instead of relying on Java to do it for you. So what you need to do is:

(This is puesdo code, you need to write the java.)


OnCombo1SelectionChanged()
{
itemA = getcomobobox1String();

itemBList = select itemB from Table where itemA=itemA;

i=0;
for all items in itemBList
{
itemClist.add(select itemC from Table where itemB = itemB[i++];
}

// At this point you have what you want to put into the comboboxB/C.

comboboxB = itemBList;
comboboxC = itemCList;

}


Here you get the information you want and YOU do what you need to do to fill on your boxes. YOU are in control here, not the Java frame work. This is a more robust solution.

merlbemo
Jan 9, 2016, 03:12 AM
the correct way it shouldn't be in JComboBox1 and JComboBox2 **Action Performed** but it should be in JComboBox1 and JComboBox2 **Item State Changed** and I found good video to explain similar case https://www.youtube.com/watch?v=ZEgXgJwm_DM

but in our case you shouldn't close result set and preparestament in JComboBox1 and JComboBox2 **Action Performed** in finally block