MS Access Database problem - No data found [Resolved]

Hi guys,

Bit of a puzzler here. I've written an app that connects to a MSAccess DB fine. The DB has 3 colums - Employeename (Text or String), PassId (Number/long) and Passdate (Date/Time). All 3 columns have data entered in them. I can retrieve info from the first column but whenever I try to get data from the other two, tthe SQL Exception thrown tells me No Data Found.

Would really appreciate any help you could give me. Thanks in advance.

Code:

---

import java.sql.*;
import java.util.Date;
import java.util.Calendar;

public class Interactor {
       
       

private String URL = "";
        private String username = "";
        private String password = "";
        private int employees = 0;
        private Statement stmt = null;
    private Connection con = null;
        private String dataSourceName = "NewTest";
       
       
        public Interactor ( String u, String p ) {
               
                dataSourceName = "NewTest";
                URL = "jdbc:odbc:" + dataSourceName;
                username = u;
        password = p;
               
                setupConnection ();

        }
       
        private void P ( String S ) { System.out.print ( S ); }

        private void PL ( String S ) { System.out.println ( S ); }
       
    public void setupConnection () {
               
                try { Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver" ); }
               
                catch ( Exception e ) {
           
                        System.out.println ( "Failed to load JDBC/ODBC driver." );
            return;
       
                }
               
                try {
           
                        con = DriverManager.getConnection ( URL, "", "" );
            stmt = con.createStatement ();
       
                }
               
                catch ( Exception e ) {
                       
                        System.err.println ( "problems connecting to " + URL );
                        System.err.println ( e.getMessage () );
                        e.printStackTrace ();
                       
                }

    }
       

       
        public void getPassDates () {
       
                               
                try {
               
                        ResultSet result = stmt.executeQuery ( "SELECT * FROM Emp;" );
            result.next();
        PL ( result.getString ( "Employeename" ) );
            PL ( "" + result.getLong ( "PassId" ) );
            PL ( result.getDate ( "Passdate" ).toString () );
           
                       
                        while ( result.next () ) {
                       
                                count ++;
                                PL ( result.getDate ( "Passdate" ).toString () );
                                PL ( "" + result.getLong ( "PassId" ) );
                               
                        }
                       
                }
               
                catch ( Exception e ) {
               
                        System.err.println ( e.getMessage () );
                        e.printStackTrace ();
                       
                }
               
               
        }
       
        public int getNoEmployees () {
       
                try {
               
                        String q = "SELECT Employeename FROM Emp;";
                        ResultSet result = stmt.executeQuery ( q );
            result.next();  // move to first row
            PL ( result.getString ( "Employeename" ) );
            //PL ( result.getDate ( 1 ).toString () );
            employees = 1;
           
                        while ( result.next () ) {
                               
                                employees ++;
                                PL ( result.getString( "Employeename" ) );
                                //PL ( result.getDate ( "Passdate" ).toString () );
                       
                        }
                       
                        PL ( "Number of employees : " + employees );
                       
                }
               
                catch ( Exception e ) {
               
                        System.err.println ( e.getMessage () );
                        e.printStackTrace ();
                       
                }
               
                return employees;
               
        }

}

---

<Moderator added green checkmark to the thread>

When you execute the query, what is "Emp"? Shouldn't that be the name of the database, like "New Test" ?

Emp is the name of the only table in the database. The database is named NewTest.

Should be something like this.

VB Code:

import java.sql.*;
 
public class test{
    public static void main(String[] args){
        Connection cn=null;
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            cn=DriverManager.getConnection("jdbc:[b][/b]odbc:test","","");
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
        }
 
        employee e=new employee(cn);
        System.out.println(e.count());
        e.displayEmployees();
    }
}
 
class employee{
    Connection cn;
    public employee(Connection cn){
        this.cn=cn;
    }
 
    public void displayEmployees(){
        try{
            Statement s=cn.createStatement();
            ResultSet rs=s.executeQuery("select * from emp");
            while(rs.next()){
                System.out.println(rs.getString(1));
                System.out.println(rs.getString(2));
                System.out.println(rs.getDate(3)+"\n");
            }
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
        }
    }
 
    public int count(){
        int n=0;
        try{
            Statement s=cn.createStatement();
            ResultSet rs=s.executeQuery("select count(*) from emp");
            rs.next();
            n=Integer.parseInt(rs.getString(1).toString());
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return n;
    }
}

Just solved the problem a couple of days ago but it was pretty similar to your suggestion - just changed the code to result.getString (1) etc as opposed to calling dat aby column name.

Thanks for the help though! :)