using JSP to update a field for multiple records

I have a table

mytable
----------
userid,
team     [The team could only have 3 possible values (ie 1,2,3 with default 1)]

I would want to display  [ user id - Team] combination in a table, with the team showing the existing value from database using "SELECT and OPTION" or "INPUT Radio button combination in 3 columns".

I would like to be able to make the changes and have an update button to update all the records with the new value.

How do I do this in  JSP?

Thank you.
Tuan_JeanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JNicCommented:
This requires different things, - you must set up a database-connection and retreive the information. Then you must construct the output on the jsp to show display data the way you want it.
It would take a looooooong time to answer this question in detail - could you perhaps write a bit more about how far you are on the subject?
Do you know basic jsp?
Do you know HTML?
Do you have a db-connection?
Do you know java?

Regards,

Nic
0
Tuan_JeanAuthor Commented:
Thank you for the quick response.

I have had db connection setup without problem and know some JSP. The problem I have was trying to use just a button for updating the table. If I have only 1 item, then using "INPUT Radio button combination in 3 columns" with the user_id as the name will work. But I am not sure how to proceed if there are more then 1 items to be updated at anyone time and how to setup the default value for each input using existing data from the database.

Thank you.
0
JNicCommented:
Hm, I am still a bit unsecure if I understand your question or not :-/  But anyway here comes an example:

(assuming you have a connection)

<form name="form" action="yourpage.jsp">
<%
try{
String query="Select userid from mytable";
ResultSet rs=statement.executeQuery(query);
while (rs.next()){
   String userid=rs.getString("userid");
   out.println("<br>UserID: "+rs.getString("userid"));
   String query2="Select team from mytable where userid='"+userid+"'";
   ResultSet rs2=statement.executeQuery(query2);
   rs2.next();
   
   int team=rs2.getInt("team");
   out.println("<SELECT>");
   
   for (int i=1; i<4; i++){
      out.println("<OPTION VALUE=\""+i+"\"");
      if (team==i){out.println(" SELECTED");}
      out.println(">"+i+"</OPTION>");
   }
   out.println("</SELECT>");
   
}
out.println("<br>");

}
catch(SQLException e){
   e.printStackTrace();
}

%>
<input type="submit" name="submit">
</form>

Now you can read (and alter db if neccesary) the choices from the select menus via request.getParameter().
Hope it helps you!

Regards,

Nic
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tuan_JeanAuthor Commented:
Nic,

You are very close to my question. Perhaps I could put down an example for the page I intended to do:

----------------
name1   1
name2   3
name3   2

[update]
----------------

When I click update, how do I update the name1 record to value 1 and etc. Thank you.
0
JNicCommented:
Ok.

In my last post I wrote:

<form name="form" action="yourpage.jsp">

If you just want to stay on that page forever, you just replace yourpage.jsp with the name of your jsp. Else create a new jsp (yourpage.jsp) with the database-update code, that I am about to post.

The code I posted before, made a lookup in the db, and produced the names and select-menus with the appropriate teams selected.

Now you need a code that can update the db, according to what selections were made on the page. It could be something like this:

<%
Enumeration enum = request.getParameterNames();
while (enum.hasMoreElements()){
   String parameter=(String)enum.nextElement();
   if (parameter.equals("submit")){parameter=enum.nextElement();} // just to eleminate the submit-button-parameter
   String val=request.getParameter(parameter);
   String update="Update mytable set team='"+val+"' where userid='"+parameter+"'";
   statement.executeUpdate(update);
}

I forgot something important in the code before:

 out.println("<SELECT>");  should have been: out.println("<SELECT NAME=\""+userid+"\">");

This way you have parameternames for all entrys in you userid-table.

1) Put this code on the page where you want to display contents of db:

<form name="form" action="yourpage.jsp">
<%
try{
String query="Select userid from mytable";
ResultSet rs=statement.executeQuery(query);
while (rs.next()){
   String userid=rs.getString("userid");
   out.println("<br>UserID: "+rs.getString("userid"));
   String query2="Select team from mytable where userid='"+userid+"'";
   ResultSet rs2=statement.executeQuery(query2);
   rs2.next();
   
   int team=rs2.getInt("team");
   out.println("<SELECT NAME=\""+userid+"\">");  
   for (int i=1; i<4; i++){
      out.println("<OPTION VALUE=\""+i+"\"");
      if (team==i){out.println(" SELECTED");}
      out.println(">"+i+"</OPTION>");
   }
   out.println("</SELECT>");
   
}
out.println("<br>");

}
catch(SQLException e){
   e.printStackTrace();
}

%>
<input type="submit" name="submit">
</form>

2) And this code on the page that should update the db - ie. the page that comes after the display-page:
    - If you want to use only that single page. You should of course put code part 2 first.

<%
Enumeration enum = request.getParameterNames();
while (enum.hasMoreElements()){
   String parameter=(String)enum.nextElement();
   if (parameter.equals("submit")){parameter=enum.nextElement();} // just to eleminate the submit-button-parameter
   String val=request.getParameter(parameter);
   String update="Update mytable set team='"+val+"' where userid='"+parameter+"'";
   statement.executeUpdate(update);
}
%>

Hope this gives you some idea.

Regards,

Nic
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tuan_JeanAuthor Commented:
Thank you Nic.
0
JNicCommented:
No problem. If your problem is solved, you should remember to award the points. ;-)
0
Tuan_JeanAuthor Commented:
Sorry Nic. I have forgotten to click on Accept button. I have just done it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.