Solved

Insert query into JcomboBox and more...

Posted on 2010-11-12
7
654 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, I'll give that a try.  (Sorry for the late response...had to sleep).
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This video teaches viewers about errors in exception handling.

771 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

11 Experts available now in Live!

Get 1:1 Help Now