Showing posts with label recordset. Show all posts
Showing posts with label recordset. Show all posts

Wednesday, March 21, 2012

Problems with null values

I'm reading data with ADO into a recordset. From the recordset, I create an XML string.

Unfortunately, when I go from rst to XML, any null values are chopped off and don't appear in the XML tree. This causes problems in further developments.

It would appear that the easiest way to fix the problem is to guarrantee that there are no nulls in the output data.

I've tried ' ' + tbl.column as NewCol but when tbl.column is null, the result is null also.

suggestions?

For the record, for some damn reason the FOR XML RAW command does not work on my SQL. Don't know why, but I'm trying to find a work around.select coalesce(tbl.column,'') as newcol

replaces null with zero-length string

use 0 for numeric fields, i guess

rudy|||u can check the null value while retreiving the value from a recordset to a variable by iif(isnull(rsAdo("column")),"",rsAdo("column"))|||A variation of r937's answer:

SELECT IsNull(Field, 0) AS Field1
or
SELECT IsNull(Field, 'unknown') AS Field1

Friday, March 9, 2012

Problems with disconnected recordsets i Yukon

We have a VB6 application using ADO 2.8 connecting to SQL Server.

We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on Yukon we get error when trying to update the disconnected recordset on the client.

Error: -2147217887 Multiple-step operation generated errors. Check each status value.

Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.

Is this the way Yukon is supposed to work? Is there anyway to disable this new functionality to really get a disconnected recordset?

One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.

I suspect that Yukon is more accurately reporting that certain columns are not updatable and ADO is marking the disconnected recordset fields as read only. I'll see if there is a way to disable this. One thing you could do to see if this is the case is save the ADO recordset to XML and compare XML from SQL 2000 and Yukon to see what is different.|||

I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.

I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.

|||Unfortunately I never found any better solution than the one I described.

Problems with disconnected recordsets i Yukon

We have a VB6 application using ADO 2.8 connecting to SQL Server.

We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on Yukon we get error when trying to update the disconnected recordset on the client.

Error: -2147217887 Multiple-step operation generated errors. Check each status value.

Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.

Is this the way Yukon is supposed to work? Is there anyway to disable this new functionality to really get a disconnected recordset?

One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.

I suspect that Yukon is more accurately reporting that certain columns are not updatable and ADO is marking the disconnected recordset fields as read only. I'll see if there is a way to disable this. One thing you could do to see if this is the case is save the ADO recordset to XML and compare XML from SQL 2000 and Yukon to see what is different.|||

I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.

I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.

|||Unfortunately I never found any better solution than the one I described.

Problems with disconnected recordsets i Yukon

We have a VB6 application using ADO 2.8 connecting to SQL Server.

We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on Yukon we get error when trying to update the disconnected recordset on the client.

Error: -2147217887 Multiple-step operation generated errors. Check each status value.

Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.

Is this the way Yukon is supposed to work? Is there anyway to disable this new functionality to really get a disconnected recordset?

One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.

I suspect that Yukon is more accurately reporting that certain columns are not updatable and ADO is marking the disconnected recordset fields as read only. I'll see if there is a way to disable this. One thing you could do to see if this is the case is save the ADO recordset to XML and compare XML from SQL 2000 and Yukon to see what is different.|||

I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.

I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.

|||Unfortunately I never found any better solution than the one I described.