Insert query into JcomboBox and more...

Hello,
I am trying to pass a query into a JComboBox, assign what is selected to a variable and use that variable in an insert statement.  I have most of it correct however I am getting a null pointer exception at the for-each statement on line 54.

Without the use of a JComboBox the insertion of data to my Oracle DB works fine. However, the Form needs to be more restrictive in that the user should not be able to insert data that doesn't relate to another table (hence, the JComboBox from a query). I'm most of the way there I'm sure, but I am not sure how to fix the problem with the null pointer. This means I'm not as close as I thought.

What am I doing wrong/How do I fix it?
and
Am I setting the html up correctly so that the insert form uses the comboboxes as opposed to an editable textfield?

How do I pass the selection from the box to a variable?

Thanks
 
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.lang.String.*;
import javax.swing.*;

public class SaleInsert_1 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 ResultSet rs_sh = null;
    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();
        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("soldWhich",soldWhich);
            }
            rs1.close();
        }
        catch(Exception e){}

        out.print("<select name=soldWhichBox>");
        String soldWhich[] =(String[])req.getAttribute("home_id");
        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();
//        rs_sh = gotHomeID();
//        updateSoldWhich(rs_sh);
    }
//    public ResultSet gotHomeID(){
//        String sql_sh = "SELECT home_id FROM Home ORDER BY home_id";
//        Statement stmt_sh = null;
//
//        try{
//            rs_sh = stmt_sh.executeQuery(sql_sh);
//        }
//        catch(SQLException e){
//            System.out.println(e);
//        }
//        return rs_sh;
//        
//    }
//    public void updateSoldWhich(ResultSet rs1){
//        try{
//            while (rs_sh.next()){
//                JComboBox HomeIns = new JComboBox();
//                HomeIns.addItem(gotHomeID());
//            }
//        }
//        catch(Exception e){
//            System.out.println(e);
//        }
//    }

    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();
    }
}

Open in new window

g_currierAsked:
Who is Participating?
 
g_currierConnect With a Mentor Author Commented:
Here is a solution.  It isn't the best but it works as I need it to.
ResultSet rs1 = null;
        Statement stmt1 = null;
        Connection con1 = null;
        List<String> soldWhich = new ArrayList<String>();
        try {
            Class.forName(DRIVER);
            con1 = DriverManager.getConnection(URL, username, password);
            String sql = "SELECT home_id FROM Home";
            stmt1 = con1.createStatement();
            rs1 = stmt1.executeQuery(sql);
            //populate the ArrayList with the results
            while (rs1.next()){
                soldWhich.add(rs1.getString("home_id"));
            }
        }
        catch(Exception e){}
        //Begin option box
        out.print("<select width=\"150px\" align=\"right\" name=\"soldWhichBox\">");
        //populate options from ArrayList to string var using for-each loop
        try{
            for(String sh : soldWhich){
                out.print("<option width=\"150px\" align=\"right\" value=\""+sh+"\">"+sh+"</option>");
            }
            rs1.close();
            con1.close();
        }
        catch (Exception e){}
        out.print("</select>");
        out.print("</td></tr>");

Open in new window

0
 
greyfairerCommented:
You're doing weird stuff with your variables. You shouldn't use request attributes or Swing classes (e.g. JCombobox) to hold local variables inside your function. A simple locally declared ArrayList should do the trick. And you should probably read something out of your query.

 
      JComboBox HomeIns = new JComboBox();
        try{
            String sh = null;
            List<String> soldWhich = new ArrayList<String>();                 
            String sql = "SELECT home_id FROM Home ORDER BY home_id";
            ResultSet rs1 = null;
            Statement stmt1 = null;
            rs1 = stmt1.executeQuery(sql);

            while (rs1.next()){
                soldWhich.append(rs1.getString(1));  // Get data
            }
            rs1.close();
        }
        catch(Exception e){}

        out.print("<select name=soldWhichBox>");
        String soldWhich[] =(String[])req.getAttribute("home_id");
        for(String sh : soldWhich){
            out.print("<option value="+ sh +">"+ sh +"</option>");
        }

Open in new window

     
0
 
greyfairerCommented:
And in the doPost method, you should use the name of your select tag:

String sold = req.getParameter("soldWhichBox").trim();

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
g_currierAuthor Commented:
Thanks, I'll give that a try.  (Sorry for the late response...had to sleep).
0
 
g_currierAuthor Commented:
So I changed the code (and added the connection text) and I'm still getting a Null Pointer Exception at the for-each loop.  It's one of two things I think: soldWhich is not getting "fed" or sh is not receiving the info from soldWhich.  The stack trace in the tomcat logs doesn't explain much else beyond the null pointer exception (specific to my code).  What's missing?
out.print("<tr><td>SoldWhich Home ID:  </td><td>");
        try{
            Connection con1 = null;
            ResultSet rs1 = null;
            Statement stmt1 = null;
            
            Class.forName(DRIVER);
            con1 = DriverManager.getConnection(URL, username, password);
            String sql = "SELECT home_id FROM Home ORDER BY home_id";
            stmt1 = con1.createStatement();
            List<String> soldWhich = new ArrayList<String>();
            rs1 = stmt1.executeQuery(sql);
            while (rs1.next()){
                soldWhich.add(rs1.getString(1));
            }
            rs1.close();
            con1.close();
        }
        catch(Exception e){}

        out.print("<select name=soldWhichBox>");
        String soldWhich[] =(String[])req.getAttribute("home_id");
        for(String sh : soldWhich){
            out.print("<option value="+ sh +">"+ sh +"</option>");
        }
        out.print("</select>");
        out.print("</td></tr>");

Open in new window

0
 
g_currierAuthor Commented:
Changed the code again to incldue try and catch.  The page displays now, but with no values in the box.  (basically the same as before)
try{
            Connection con1 = null;
            ResultSet rs1 = null;
            Statement stmt1 = null;
            
            Class.forName(DRIVER);
            con1 = DriverManager.getConnection(URL, username, password);
            String sql = "SELECT home_id FROM Home ORDER BY home_id";
            stmt1 = con1.createStatement();
            rs1 = stmt1.executeQuery(sql);
            List<String> soldWhich = new ArrayList<String>();

            while (rs1.next()){
                soldWhich.add(rs1.getString(1));
            }
            
            rs1.close();
            con1.close();
        }
        catch(Exception e){}

        out.print("<select name=soldWhichBox>");
        String soldWhich[] =(String[])req.getAttribute("home_id");
        try{
        for(String sh : soldWhich){
//        for(int i=0;i<soldWhich.length;i++){
            out.print("<option value="+ soldWhich +">"+ soldWhich +"</option>");
        }
        out.print("</select>");
        out.print("</td></tr>");
        }
        catch (Exception e){}

Open in new window

0
 
g_currierAuthor Commented:
Sorry, no points awarded to anyone due to lack of interest or follow-up.
0
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.

All Courses

From novice to tech pro — start learning today.