Friday, 23th February 2007Adding to NTEXT or TEXT field causes Multiple-step OLE DB operation generated error (ASP)
Your SQL Server database has suddenly started giving the Multiple-step OLE DB operation generated error when you try and update it using a recordset.
Over the years I've had many enquiries from developes who, when updating an ntext or text field in a database whose structure hasn't changed for months, are suddenly getting the error:
Multiple step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done
In every instance that I've seen this occur, it has been caused by the fact that its a recordset being used to update the database rather than an SQL statement.
While many condemn the use of recordsets for updating databases, I do not - they can be very useful and have a canny ability to instantly return the index key of a database after the update command has been issued - nice and simple compared to SQL INSERT.
Nonetheless, recordsets do not handle large datafields well (NTEXT, TEXT, IMAGE, etc).
So if you've previously been using this
Set articleRS = Server.CreateObject("ADODB.Recordset")
articleRS.LockType = 3
articleRS.Open "SELECT * FROM MAGAZINE_articles WHERE articleID = " & articleID , "DSN=myDSNinfo"
articleRS("articleBody") = REQUEST("articleBody")
articleRS.Update
articleRS.Close
Set articleRS = Nothing
You may wwant to try this
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.ConnectionString = "dsn=myDSNinfo"
myConn.Open
myConn.Execute("UPDATE MAGAZINE_articles SET articleBody = '" & REQUEST("articleBody") & "' WHERE articleID = " & articleID)
myConn.Close
Set myConn = Nothing