Thread: I have a problem with this java code for database access

I have this code through which I am first retrieving a single value fro a row in database table using statement command.This is working fine.After this I am updating this value and want to reinsert it into database at its previous position using preparedstatement command.But at this it is showing an exception that "A character, token, or clause is invalid or missing".My database table has 5 columns and 4 rows as of now.the 5 columns are-Q_NO,option1,option2,option3,option4.I want that the code at first time should only insert value in first row 2nd column i.e. Q1 and option1.

 if(request.getParameter("radio"+i).equals("radio1")) {
                System.out.println("Radio button 1 was selected.");
                String QueryString = "select * from status where Q_No='Q1'";
 
  		      rs = statement.executeQuery(QueryString);
  		      while(rs.next()){
  		      System.out.println(rs.getInt(2));
                count1=rs.getInt(2);
  		      }
                count1++;
                System.out.print("coun1 incremented to"+count1);
 
                String insert="insert into administrator.status(OPTION1) values(?) where Q_NO=?";
   	    	 PreparedStatement psmt=connection.prepareStatement(insert);
   	       //psmt.setString(1,"Q1");
                psmt.setInt(1,count1);
                psmt.setString(2,"Q1");
                psmt.executeUpdate();
 
            }

Do you want to INSERT, or UPDATE? INSERT will create new rows, UPDATE will change existing rows

Originally Posted by Sean4u

Do you want to INSERT, or UPDATE? INSERT will create new rows, UPDATE will change existing rows

Ya that problem can be solved by using update but actually the problem is that I have a jsp page where I am accessing data from the database and showing the data in table on the browser with radio buttons in between.And then there is a submit button on whose click the control is transferred to a servlet where I reading the status of the radio buttons and inserting in the new database table the response accordingly.I am able to read that in a row out of the four radio buttons which was clicked but I am unable to transfer the option (which is a string) from the jsp page to the servlet

Below is my jsp code where I am displaying data from database in the table.

index.jsp

 
   <form name="myform" id="myform" action="/proj/Test" method="get">
 
    <input type="text" name="Emp_id">
 
      <TABLE cellpadding="15" border="1" style="background-color: #ffffcc;">
 
      <%
 
      while (rs.next()&&(i<=4)) {
 
      %>
 
       <TR>
        <TD><%=rs.getString(1)%></TD>                                                                                                          //I want to pass this 
        <TD><%=rs.getString(2)%></TD>                                                                                                             value for each row
 
        <TD><input type='radio' name="radio<%= i %>" value="radio1"><%=rs.getString(3)%></TD>                     //I want to pass                
        <TD><input type='radio' name="radio<%= i %>" value="radio2"><%=rs.getString(4)%></TD>                     //these values  
        <TD><input type='radio' name="radio<%= i %>" value="radio3"><%=rs.getString(5)%></TD>
        <TD><input type='radio' name="radio<%= i %>" value="radio4"><%=rs.getString(6)%></TD>
 
    </TR>  
 
      <%i++; %>                                                      
 
      <%   }   %>
 
 
      <% 
      rs.close();
 
      statement.close();
 
      connection.close();
 
      } catch (Exception ex) {
 
      %>
 
 
      <font size="+3" color="red"></font>
 
     <%   
 
     out.println("Unable to connect to database."); 
 
           }
       %>
 
 
 
      </TABLE>
 
 
 
   `	
 
 
  <input type="submit" name="submit" value="Submit" align="middle" >
  </form>

Here is my servlet code where i am reading actions of radio buttons

test.java

 
	for(int i=1;i<=4;i++)
		{
		//if(request.getParameter("radios") != null) {
            if(request.getParameter("radio"+i).equals("radio1")) {
                System.out.println("Radio button 1 was selected.");
 
//HERE I WANT TO GET rs.getsString(1) & rs.getString(3) value from index.jsp for the first row of table on the first run of for loop.For 2nd run 2 //nd row of database table should be accessed
 
 
            }
            else if(request.getParameter("radio"+i).equals("radio2"))
            {
            	 System.out.println("Radio button 2 was selected.");
 
//HERE I WANT TO GET rs.getsString(1) & rs.getString(4) value from index.jsp for the first row of table on the first run of for loop.For 2nd run 2 //nd row of database table should be accessed
 
 
            }
            else if(request.getParameter("radio"+i).equals("radio3"))
            {
            	 System.out.println("Radio button 3 was selected.");
 
//HERE I WANT TO GET rs.getsString(1) & rs.getString(5) value from index.jsp for the first row of table on the first run of for loop.For 2nd run 2 //nd row of database table should be accessed and so on for every run
 
 
            }
 
            else if(request.getParameter("radio"+i).equals("radio4"))
            {
            	 System.out.println("Radio button 4 was selected.");
 
//HERE I WANT TO GET rs.getsString(1) & rs.getString(6) value from index.jsp for the first row of table on the first run of for loop.For 2nd run 2 //nd row of database table should be accessed and so on for every run
 
 
 
 
 
 
            }
 
            else System.out.println("No button selected");
		}

Your original problem:

A character, token, or clause is invalid or missing

Probably refers to your INSERT statement. Your original code had a WHERE clause, which I'm fairly sure can't be used with INSERT like that. INSERT is a new row, while WHERE constrains your statement to act on existing rows.

I understand from your second post that you want to record the user responses to your option questions - is that right? In which case you'll need a (probably separate) table to record answers. If that table is administrator.status, then you need a SQL statement like:

INSERT INTO administrator.status(q_no, option1, option2, option3, option4) values(?, ?, ?, ?, ?)

That should get you something to look at in your table, but I suspect there's still a lot of work to do on your database design.