Problem with update data to database

[shaun09]

I'm trying to cycle through a series of dynamic fields and update the value to the database. But the value of the last field is the one retrived and updated to all fields in the loop. How do i solve this problem. Thank you so so much for your help.

rst2 = stm1.executeQuery("select * from students");
String subject = request.getParameter("subject");

for(int i = 0; rst2.next(); i++){
String ca1 = request.getParameter("ca1_"+i);
String sa1 = request.getParameter("sa1_"+i);
String ca2 = request.getParameter("ca2_"+i);
String sa2 = request.getParameter("sa2_"+i);
//String query2 = "INSERT into results(ca1,sa1,ca2,sa2) values ('"+ca1+"','"+sa1+"','"+ca2+"','"+sa2+"')";
stm2.executeUpdate("UPDATE results SET ca1 ='"+ca1+"', sa1 ='"+sa1+"', ca2 ='"+ca2+"', sa2 ='"+sa2+"' where subject like '" + subject + "%'");
}

[masijade]

I think you can extropolate my answer from your other thread to fit this one. It seems to be the same thing.

[shaun09]

I tried this its also not working. What i plan to acheive is that all fields that match the value of subject must be updated. The value of subject is taken from a drop down box. This portion works. The problem is that it updates all fields with the same value. I think its because the update is in a loop? I'm not sure how to rectify my coding. Please help. Thank you for your kind assistance.

String ca1date = request.getParameter("dc1");
String sa1date = request.getParameter("dc2");
String ca2date = request.getParameter("dc11");
String sa2date = request.getParameter("dc22");
String subject = request.getParameter("subject");

rst2 = stm1.executeQuery("select * from results");

for(int i = 0; rst2.next(); i++){

String ca1 = request.getParameter("ca1_"+i);
String sa1 = request.getParameter("sa1_"+i);
String ca2 = request.getParameter("ca2_"+i);
String sa2 = request.getParameter("sa2_"+i);

stm2.executeUpdate("UPDATE results SET ca1 ='"+ca1+"', sa1 ='"+sa1+"', ca2 ='"+ca2+"', sa2 ='"+sa2+"', ca1date ='"+ca1date+"', sa1date ='"+sa1date+"', ca2date ='"+ca2date+"', sa2date ='"+sa2date+"' where subject like '" + subject + "%'");
}

[shaun09]

Anyone able to help....

Greatly appreciated.

[shaun09]

When i move the update out of the loop i get the following errors.

Undefined variable: ca1
Undefined variable: sa1
Undefined variable: ca2
Undefined variable: sa2

String ca1date = request.getParameter("dc1");
String sa1date = request.getParameter("dc2");
String ca2date = request.getParameter("dc11");
String sa2date = request.getParameter("dc22");
String subject = request.getParameter("subject");

rst2 = stm1.executeQuery("select * from results");

for(int i = 0; rst2.next(); i++){

String ca1 = request.getParameter("ca1_"+i);
String sa1 = request.getParameter("sa1_"+i);
String ca2 = request.getParameter("ca2_"+i);
String sa2 = request.getParameter("sa2_"+i);
}

stm2.executeUpdate("UPDATE results SET ca1 ='"+ca1+"', sa1 ='"+sa1+"', ca2 ='"+ca2+"', sa2 ='"+sa2+"', ca1date ='"+ca1date+"', sa1date ='"+sa1date+"', ca2date ='"+ca2date+"', sa2date ='"+sa2date+"' where subject like '" + subject + "%'");

[masijade]

Because you are defining the variable inside the loop. Change the following:

for(int i = 0; rst2.next(); i++){ 
  String ca1 = request.getParameter("ca1_"+i);
  String sa1 = request.getParameter("sa1_"+i);
  String ca2 = request.getParameter("ca2_"+i);
  String sa2 = request.getParameter("sa2_"+i);
}

to

String ca1 = "";
String sa1 = "";
String ca2 = "";
String sa2 = "";

for(int i = 0; rst2.next(); i++){ 
  ca1 += request.getParameter("ca1_"+i) + " ";
  sa1 += request.getParameter("sa1_"+i) + " ";
  ca2 += request.getParameter("ca2_"+i) + " ";
  sa2 += request.getParameter("sa2_"+i) + " ";
}

ca1 = ca1.trim();
sa1 = sa1.trim();
ca2 = ca2.trim();
sa2 = sa2.trim();

[shaun09]

I'm sorry but it does not work. The value is still taken as the last value and updated to all fields. Also now the strings are joined together...

[shaun09]

I think my logic of the problem is wrong. I'm still learning and need all the help i can. This what I’m trying to achieve. I have 6 levels. Primary1-Primary6. Each level takes 4 different subjects. Each subject has four exams in a year. Ca1,sa1,ca2,sa2.

I'm creating a form where teachers can record the grades of the exam into the system so they can retrieve and modify the results anytime.

The problems I’m facing are

1) Database design
I have Childic,Name,Subject,Level,ca1,sa1,ca2,sa2 fields in a table called results. I understand that this is the wrong way to design a table as primary key is to be Childic. But I’m having the four subject assigned to one Childic. So now the database is like

Childic name, Subject, Level, ca, sa1, ca2, sa2
12345 xyz English Primary1
12345 xyz Science Primary1
12345 xyz Maths Primary1
12345 xyz History Primary1

2)The form to input the data. The teacher selects which subject to update the results. I then create dynamic tables to store the data.

String subject = request.getParameter("subject");
rst2 = stm1.executeQuery("select * from results where subject like '" + subject + "%'");
String Childic = "";
String Childname = "";

for(int i = 0; rst2.next(); i++){
Childic = rst2.getString("Childic") ;
Childname = rst2.getString("Name") ;
%>
<tr>
<th scope="col"> </th>
<th scope="col"><%=Childic%></th>
<th scope="col"><%=Childname%></th>
<th scope="col"><input name="ca1_<%=i%>" type="text" id="ca1" size="7" /></th>
<th scope="col"><input name="sa1_<%=i%>" type="text" id="ca2" size="7" /></th>
<th scope="col"><input name="ca2_<%=i%>" type="text" id="ca3" size="7" /></th>
<th scope="col"><input name="sa2_<%=i%>" type="text" id="ca4" size="7" /></th>
<th scope="col"> </th>
</tr>
<%

}
%>

3)I'm trying to update the record into the database. See previous post for coding.

I'm sorry if the logic of the design and programming is wrong. If someone is willing to guide me, I’m willing to learn and try. All your help is greatly appreciated.