This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes Problem with update data to database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with update data to database" Watch "Problem with update data to database" New topic
Author

Problem with update data to database

vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
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
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
Anyone able to help....

Greatly appreciated.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
The primary key will Childic
vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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



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

Joined: Jun 02, 2006
Posts: 95
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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:


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
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
Thank you for the advice Jeanne Boyarsky. Will see how i can update my database designs with it.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem with update data to database
 
Similar Threads
How to save value from ResultSet as a single value in database
array
Problems understanding EJB transactions and isolation
Unsure how to create and name dynamic Textfield
Returning values from a one method in a class....to another method in a class?