jsp checkbox display for loop value according to array will have duplication

Why are u splitting the string? Can you tell me how many rows are returned from your database for your query and also paste one of the row values here so that I can have a look at it.

In cooking table, there are 2 fields: id, cook
1 milk
2 juice
3 sauce
.....totally 10 rows of data

In member table, there is a field 'cooking'
It records the checkbox (checked values) in a string, e.g. 2,5,9,10

When the member comes back to the checkbox page, I need to do databind with those input values in cooking field of member table and the cooking table. So that member knows what did he chose last time.

I need to split the string 2,5,9,10, when it matches with the id in cooking table, it is checked.

But, the for loop will display the checkbox same value for the array length of the split string.

I tried 2 days but still cannot think how can fix it.

Could you help?

First of all I think your query is wrong. If you want to join a table you need to specify the columns to join from both tables. From your table structure what you can actually do is first query the member table based on the member id the values for cooking (e.g. 2,5,9,10 ) from member table and store this in a variable (let us call it varCooking). Then query all the cooking table values loop through the results and check the id exists in the varCooking variable. You will not have do any splits you can compare if the string exists using java string functions. I dont remember the function names and all because I have used around 4 years back. Hope this helps..

Can you show me the code? Is it need 2 queries?

I am not an expert in JSP but you would want to create 2 queries as I said earlier in post 23584214. That post contains all you need to do this thing working

Yes you can manage it with 2 queries.. your code will look something like below..
no need to manage a string array here

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
<%  
String sql = "SELECT s.id as id, s.cook as cook FROM cooking s";
String sql1 ="Select m.cocking as e from member m where m.id="=myID;
ResultSet rs = s.executeQuery(sql);
ResultSet rs1=s.executeQuery(sql1);
rs1.next();
String selected= rs1.getString("e");
while (rs.next()) {
%>
<input TYPE=checkbox NAME="cooking" VALUE="<%=rs.getInt("id")%>" <%if(select.indexOf(rs.getString("id"))>-1){out.print("checked");}%>><%=rs.getString("cook")%><BR>
<%
}
}%>

Select allOpen in new window

the code throws exception:
throws at while (rs.next()) {

javax.servlet.ServletException: java.sql.SQLException: Operation not allowed after ResultSet closed

com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:644)
com.mysql.jdbc.ResultSet.next(ResultSet.java:6663)

and why it does not need array to split the rs1.getString("e"); which has the value "1,3,5," for example?

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
<%  
String sql = "SELECT s.id as id, s.cook as cook FROM cooking s";
String sql1 ="Select m.cooking as e from member m where m.id=" + myID;
ResultSet rs = s.executeQuery(sql);
ResultSet rs1=s.executeQuery(sql1);
rs1.next();
String selected= rs1.getString("e");
while (rs.next()) { //throws exception
%>
<input TYPE=checkbox NAME="cooking" VALUE="<%=rs.getInt("id")%>" <%if(selected.indexOf(rs.getString("id"))>-1){out.print("checked");}%>><%=rs.getString("cook")%><BR>
<%
}
}%>
<%
rs.close();
rs1.close();
s.close();
con.close();
%>	
<input type="submit" name="Submit" value="cook" >

Select allOpen in new window

oops sorry I missed the fact that you will need anotherStatement as well.

create another statement say s1 ( at the same place where you are initiating s).
and change
ResultSet rs1=s.executeQuery(sql1);

to
ResultSet rs1=s1.executeQuery(sql1);

you dont need the string because I am using (indexOf) to find your string around.

The databind works now, thanks for help.

when I select checkbox numbers: 4
it insert into the field value '4,'

when i select checkbox numbers: 4,8
it inserts into the field value '3,5,2,24,4,2,6,10,4,8,' instead of '4,8,'

What happen to my java code below?

private int myID=1;
private String in[];
in = request.getParameterValues("cooking");
for(int i=0; i<in.length;i++){
inappend += in[i]+",";
}
save();
 
public void save() throws MemberException
{
MySQLConnector db = new MySQLConnector();
Connection con = db.Getcon();
String upd;
int isWrite;
Statement stmt=null;
upd = "update member set cooking";
upd = upd + "='"+inappend+"'";
upd = upd + " where id = '" + myID +"';";
try
{
stmt = con.createStatement();
}

Select allOpen in new window

you are using global variables ( or class level variables)..

I think it was caused by that problem. I set those variables insides doGet() and pass the inappend to method save(String inappend) it works now.

Thank you very much for your help.

sorry, i got to add points

Excellent advice and very detail guideline. I learnt a lot from Kuldeepchaturvedi and he is very professional.

Sorry, it does not work if I select an item with id = 123, (e)
The checkbox will check 1,2,3,12,123.

String selected= rs1.getString("e");
selected = 123

But, selected.indexOf(rs.getString("id"))>-1

indexOf will get 1,2,3,12,123 even the e value in database is 123.

String sql = "SELECT s.id as id, s.cook as cook FROM cooking s";
String sql1 ="Select m.cooking as e from member m where m.id=" + myID;
ResultSet rs = s.executeQuery(sql);
ResultSet rs1=s.executeQuery(sql1);
rs1.next();
String selected= rs1.getString("e");
while (rs.next()) { 
%>
<input TYPE=checkbox NAME="cooking" VALUE="<%=rs.getInt("id")%>" <%if(selected.indexOf(rs.getString("id"))>-1){out.print("checked");}%>><%=rs.getString("cook")%><BR>

Select allOpen in new window

yup you are right.. lets do it differently



String selected= rs1.getString("e");
String [] arrSel = selected.split("\\,");
java.util.ArrayList alSel= new ArrayList(Arrays.asList(arrSel));
while (rs.next()) {
%>
<input TYPE=checkbox NAME="cooking" VALUE="<%=rs.getInt("id")%>" <%if(alSel.contains(rs.getString("id"))>-1){out.print("checked");}%>><%=rs.getString("cook")%><BR>


Also for your other question, I will have to look at it later, have a busy day today.

<%if(alSel.contains(rs.getString("id"))>-1)

needs to changed to be

<%if(alSel.contains(rs.getString("id")))

and it works now

Thank you very much

By the way, do you know how to set and get Vector object's parameters?