excel, sql, and java NO DATA FOUND

[jite eghagha]

while (rs.next()) {
BPO = new BasisPlusObjects();
BPO.setProjectTitle(rs.getString("ProjectTitle"));
BPO.setProjectNumber(rs.getString("ProjectNumber"));
BPO.setGeoArea(rs.getString("Geo Area"));

task = rs.getString("Task1");
if( task != null) {
BPO.setTasks(task);

BPOObjectArray[i++] = BPO;

In the code above rs.getString("Task1"); will alway return null. Strange cause it should return a string. All the other getString() methods work fine.

Do you think it is a problem with sun.jdbc.odbc.JdbcOdbcDriver.

[Jeanne Boyarsky]

Jite,
Welcome to JavaRanch!

Can you show the SQL query used? Does it work if you call rs.getString(4) [or whatever the index is] ?

[jite eghagha]

Thanks i feel welcome already !

No, i tried using the index too, but i got the same "null".

Here's the SQl query

try{
Class.forName(driver);
connection = DriverManager.getConnection("jdbcdbc:usgs_proj_v4", "", "");
st = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = st.executeQuery( "Select * from [Sheet1$]" );
}
catch(Exception ex){ex.printStackTrace();}

[Wei Dai]

>In the code above rs.getString("Task1"); will alway return null.
>Do you think it is a problem with sun.jdbc.odbc.JdbcOdbcDriver
What's your sql? From your code, I can't see issue. You can download an evaluation HXTT Excel driver from http://www.hxtt.com/excel.html , and repeat your test. If it works normal, then you have to change column list in your sql or check whether your data in sheet triggers the known data type scan bug of ODBC Excel driver.

[Wei Dai]

>rs = st.executeQuery( "Select * from [Sheet1$]" );
For instance, try
rs = st.executeQuery( "Select ProjectTitle,ProjectNumber,[Geo Area],Task1 from [Sheet1$]" );

[jite eghagha]

Wei Dai,

i tried what you suggested
rs = st.executeQuery( "Select ProjectTitle,ProjectNumber,[Geo Area],Task1 from [Sheet1$]" );

but rs.getString("Task1"); still returns a null.

i'm downloading the HXTT Excel driver right now.

[jite eghagha]

I hope y'all be here tomorrow cause the help desk isn't installing my driver till tomorrow (if i'm lucky).

Funny, i think i'm the only developer that doesn't have administrative rights to his own machine. (i can't even change a path if i wanted too)

LOL.

[jite eghagha]

So, i found out that the problem is with microsoft excel and not my code.

To cut a long story short, excel gets very confused when its fields contain both strings and numbers.

There are two soultions to the problem, but neither is practical. (retyping the whole sheet ! for example)

There's more at microsoft.com (not hard to find)

[Jeanne Boyarsky]

Thanks for sharing that!

[Wei Dai]

>>or check whether your data in sheet triggers the known data type scan bug of ODBC Excel driver.
>To cut a long story short, excel gets very confused when its fields contain both strings and numbers.
Yeah. That's the key