Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Insert query into JcomboBox and more...

Posted on 2010-11-12
7
Medium Priority
?
681 Views
Last Modified: 2013-11-23
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

0
Comment
Question by:g_currier
  • 5
  • 2
7 Comments
 
LVL 4

Expert Comment

by:greyfairer
ID: 34126887
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
 
LVL 4

Expert Comment

by:greyfairer
ID: 34126896
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
 

Author Comment

by:g_currier
ID: 34127029
Thanks, I'll give that a try.  (Sorry for the late response...had to sleep).
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:g_currier
ID: 34127243
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
 

Author Comment

by:g_currier
ID: 34127329
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
 

Accepted Solution

by:
g_currier earned 0 total points
ID: 34132139
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
 

Author Closing Comment

by:g_currier
ID: 34171263
Sorry, no points awarded to anyone due to lack of interest or follow-up.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

581 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