Need help with a DA class.

Ok, I am trying to get this to work. I have cut out everything in my servlet except for the searching for the appointment.

My problem is that everytime I access my DA class, it sends back a null appointment. In my servlet, i have code that if it returns a null appointment, it forwards to the Error.jsp. I think my problem lies with my SQL code in the ApptDA.java class. I checked my log.txt I am creating with my LogUtil.java class and it shows an error of

Quote:

---

[Microsoft][ODBC Driver Manager] Invalid cursor state

---

Here is my ApptDA.java class method that I am using to try to find the certain appointment. I have been putting serveral statements using my LogUtil.logToFile() methods to write a file to help track down my problem. I have included it in my zip file and I will include it below the code for my searchApptsDB() method.

Code:

---

        public Appointment searchApptsDB(int searchID)throws SQLException
        {
                LogUtil.logToFile("Now in searchApptsDB, before Trycatch statement.");
            //make the connection
            try
            {
                        //find the info
                        //sql = "Select * from appts where PatID="+"'"+searchID+"'";
                        LogUtil.logToFile("Now in try statement");
                        sql = "Select * from appts";
                        appt=null;
                       
                        LogUtil.logToFile("SQL Statement.=");
                        LogUtil.logToFile(sql);
                       
                        //make a connection, need the driver string and the dsn for access
                        LogUtil.logToFile("Value of Connection con.=");
                        con = ConnectionManager.getInstance().getConnection("schedule");
                        LogUtil.logToFile(con);
                       
                        //create a Statement in order to execute an sql command
                    LogUtil.logToFile("Value of Statement stmt =");
                    stmt = con.createStatement();
                    LogUtil.logToFile(stmt);
                   
                        //get the list of appointments
                      LogUtil.logToFile("Value of ResultSet rsPats =");
                    rsPats = stmt.executeQuery(sql);
                    LogUtil.logToFile(rsPats);

                    LogUtil.logToFile("Value of ResultSetMetaData rsmPats =");
                    rsmPats = rsPats.getMetaData();
                    LogUtil.logToFile(rsmPats);

                        //gets the  for each row
                        LogUtil.logToFile("pid in ApptDA.=");
                        pid = rsPats.getInt(1);
                        LogUtil.logToFile(pid);

                        LogUtil.logToFile("patName in ApptDA.=");
                        patName = rsPats.getString(2);
                        LogUtil.logToFile(patName);

                        LogUtil.logToFile("paReason in ApptDA.=");
                        patReason = rsPats.getString(3);
                        LogUtil.logToFile(patReason);

                        LogUtil.logToFile("month in ApptDA.=");
                        month = rsPats.getInt(4);
                        LogUtil.logToFile(month);

                        LogUtil.logToFile("day in ApptDA.=");
                        day = rsPats.getInt(5);
                        LogUtil.logToFile(day);

                        LogUtil.logToFile("year in ApptDA.=");
                        year = rsPats.getInt(6);
                        LogUtil.logToFile(year);
                       
                        LogUtil.logToFile("hour in ApptDA.=");
                        hour = rsPats.getInt(7);
                        LogUtil.logToFile(hour);
                       
                        LogUtil.logToFile("minute in ApptDA.=");
                        minute = rsPats.getInt(8);
                        LogUtil.logToFile(minute);
                       
                          //create a appointment object here
                        appt = new Appointment(patName, pid, patReason, year,month,day,hour,minute);
                        LogUtil.logToFile("Value of appt in try statement.=");
                        LogUtil.logToFile(appt);
                       
                        //return appt;       
                        LogUtil.logToFile("Now Leaving try statement.");                   
                }//end try
               
                catch (ClassNotFoundException nf){
                        LogUtil.logToFile(nf.getMessage());
                System.out.println(nf.getMessage());
                nf.printStackTrace();
            }
           
                catch(SQLException s){
                        LogUtil.logToFile(s.getMessage());
                        System.out.println(s.getMessage());
                        s.printStackTrace();
                }
               
                catch(Exception e){
                        LogUtil.logToFile(e.getMessage());
                        System.out.println(e.getMessage());
                        e.printStackTrace();
                }
        LogUtil.logToFile("Sending appt back.");
        LogUtil.logToFile("Now leaving searchApptsDB, after Trycatch statement.");

        return appt;                           

        }//end Appointment search()

---

Now for the contents of my log file. My logUtil, prints out the value of each of the variables in the searchApptsDB() method. I noticed that it doesnt print out the values of the variables from the database.

Quote:

---

This is the value of searchID before accessing the database.
1
SearchAppt before DA
business.Appointment@b03be0
Now in searchApptsDB, before Trycatch statement.
Now in try statement
SQL Statement.=
Select * from appts
Value of Connection con.=
sun.jdbc.odbc.JdbcOdbcConnection@113a53d
Value of Statement stmt =
sun.jdbc.odbc.JdbcOdbcStatement@c5495e
Value of ResultSet rsPats =
sun.jdbc.odbc.JdbcOdbcResultSet@53fb57
Value of ResultSetMetaData rsmPats =
sun.jdbc.odbc.JdbcOdbcResultSetMetaData@19a32e0
pid in ApptDA.=
[Microsoft][ODBC Driver Manager] Invalid cursor state
Sending appt back.
Now leaving searchApptsDB, after Trycatch statement.
The try statement, searchAppt after returning from DA.
null
search appt null if statement
null

---

For those of you that would rather run the code yourself, I have included an updated version of my code in this post.

Ok, i think I know where that Invalid Cursor state error is coming from, but I don tknow why I am getting it.

According to the log.txt file, its gets to the LogUtil.logToFile("pid in ApptDA.="); in my method and it just skips getting hte information from the database, therefor thats why I keep getting redirected to my Error.jsp assignment. But my question is though, why is this happening

Code:

---

pid = rsPats.getInt(1);
patName = rsPats.getString(2);
patReason = rsPats.getString(3);
month = rsPats.getInt(4);
day = rsPats.getInt(5);
year = rsPats.getInt(6);
hour = rsPats.getInt(7);
minute = rsPats.getInt(8);

---

Ok, I figured it out.

I got the error pinned down to my ResultSet object when it trying to get the information from the database.

I was looking at

http://java.sun.com/j2se/1.4.2/docs/...Set.html#next()

and I read

Quote:

---

Moves the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

---

I then figured out that I was always before the first row of data in my database. I added rsPats.next() before I started to get information out of the ResultSet and it worked.

Its really nice to figure that out.

Below is a finished version of my code.

glad you could help you ;)