MS Access Database problem - No data found [Resolved]

[MethadoneBoy]

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.


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>

[System_Error]

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

[MethadoneBoy]

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

[nebulom]

Should be something like this.

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: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;
}
}

[MethadoneBoy]

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! :)