FN-FORUM: Updating / inserting a LONGTEXT field in mySQL using ASP / IIS

date posted 15th January 2007 11:11

Hi

I had a table I wanted to update with a LONTEXT field using the ADO
Update method. When I added a new record it worked fine. When I
updated a record it would cause an error if I lengthened the content
of this field. The error was =85

Error Type:
Microsoft Cursor Engine (0x80004005)
Data provider or other service returned an E_FAIL status.

=85 which I'd never seen before. Googling said it was a provider error
that basically meant it didn't know what was going wrong and you could
not tell much from it. It would update fine if I was shortening the
content. I temporarily solved the problem by just having a record in
the table with very long content for that field. For some reason I
was then always able to update this field with longer content in other
records. Fine =85 a mystery =85 but at least it worked.

However I have now added another LONGTEXT field to this table and it's
again causing problems. So I'm hoping there's some difference in the
way you update these fields when using mySQL as opposed to an MS db.

On my local set up I still sometimes had this error. Sometimes it
worked OK. Other times I'd get this error =85

Error Type:
Microsoft Cursor Engine (0x80040E21)
Multiple-step operation generated errors. Check each status value.
/lifo/administration/content/surveys/processupdatesurvey.asp, line 241

Line 241 is =85

rsSurvey("Introduction") =3D strIntroduction

which I'd then use the ADO update method on. 'Introduction' is one of
my LONGTEXT fields.

So I then decided to use a SQL update just for these two fields that
were causing the problem and it all worked fine locally using =85

strsSQL =3D "UPDATE tblSurveys SET Introduction =3D '" & strIntroduction &
"', Instructions =3D '" & strInstructions & "' WHERE SurveyID =3D " &
strID & ";"


However on my remote server I get this error =85

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-nt]You have an error
in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 's important to you,
how you get the most out of situations and how you interact ' at line
1
/lifo/administration/content/surveys/processupdatesurvey.asp, line 270

Line 270 is the line after the SQL above and is as follows=85

rsSurvey2.Open strsSQL, strConnect, adOpenForwardOnly,
adLockOptimistic, adCmdText

So can anyone shed some light on why I'm getting all these problems
for LONGTEXT fields? Is there another data type I should be using?

TIA
Paul