Solved

using JSP to update a field for multiple records

Posted on 2003-11-11
8
1,591 Views
Last Modified: 2008-03-10
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.
0
Comment
Question by:Tuan_Jean
  • 4
  • 4
8 Comments
 
LVL 1

Expert Comment

by:JNic
ID: 9730506
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
 

Author Comment

by:Tuan_Jean
ID: 9735115
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
 
LVL 1

Expert Comment

by:JNic
ID: 9739325
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
 

Author Comment

by:Tuan_Jean
ID: 9743320
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Accepted Solution

by:
JNic earned 40 total points
ID: 9746672
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
 

Author Comment

by:Tuan_Jean
ID: 9760151
Thank you Nic.
0
 
LVL 1

Expert Comment

by:JNic
ID: 9763305
No problem. If your problem is solved, you should remember to award the points. ;-)
0
 

Author Comment

by:Tuan_Jean
ID: 9767342
Sorry Nic. I have forgotten to click on Accept button. I have just done it.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
A Short Story about the Best File Recovery Software – Acronis True Image 2017
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now