PDA

View Full Version : Updating database from JSP page

yebirtiebal
09-24-2007, 09:21 AM
Hi,

I want to update table(question(varchar), yess(int), nos)) based on certain conditions as shown in the following program. The problem is onthe line
st.executeUpdate("update opinions set yess='ys' where question='q' ");
please help me.

<html><head><title>Q&A</title>
</head>
<body>
<%@ page import="java.sql.*" %>
<%
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost/oop","esubalew","esubalewa");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select * from Opinions");



int g=0;
String group="group",yes="yess", no="nos";
String str,y,n,s, q, s1;
int ys, ns;
%>


<br><br><br>
<table cellspacing="15" cellpadding="8">

<% while(rs.next()){
g=g+1;
str=group+g;
y=yes+g;
n=no+g;
q=rs.getString("question");
s=request.getParameter(str);
ys=rs.getInt("yess");
ns=rs.getInt("nos");

if(s.equals(y)){//increase numberof yeses
ys=ys+1;
out.println(ys);
st.executeUpdate("update opinions set yess='ys' where question='q' ");

}
out.println(s);
out.println(y);
//out.println(ys);
// else{//increase number of noes
// ns=rs.getInt("nos")+1;
// st.executeUpdate("update opinions set nos='ns' where question='q'");
//}


%>
<tr>
<td><%out.println(rs.getString("question"));%>
<td><%out.println("("+ys+" Yes )");%>
<td><%out.println("("+ns+" No )"+"<br>"); %>
</tr>

<%
str="";
y="";
n="";
} //end of while loop
rs.close();
con.close();
%>

</table>


<%}
catch(ClassNotFoundException e1){
System.out.println(e1.toString());
}
catch(SQLException e2){
System.out.println(e2.toString());
}
catch(Exception e3){
System.out.println(e3.toString());
}
%>
</body>
</html>

thank you
ess
09-24-2007, 10:48 AM
try changing it to the following

st.executeUpdate("update opinions set yess='"+ys+"' where question='"+q+"' ");

I would strongly recommend using prepared statements for convenience purposes.

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Cheers,
~E
yebirtiebal
09-25-2007, 03:39 AM
Dear friend before I shift using prepaidstatement I want to try my best using the statement to fix the problem.

Your suggestion works but it is for the first row of the table. It updates and displays the first tuple but for the next iteration it doesn't update or display. I don't know why it doesn't work for the next loop? Here is the code fragment:

<table cellspacing="15" cellpadding="8">

<% while(rs.next()){
g=g+1;
str=group+g;
y=yes+g;
n=no+g;
q=rs.getString("question");
s=request.getParameter(str);
ys=rs.getInt("yess");
ns=rs.getInt("nos");

if(s.equals(y)){ //increase numberof yeses
ys=ys+1;
st.executeUpdate("update opinions set yess='"+ys+" ' where question='"+q+"' ");

}
else{ //increase number of noes
ns=rs.getInt("nos")+1;
st.executeUpdate("update opinions set nos='"+ns+" ' where question='"+q+"' ");
}


%>
<tr>
<td><%out.println(q);%>
<td><%out.println("("+ys+" Yes )");%>
<td><%out.println("("+ns+" No )"+"<br>"); %>
</tr>

<%
str="";
y="";
n="";
} //end of while loop
rs.close();
con.close();
%>
</table>