How to use combo box for record insert with JDBC in JAVA Servlet
Hello,
I would like to know how I go about using a combo box, populated with query results to insert a record in an Oracle database. I would like the user to be able to choose from existing records so that there are no constraint violations. Short of displaying all info for each table on each form page, I lack the knowledge to use combo boxes for this type of work.
Can anyone help?
Here is one of the servlet pages for inserting a sale as well as a view of the table itself. (screenshot and code)
The attribute for the SoldTo is an FK to a "Customer" table and the SoldBy is an FK to an "Agent" table. I ahve removed the connection info for privacy (the connection is not the problem). I have no errors running it, I just want to do it better and lack the experience/know-how.
I appreciate the assistance...thanks
I would like to know how I go about using a combo box, populated with query results to insert a record in an Oracle database. I would like the user to be able to choose from existing records so that there are no constraint violations. Short of displaying all info for each table on each form page, I lack the knowledge to use combo boxes for this type of work.
Can anyone help?
Here is one of the servlet pages for inserting a sale as well as a view of the table itself. (screenshot and code)
The attribute for the SoldTo is an FK to a "Customer" table and the SoldBy is an FK to an "Agent" table. I ahve removed the connection info for privacy (the connection is not the problem). I have no errors running it, I just want to do it better and lack the experience/know-how.
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.lang.String.*;
public class SaleInsert extends HttpServlet {
public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
public static final String URL = "jdbc:odbc:rreOracle";
public static final String username = "<removed>";
public static final String password = "<removed>";
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.print("<html><head>");
out.print("<title>Add Sale</title>");
out.print("<link href=\"style.css\" rel=\"stylesheet\" type=\"text/css\" />");
out.print("</head>");
out.print("<body>");
out.print("<center><h2>Add a Sale</h2></center>");
out.print("<div><form action=\"");
out.print(req.getRequestURI());
out.print("\" method=\"post\">");
out.print("<center><table class=\"ins_form_tbl\">");
out.print("<tr><td>SoldWhich Home ID: </td><td>");
out.print("<input type=\"text\" name=\"sh\"></td></tr>");
out.print("<tr><td>SoldTo Customer ID: </td><td>");
out.print("<input type=\"text\" name=\"sc\"></td></tr>");
out.print("<tr><td>SoldBy Agent ID: </td><td>");
out.print("<input type=\"text\" name=\"sa\"></td></tr>");
out.print("<tr><td>Sold Price: </td><td>");
out.print("<input type=\"text\" name=\"sold\"></td></tr>");
out.print("<tr><td>Date of Sale: </td><td>");
out.print("<input type=\"text\" name=\"dos\"></td></tr>");
out.print("<tr><td align=\"center\">" +
"<p><br /><input type=\"submit\" value=\"Insert Record\" class=\"btn\"></p></td></tr>");
out.print("<tr><td align=\"center\">" +
"<p><br /><input type=\"reset\" value=\"Clear the Form\" class=\"btn\"></p></td></tr>");
out.print("</table>");
out.print("<p><a href=\"gcurrierFA.html\" class=\"links\">Return to Main Page</a></p>");
out.print("<p><a href=\"SaleDisplay\" class=\"links\">Display Sale Records</a></p>");
out.print("</center></form></div>");
out.print("</body></html>");
out.close();
}
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
//upon successful record insert
out.print("<html><body>");
out.print("<link href=\"style.css\" rel=\"stylesheet\" type=\"text/css\" />");
out.print("<head><center><h2>Record Added Successfully!</h2></center></head>");
out.print("<div><center><br /><table cellpadding=\"5\">");
out.print("<tr><td><a href=\"SaleInsert\" class=\"links\">Add Another Sale Record</a>");
out.print(" <a href=\"SaleUpdate\" class=\"links\">Update Sale Records</a>");
out.print(" <a href=\"SaleDelete\" class=\"links\">Delete Sale Record</a></td></tr>");
out.print("<tr align=\"center\"><td><a href=\"SaleDisplay\" class=\"links\">Display Sale Records</a>");
out.print(" <a href=\"gcurrierFA.html\" class=\"links\">Back to Main Page</a></td></tr>");
// receiving parameters
String sh = req.getParameter("sh").trim();
String sc = req.getParameter("sc").trim();
String sa = req.getParameter("sa").trim();
String sold = req.getParameter("sold").trim();
String dos = req.getParameter("dos").trim();
boolean proceed = false;
if(sc != null && sa != null){
if(sc.length() > 0 && sa.length() > 0){
proceed = true;
}
}
// connecting to database
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
Class.forName(DRIVER);
con = DriverManager.getConnection(URL, username, password);
String sql;
sql = "INSERT INTO sale (sale_home_id,sale_cust_id,sale_agent_id,actual_amount,sale_date) VALUES (?,?,?,?,?)";
ps = con.prepareStatement(sql);
stmt = con.createStatement();
// inserting records
if (!proceed) {
out.print("No records inserted. Go back to try again.");
}else if (proceed){
ps.setString(1, sh);
ps.setString(2, sc);
ps.setString(3, sa);
ps.setString(4, sold);
ps.setString(5, dos);
ps.executeUpdate();
}
} catch (SQLException e) {
throw new ServletException(e);
} catch (ClassNotFoundException e) {
throw new ServletException(e);
}finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (ps != null) {
ps.close();
}
if (con != null) {
con.commit();
con.close();
}
} catch (SQLException e) {
}
}
out.print("</table></center></div>");
out.print("</body></html>");
out.close();
}
}
I appreciate the assistance...thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Or Maybe something like this
but then, what does HomeInfo look like?
but then, what does HomeInfo look like?
public ResultSet HomeData() throws SQLException{
JComboBox HomeIns = new JComboBox();
try{
String sql = "SELECT home_id,address_street,address_city,purchase_price FROM Home ORDER BY home_id";
ResultSet rs1 = null;
Statement stmt1 = null;
rs1 = stmt1.executeQuery(sql);
Integer home_id;
String address_street = "";
String address_city = "";
String purchase_price = "";
while (rs1.next()){
home_id = rs1.getInt("home_id");
address_street = rs1.getString("address_street");
address_city = rs1.getString("address_city");
purchase_price = rs1.getString("purchase_price");
//Create homedata object
HomeInfo HomeObj = new HomeInfo(home_id,address_street,address_city,purchase_price);
HomeIns.addItem(HomeObj);
}
rs1.close();
}
catch(SQLException e){}
return rs1;
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please correct the line:
out.println("<option value="+item+">"+item+"</o
out.println("<option value="+item+">"+item+"</o
And if you want to use SERVLET only in place of JSP, then you may implement the same logic of JSP into your SERVLET
ASKER
OK,
I think I have this mostly right. But in the for each statement, the soldWhich var is not declared, yet it is delcared in the try block. I'm trying, but there is something I'm missing. I just don't know what...
I think I have this mostly right. But in the for each statement, the soldWhich var is not declared, yet it is delcared in the try block. I'm trying, but there is something I'm missing. I just don't know what...
public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.print("<html><head>");
out.print("<title>Add Sale</title>");
out.print("<link href=\"style.css\" rel=\"stylesheet\" type=\"text/css\" />");
out.print("</head>");
out.print("<body>");
out.print("<center><h2>Add a Sale</h2></center>");
out.print("<div><form action=\"");
out.print(req.getRequestURI());
out.print("\" method=\"post\">");
out.print("<center><table class=\"ins_form_tbl\">");
// out.print("<input type=\"text\" name=\"sh\"></td></tr>");
out.print("<tr><td>SoldWhich Home ID: </td><td>");
JComboBox HomeIns = new JComboBox();
Object HomeObj = new Object();
try{
String sh = null;
String[] soldWhich =(String[])req.getAttribute(sh);
String sql = "SELECT home_id FROM Home ORDER BY home_id";
ResultSet rs1 = null;
Statement stmt1 = null;
rs1 = stmt1.executeQuery(sql);
while (rs1.next()){
req.setAttribute(sh,soldWhich);
}
rs1.close();
}
catch(Exception e){}
out.print("<select name=soldWhichBox>");
for(String sh: soldWhich){
out.print("<option value="+ sh +">"+ sh +"</option>");
}
out.print("</select>");
out.print("</td></tr>");
out.print("<tr><td>SoldTo Customer ID: </td><td>");
out.print("<input type=\"text\" name=\"sc\"></td></tr>");
out.print("<tr><td>SoldBy Agent ID: </td><td>");
out.print("<input type=\"text\" name=\"sa\"></td></tr>");
out.print("<tr><td>Sold Price: </td><td>");
out.print("<input type=\"text\" name=\"sold\"></td></tr>");
out.print("<tr><td>Date of Sale: </td><td>");
out.print("<input type=\"text\" name=\"dos\"></td></tr>");
out.print("<tr><td align=\"center\">" +
"<p><br /><input type=\"submit\" value=\"Insert Record\" class=\"btn\"></p></td></tr>");
out.print("<tr><td align=\"center\">" +
"<p><br /><input type=\"reset\" value=\"Clear the Form\" class=\"btn\"></p></td></tr>");
out.print("</table>");
out.print("<p><a href=\"gcurrierFA.html\" class=\"links\">Return to Main Page</a></p>");
out.print("<p><a href=\"SaleDisplay\" class=\"links\">Display Sale Records</a></p>");
out.print("</center></form></div>");
out.print("</body></html>");
out.close();
}
ASKER
Or maybe something like this?
out.print("<select name=soldWhichBox>");
for(String sh : soldWhich){
if (!sh.isEmpty()){
out.print("<option value="+ sh +">"+ sh +"</option>");
}
}
out.print("</select>");
ASKER
Needed follow up help...moved to different question
ASKER
[code]
public ResultSet getHomeData() throws SQLException{
ResultSet rs1 = null;
Statement stmt1 = null;
JComboBox HomeIns = new JComboBox();
try{
rs1 = stmt1.executeQuery("SELECT
while (rs1.next()){
HomeIns.addItem(HomeObj(rs
}
rs1.close();
}
catch(SQLException e){}
return rs1;
}
[/code]
But how does that integrate into the HTML written in the rest of the servlet? I'm sorry, I'm not very good at this yet (I can't even figure out how to get the HomeObj Object instantiated.
Thanks