CDaoRecordset::GetFieldValue() fails while reading data from excel sheet

[ccbalamurali]

Hello,


I hope that somebody has found this bug already and is
able to help me.


I am using DAO (in Visual C++ 6.0) to get some data
from an Excel-sheet. (My operating system windows 2000 server )



Everything else goes OK, but when I have 8 or more rows
whose values are null in one column of an Excel-table,
all the following rows of that column are also seen as nulls
in my VC-application. (Values of the rows have to be nulls
right from the first line.) Otherwise this bug does not exist.


Here's an example:


I have following data in my Excel-sheet:


COLUMN1 COLUMN2
------- -------
1 AA null
2 BB null
3 CC null
4 DD null
5 EE null
6 FF null
7 GG null
8 HH null
9 II 1.0 <- these lines are seen as nulls in Vc
10 JJ 2.0 <- these lines are seen as nulls in Vc.
11 KK 3.0 <- these lines are seen as nulls in Vc.
12 LL 4.0 <- these lines are seen as nulls in Vc.
13 MM 5.0 <- these lines are seen as nulls in Vc.
14 NN 6.0 <- these lines are seen as nulls in Vc.


Now, if the value on line 8 of column2 is null, also
values in lines 9 to 14 are also seen as nulls in my VC
program. If the value on line 8 of column2 is not null,
all the values are seen correctly.


If the value on first line of column2 is not null, all
values are seen correctly. So the problem only occurs
when the first 8 lines are nulls. (So I need 8 nulls
on lines 1 to 8 to notice the bug.)


I have noticed that this bug occurs in any column if
the nulls are placed "right".


This bug occurs even if I first edit the Excel-file and
save changes, then close Excel, then start my application
and read the Excel-file.


I open my Excel-database the following way:

try{
CDaoDatabase db;
db.Open(sFile,0,0,"Excel 5.0;HDR=Yes;");
CDaoRecordset recset(&db);
CString strSelect="Select * From [";
strSelect += "demo_table";
strSelect += "]";

recset.Open(dbOpenSnapshot,strSelect);

int nbfilelds = recset.GetFieldCount();


while( !recset.IsEOF() )
{
for(int index = 0; index <nbfilelds;index++)
{
CDaoFieldInfo fi;
recset.GetFieldInfo(index,fi);
// Read the result line
COleVariant var1,var2;
var1 =recset.GetFieldValue(index);
var2 = recset.GetFieldValue("field_2"); //--//This returns NULL always in the above case
sItem1 = strVARIANT(var1);
sItem2 = strVARIANT(var2);

// Insert result into the list
m_ctrlList.AddString( sItem1 "+ " -->"+sItem2 );
}

// Skip to the next resultline
recset.MoveNext();
}

recset.Close();
db.Close();

} catch(CDaoException& e){
AfxMessageBox("exception");
}

Are there some mistakes in my program?
Is there any solution for this??

Thanks in adavnce
Girish

[chechuj]

Hello!,

First, when you crearte the database acces use this:

CDaoDtabase (..., ..., "EXCEL 5.0;HDR=Yes;IMEX=1");

(IMEX=1 forces excel jet engine to search in the registry what to do in case of mix types in the same colun of the sheet).

Second, provided that the registry values are set to (x.x means the version of excel you are using, 3.5 for Excel 97; 4.0 for Excel 2000 and after)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\x.x\Engines\Excel\TypeGuessRows=0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\x.x\Engines\Excel\ImportMixedTypes=Text

(The first one forces excel engine to take a look in every row of the worsheet to decide which is the type of the column. (set to 8 by default)
The second one commands excel jet engine to decide for text type in case of mixture of types)

In doping this, all the fields read in a mixed columnof should be read as TEXT fields, so your numbers will be converted to BSTR. The onlu think you need to do is deconvert (atof) those values.

Good luck!

[VictorN]

Dear chechuj!
Do you really think your reply is not too late (five years!!!) and OP still needs it? :confused:
Besides, the correct connection specifier for Excel 97 is "EXCEL 97;", not the "EXCEL 5.0;"