Problem with update data to database

[vanan saravanan]

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.



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 + "%'");
}

[Jeanne Boyarsky]

Vanan,
A few observations about this code:

1) I don't see where you use rst2 is the loop. This is may be causing you some sort of problem. What are you trying accomplish with the select * from students query.
2) In stm2.executeUpdate, the where clause matches all rows with a given subject. Since subject is assigned outside of the loop, the update statement updates the same rows every time the query is run.
3) Consider using a PreparedStatment. It's clearer and more efficient when running the same query multiple times.

#2 is the reason for the behavior you described. Perhaps you want to use the key from the student table instead?

[vanan saravanan]

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 + "%'");
}

[vanan saravanan]

Anyone able to help....

Greatly appreciated.

[Paul Sturrock]

I'm trying to follow what you are trying to achieve. You get a ResultSet containing all the records from the "results" table. You then loop through this ResultSet and repeatedly update every record in this table that meets the value you assigned for subject. Why are you running the update inside a loop?

[vanan saravanan]

Thank you for your help Paul Sturrock .


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 + "%'");
[ October 19, 2006: Message edited by: vanan saravanan ]

[Paul Sturrock]

As you would; the scope of these variables is inside the loop.

Ah! I've re-read your first post, now it makes more sense. You set the value of subject once, so every time you execute your update statement, you update the same records. There doesn't seem to be any point doing this update inside the loop you use to navigate through your ResultSet - you don't do anything with the ResultSet, so why use it to control the updates?

[vanan saravanan]

I'm not sure what i'm doing is right. This is what i have in my form. Code below. I'm trying to receive the values from the dynamic textfields to save into my database. It should cycle through the fields and save to database where my subject matches. I tried to use ResultSet to retrive the values but it doesnt work. Please do advice on the correct syntax to use. Your help is greatly appreciated.

<%

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

%>
<tr>
<th scope="col"> </th>
<th scope="col"><%=Childic%></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>
<%
}
%>

[Jeanne Boyarsky]

Vanan,
Taking a step back for a minute, how do you uniquely identify a row in the students table? In other words, what is the primary key?

This needs to be used to correctly write the update statement.

[vanan saravanan]

The primary key will Childic

[vanan saravanan]

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.
[ October 19, 2006: Message edited by: vanan saravanan ]

[Jeanne Boyarsky]

Vanan,
Thanks for providing the background. Now I understand the problem and can provide a more useful answer. I'll provide some comments on each of your steps. The logic is really close.

1) It sounds like your primary key has three fields - childic, subject and level. This is fine as a primary key doesn't need to be one column. (As I side note, if this were a real production application, you would want to have another table with childic and name so this info doesn't have to be duplicated.)

2) The form needs to submit the childic and level too since those are part of the primary key. The SQL update will need them. You can use <input type="hidden" name="childic" value="<%= Childic %>" /> and the equivalent for level. That way the user doesn't see extra form fields.

3) Now the query looks more like UPDATE results SET ca1 = ?', sa1 = ?, ca2 = ?, sa2 = ?, ca1date = ?, sa1date = ?, ca2date = ?, sa2date = ? where subject like ? and childic = ? and level = ?
Notice that the where clause of the update contains three columns - one for each of the columns in your primary key.

[Jeanne Boyarsky]

In my previous post, I used "?" to represent all the values. This comes from a prepared statement as used below.

String sql = "UPDATE results SET ca1 = ?', sa1 = ?, ca2 = ?, sa2 = ?,
 ca1date = ?, sa1date = ?, ca2date = ?, sa2date = ? where subject like ? and
 childic = ? and level = ?";
 
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, ca1);
stmt.setString(2, sa1);
...
stmt.setString(11, level);
 
stmt.executeUpdate();

[edited to add line breaks]
[ October 21, 2006: Message edited by: Jeanne Boyarsky ]

[vanan saravanan]

Thank you Jeanne Boyarsky. It works perfectly.

Could you please advice me more about "...if this were a real production application, you would want to have another table with childic and name so this info doesn't have to be duplicated."

Would like to learn how that done. Like how do you reference between the two tables and the code for it.

Thank you for once again.

[Jeanne Boyarsky]

Originally posted by vanan saravanan:
Could you please advice me more about "...if this were a real production application, you would want to have another table with childic and name so this info doesn't have to be duplicated."

Would like to learn how that done. Like how do you reference between the two tables and the code for it.

Sure. There is a term called database normalization. Basically, you would want to use a level of normalization where information is in only one place.

Looking at the table, I see two entities (things) - a student and a course. So you would have:
Student - ChildIc, Name (and any other student specific info like the address)
Course - ChildIc, Subject, Level, ca1, ... (the first three columns are still the key)

Now if the student info changes, you only need to update it in one place.

To query the table, you use a SQL join:

select s.childic, s.name, c.subject, c.level, c.ca1...
from Student s, Course c
where s.childic = c.childic

Basically this queries from two tables matching on the field that must be the same. The update stays the same as from before..

[vanan saravanan]

Thank you for the advice Jeanne Boyarsky. Will see how i can update my database designs with it.