Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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.ActiveCe ll.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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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 "FlaviusFlav " schrieb im Newsbeitrag ... 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. 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.ActiveCe ll.Row + 1, Application.ActiveCell.Column).CopyFromRecordset rs I've been battling with this for some time now, and I'm not sure how to get around this. It also 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. I assume there must be a way to handle this with VBA, but for the life of me, I haven't been able to figure out. If anybody has any idea here, I would GREATLY appreiate the help. TIA --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
FlaviusFlav wrote in message ...
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;;@" -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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". --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
Hi,
google for 2147467259 (80004005) as an exact phrase. maybe there's something for you. arno |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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 -- Message posted from http://www.ExcelForum.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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 -- Message posted from http://www.ExcelForum.com |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
Trying to copy to an array using the getRows method of recordset fail
as well, same error -- Message posted from http://www.ExcelForum.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to handle null numeric values in ADO recordset
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" / -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TO DELETE ONLY NUMERIC VALUES IN A CHARACTER AND NUMERIC CELL IN | Excel Discussion (Misc queries) | |||
Showing NULL Command Bar handle | Excel Discussion (Misc queries) | |||
Handle missing values | Charts and Charting in Excel | |||
Return Numeric Labels that have different Numeric Values | Excel Worksheet Functions | |||
Creating small "recordset" or sorting a numeric 1-dim array | Excel Programming |