inserting into a database problems

[Bron Czimes]

well, I have been looking at this for hours (which could be one of my problems) and I can't seem to work out what is wrong.
i have a servlet that is supposed to be entering some data for me (this is a web-based application by the way). the program works fine, shows no errors, but when I go to check the database, nothing has happened! no data is entered into the table that I specified, and because I'm not getting any error messages whatsoever, I have no idea what the problem is.
The code is....

package com.mycompany;
 
import java.io.*;
import java.net.*;
import java.text.*;
import java.sql.*;
import java.util.*;
 
import javax.servlet.*;
import javax.servlet.http.*;
 
/**
 *
 * @author  bron
 * @version
 */
public class processPayment extends HttpServlet {
    
    /** Initializes the servlet.
     */
    public void init(ServletConfig config) throws ServletException {
        super.init(config);
        
    }
    
    /** Destroys the servlet.
     */
    public void destroy() {
        
    }
    
    /** Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        
        HttpSession session = request.getSession(true);
        
        // get total order amount
        String creditCardType = request.getParameter("card");
        String creditCard = request.getParameter("cardHolder");
        String creditCardNo = request.getParameter("creditcard");
        String creditCardExpires = request.getParameter("month");
        String creditCardExpires2 = request.getParameter("month2");
        String userName = session.getAttribute("username").toString();
        FlightBean d = ( FlightBean ) session.getAttribute( "flightToAdd" );
        String total = String.valueOf(d.getCost());
        java.util.Date isToday = new java.util.Date();
        DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT);
        String today = df.format(isToday).toString();
        
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String filename = "C:/OnAir.mdb";
            String database = "jdbc  :-o	  dbc  :mrgreen:  river={Microsoft Access Driver (*.mdb)};DBQ=";
            database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end
            // get the connection from the DriverManager
            Connection con = DriverManager.getConnection( database ,"","");
            int bookNum;
            String temp = "";
            
            //find the maximum booking number currently in the database
            PreparedStatement s;
            s = con.prepareStatement("SELECT max(BookingNbr) FROM tblBookings");
            s.executeUpdate();
 
            ResultSet maxResult = s.getResultSet();
 
            if(maxResult !=null){
                                
              while(maxResult.next()){
                    temp = maxResult.getObject(1).toString();
                }
            }
            s.close();
            
            //insert booking details into the database
            //add 1 to booking number to increment
            PreparedStatement c;
            bookNum = Integer.parseInt(temp);
            bookNum += 1;
            //s.execute("INSERT INTO tblBookings(BookingNbr,UserName,FlightNbr,Cost,BookingDate)VALUES(" + bookNum + "','" + userName + "','" + d.getFlightNbr() + "','" + d.getCost() + "','" + today);
            //c.execute("INSERT INTO tblBookings(BookingNbr,UserName,FlightNbr,Cost,BookingDate)VALUES(3,broccie,B8,129.00,2001)");
            c = con.prepareStatement("INSERT INTO tblBookings(BookingNbr,UserName,FlightNbr,Cost,BookingDate)VALUES(?, ?, ?, ?, ?)");
            c.setInt(1, bookNum);
            c.setString(2, userName);
            c.setString(3, d.getFlightNbr());
            c.setString(4, total);
            c.setString(5, today);
            c.executeUpdate();
            c.close();
        }catch(Exception e){
            e.printStackTrace();
        }
         
        //invalidate session because processing is complete
        session.invalidate();
        
        //make XHTML document
        out.println("<html xmlns = \"http://www.w3.org/1999/xhtml\"><head><title>Thank You!</title>");
        out.println("<link rel = \"stylesheet\" href = \"styles.css\" type = \"text/css\" /></head>");
 
        if(creditCardNo =="" || creditCardExpires == "" || creditCardExpires2 == ""){
        out.println("<body bgcolor=cornflowerblue><p><center><h2>Please Fill In all of your Details.</h2></center></p>");
        out.println("<p><center><a href =\"order.jsp\">Return to order form</a><p></body></html>");
        }else{
        out.println("<body bgcolor=cornflowerblue><p><center><h2>Thank You</h2></center></p><p><center><label class=\"label\">");
        out.println("Your order has been processed.</label></center></p>");
out.println("<p><center><label class=\"label\">Your credit card has been billed: $");
 
        DecimalFormat dFormat = new DecimalFormat();
 
        out.println(dFormat.format(d.getCost()) + " </label></center></p><br>");
      out.println("<p><center><a href =\"main.jsp\">Return To Main Page</a></center></p>");
      out.println("</body></html>");
        }
           
        out.close();
        }
        
        /** Handles the HTTP <code>GET</code> method.
         * @param request servlet request
         * @param response servlet response
         */
        protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
            processRequest(request, response);
        }
        
        /** Handles the HTTP <code>POST</code> method.
         * @param request servlet request
         * @param response servlet response
         */
        protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
            processRequest(request, response);
        }
        
        /** Returns a short description of the servlet.
         */
        public String getServletInfo() {
            return "Short description";
        }
        
    }

Can somebody please tell me what is wrong?

[Bear edit: removed potentially offensive slur]
[ October 07, 2004: Message edited by: Bear Bibeault ]

[Bear Bibeault]

What is the executeUpdate returning? You don't even check.

[Bron Czimes]

Thats because I dont need to use the result anywhere. I just need some information generated by my program to be inserted into the database at this point.
I was under the impression that executeUpdate() cannot or should not return a result (throws a SQL exception instead).

[Bear Bibeault]

It returns the number of records that were updated/inserted/deleted.

[Bron Czimes]

ok, I tried to do a getResultSet() to see if any thing is returned and it turns up blank :S
so I'm assuming this means that nothing is being updated?? is that the correct assumption to make?


so what do I do now?

[Adeel Ansari]

i dont know if you can get the result set by getResultSet(), though you are executing executeUpdate().

- why are you doing executeUpdate() with select statement?
- while updating the records, what are you expecting in a resultset mate?

[Sonny Gill]

Try to change your code as follows

 
PreparedStatement s;
temp = 0;
            s = con.prepareStatement("SELECT max(BookingNbr) FROM tblBookings");
 
            ResultSet maxResult = s.executeQuery();
 
              if(maxResult.next()){
                    temp = maxResult.getObject(1).toString();
                }
            s.close();
 

Give an initial value to temp, just to get the program working.
Later you can check for invalid values or other conditions etc.

Use executeQuery() instead of executeUpdate, because you want a ResultSet containing the records returned by the ResultSet.

Hopefully this takes you a step closer to what you are trying to do.

Sonny

[Bron Czimes]

Thanks Sonny, that helped.
Thanks all.