How to handle null numeric values in ADO recordset

[FlaviusFlav]

I am currently working on some code that populates a spreadsheet wit
data obtained from a servlet that returns an ADO object. The code work
ok except for the case when the object contains 'empty' numeri
values.

The object returned is something like :

<?xml version="1.0" encoding="UTF-8"?>
...
<s:datatype rs:maybenull="true" *dt:type="float"
maxLength="20" />
</s:AttributeType>

<s:extends type="rs:rowbase" />
</s:ElementType>
</s:Schema>
<rs:data>
<z:row INDEX="2004-04-05" x="1" />
<z:row INDEX="2004-04-06" x="2" />
<z:row INDEX="2004-04-07" x="3" />
<z:row INDEX="2004-04-08" x="4" />
*<z:row INDEX="2004-04-09" x="" />*
<z:row INDEX="2004-04-12" x="6" />
<z:row INDEX="2004-04-13" x="7" />
<z:row INDEX="2004-04-14" x.="8" />
<z:row INDEX="2004-04-15" x.="9" />
<z:row INDEX="2004-04-16" x="10" />
</rs:data>
</xml>

The error I get when it fails is:
Run-time error '-2147467259 (80004005)':
Data provider or other service returned an E_FAIL status.

and comes from this line:
Application.ActiveSheet.Cells(Application.ActiveCell.Row + 1
Application.ActiveCell.Column).CopyFromRecordset rs

I've been battling with this for some time now, and I'm not sure how t
get around this. It also works ok if I tweak it so that the data typ
of the column returned is string. Then the empty-string null i
handled ok.

I assume there must be a way to handle this with VBA, but for the lif
of me, I haven't been able to figure out.

If anybody has any idea here, I would GREATLY appreiate the help. TI

 

[arno]

Hi Flavius,

I do not really know what you are doing here, however, maybe it could help
if you check the contentents of your field if it is null and replace it with
zero. In an sql-statement I would use something like:
Selecect IIF(myfield is null, 0, myfield) as myNumber from mytable
before you CopyFromRecordset, eg. when initially creating the data (<===try
this first!). If this is no option (but you must try!) you could loop
through your recordset and replace values, however, I cannot tell you how to
do this.

Maybe you should look at KB 246335, esp. the trouble Excel97 can cause with
date fields (also if you are not using xl97). There you see how you
transform your recordset to an array and reformat (date-) fields. I do not
think that this is an option for you.

regards


arno

 

[onedaywhen]

I am currently working on some code that populates a spreadsheet with
data obtained from a servlet that returns an ADO object. The code works
ok except for the case when the object contains 'empty' numeric
values.
works ok if I tweak it so that the data type
of the column returned is string. Then the empty-string null is
handled ok.

Or replace the NULLs with zeros before it goes to Excel. If desired,
you can format the column to show zeros as null strings e.g.

Range("A:A").NumberFormat = "General;General;;@"

--

 

[FlaviusFlav]

Replacing with zeros is a valid suggestion, however it won't work in
this scenario.

Missing and zero would actually be two distinct values in the dataset,
so I can't simply replace one with the other.

In addition, I can't use any sort of sentinel value, because I need the
client application to treat the data sent by the server as actual
values... there isn't really any room to budge here, as clients other
than excel will possibly be using the same data.

Is there any property that needs to be set to make having null numeric
values possible? The object already has rs:maybenull="true".

 

[arno]

Hi,

google for

2147467259 (80004005)

as an exact phrase. maybe there's something for you.

arno

 

[FlaviusFlav]

Thanks arno, google is always my first step. It almost always leads t
a quick solution, but it hasn't helped me in this case.

In my opinion, the error message must be fairly vauge or over used
because it seems to be used in a lot of cases that seem unrelated t
mine...

So far the only workaround I have been able to come up with was t
change the datatype returned to string, but since this informatio
could be used by a client application, I cannot practically labe
numeric values as string data

 

[arno]

Hi Flavius,

maybe you already did
but you could post your question in one of the ADO- or XML Newsgroups. Esp.
ADO-NG's helped with my problems with ADO/Access/Excel/ODBC/foxpro/Jet....

regards

arno

 

[FlaviusFlav]

Thanks again, arno. That definately sounds like its worth a shot
although I must admidt I havent posted directly to a newsgroup before.
I'm currently posting through a web forum.

Could you reccomend a specific group

 

[onedaywhen]

FlaviusFlav wrote ...

I need the
client application to treat the data sent by the server as actual
values... there isn't really any room to budge here, as clients other
than excel will possibly be using the same data.

Well, that doesn't change the fact that when then client is Excel you
can't put a 'null' into a cell, you cannot actively put 'nothing' in.
The nearest I can see: in the recordset change the nulls to the
sentinel value, assign the recordset to a range, then going though the
range and clear the cells with the sentinel values. If you are
concerned about changing the actual data, clone the recordset and
change the values in the clone. And if you want to use the same
component for different clients, you can always use e.g. an IsExcel
property.

--

 

[FlaviusFlav]

Onedaywhen: Your suggestion sounds like it would work for me. I don'
mind going through the extra steps of putting the sentinel in, the
removing it, as long as I can keep in invisible to the user.

I'm trying now to do this, but I'm not sure how, since even when I tr
to loop through the recordset row by row, I get the E_FAIL error when
try to advance to the row with the missing value.

Heres some code I was trying to use to loop through the recordset.

rs.MoveFirst
Dim f As Field
While Not rs.EOF
For Each f In rs.Fields
If (f.Type = adVarChar Or f.Type = adVarWChar) And _
(IsNull(f.Value) Or Trim(f.Value) = "") Then
MsgBox "missing character value found in column " & f.Name
ElseIf IsNull(f.Value) Then
MsgBox "missing numeric value found in column " & f.Name
Else
'Looks ok
MsgBox "no missing value found"
End If
Next f
rs.MoveNext '<-----dies on this line when the row with
missing is next
Wend


I'm not very experienced with this, so I wouldn't put it passed me tha
this code is fundamentally flawed, but I pulled it from the web, whic
everyone knows means it must be flawless ;-)

Anyways, it will successfully loop through signaling "no missing value
until the row with the missing is next up, then fails with the same ol
error message,
Run-time error '-2147467259 (80004005)':
Data provider or other service returned an E_FAIL status.

Is there a better way to loop through and look for missing values

 

[FlaviusFlav]

Trying to copy to an array using the getRows method of recordset fail
as well, same error

 

[FlaviusFlav]

For anyone interested, I didn't quite solve the problem, but it i
working now.

I had to change the format of the output to not have the missin
paramater at all.

<z:row INDEX="2004-04-05" x="1" />
<z:row INDEX="2004-04-06" x="2" />
<z:row INDEX="2004-04-07" x="3" />
<z:row INDEX="2004-04-08" x="4" />
<Z:ROW INDEX=\"2004-04-09\" /
<z:row INDEX="2004-04-12" x="6" />
<z:row INDEX="2004-04-13" x="7" />
<z:row INDEX="2004-04-14" x.="8" />
<z:row INDEX="2004-04-15" x.="9" />
<z:row INDEX="2004-04-16" x="10" /