Trouble dynamically filling a table

[Former Member]

Hello,

I am trying to create a table that will dynamically be populated with values. What it must contain is the customer type (column1), and then the count (from a database) where the customer type and encounter type are equal to the values inputted (from an array being looped through). I have a screenshot of what it should look like but I'm not sure how to post it.

So, I have individually gotten the different parts to work, but when I put them together it does not work as expected.

If I just use a for loop, and hard code values (for testing), it works as it should:

String[] encounterTypes ={"Row 1","Row 2","Row 3","Row 4","Row 5","Row 6","Row 7","Row 8","Row 9","Row 10","Row 11","Row 12" };
int[] count0 = {1,2,3,4,5,6,7,8,9,10,11,12};
for (int i = 0; i < encounterTypes.length; i++) {
   row = wdContext.nodeTableNode().createTableNodeElement();
   row.setEncounter(encounterTypes<i>);
   row.setCount0(count0<i>);
   wdContext.nodeTableNode().addElement(row);
}

But what I want to do is get the count values from a database, so the code would be something like this (which only will ever return one row, and the encounter value is always the last one - Row 12 for this example):

String[] encounterTypes ={"Row 1","Row 2","Row 3","Row 4","Row 5","Row 6","Row 7","Row 8","Row 9","Row 10","Row 11","Row 12" };
String[] customerTypes ={"Architects","Architectural Reps","Contractors","Distributors","Engineers","Food Equipment Dealers",
"Food Service Consultants","Food Service Reps","Mechanical Reps",
"National Accounts","OEM's","Others","Owners" };

try {
   String url ="jdbc:oracle:thin:@(DESCRIPTION="
	+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)"		+ "(HOST=<HOST_NAME>)(PORT=<PORT_NUM>)))"
	+ "(CONNECT_DATA=(SERVICE_NAME=<SVC_NAME>)(SERVER=DEDICATED)))";
   String username = <USERNAME>;
   String password = <PW>;

   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection con =DriverManager.getConnection(url, username, password);

   ITableNodeElement row =wdContext.nodeTableNode().createTableNodeElement();

   int[] count0 = new int[12];
			
   for (int i = 0; i < encounterTypes.length; i++) {
      row = wdContext.nodeTableNode().createTableNodeElement();
				
      PreparedStatement stmt =con.prepareStatement("select count(*) from tablename where customer_type=? and encounter_type=?");
      stmt.setString(1, customerTypes<i>);
      stmt.setString(2, encounterTypes<i>);
      con.setAutoCommit(false);

      ResultSet results = stmt.executeQuery();
      wdContext.nodeTableNode().invalidate();
      while (results.next()) {
         String encounter = encounterTypes<i>;
         int x = 0;
         //Set array values to be added
         count0[x] = results.getInt("COUNT(*)");
         wdContext.nodeTableNode().addElement(row);
         x++;
      }
				
      row.setEncounter(encounterTypes<i>);
      row.setCount0(count0<i>);
      wdContext.nodeTableNode().addElement(row);

      }

  } catch (Exception e) {
	e.getMessage();
	e.printStackTrace();
}

FYI - for some reason when I put the code here, at times the index brackets look like <> instead of [], but I am coding them as [].

Can anyone see what I am doing wrong in the code?

Thanks!

[Former Member · Accepted solution]

Why do you call

wdContext.nodeTableNode().invalidate();

inside the loop?

Armin

[Former Member · Accepted solution]

Thank you, thank you, thank you! That one little line of code cost me lots of time! I removed that and made some other little changes and now it is working perfectly!