Solved

Insert query into JcomboBox and more...

Posted on 2010-11-12
7
661 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
tomcat not starting 6 69
null output 3 35
Using jdbcTemplate.batchUpdate to improve INSERT performance 6 24
What is the use of Forwarding Class in java 1 18
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
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.

830 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