NULL longtext fields leading to errors

[u3003-04-23]

Hi,

I have a problem where I have some longtext fields.

When I am using ASP to update these fields I get a range of errors when I try to do something like this:

<% sql = “SELECT my_longtext_field FROM myTable” set opRS = oConn.Execute(sql) longtextvar = opRS(“my_longtext_field”) '<

---

this is the line that generates the error

%>

The error is either:

Error Type:

(0x80020009)

Exception occurred.

Or:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I have found that the error occurs when the longtext field is empty. I have run an SQL statement which runs through to remove all the NULLS:

UPDATE myTable SET my_longtext_field = 'n/a' WHERE my_longtext_field IS NULL;

this removes some of the empty fields, but there are still a load which are empty but do not get picked up as being NULL.

When I look at the field in SQLyog it shows that the field is 0 bytes, and when you look at the field, it is empty.

How can I reference these empty fields to update them to be 'n/a', if they are not classed as NULL?

Thanks

Jim

[u3003-04-23]

In case it helps anyone else, I found a simple workaround, which fixed the problem.

e.g.

update v_board_all_notices

set site = 'n/a'

where

length(site) = 0 or

length(site) = 1 or

length(site) = 2 or

site = '' or

site is null;

jim

[Author]

Posts