Re: Make a disconnected recordset editable

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Barry Seymour" <bseymour@xxxxxxxxxxx> wrote in message
news:%230CFIQkqIHA.1164@xxxxxxxxxxxxxxxxxxxxxxx
Dang, I thought I deleted this from my outbox before it went out. Oh well,
let me update this and answer your questions.

I added LockBatchOptimistic and got to where I could delete a row. Then I
tried to edit a field or add a field. no go. Here's what I get:

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

The recordset was NOT updatable when I first fetched it. It comes from a
stored procedure and is read only.

Here are my steps:
1. Use ADO command to run a stored procedure and get a read-only
Recordset.
2. Convert Recordset to an XML document
3. When needed, create a *new* ADO Recordset...
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
5. Load XML into the recordset
4. Try to update a field in a row. I get this error:
Error -2147217887: Multiple-step operation generated errors. Check each
status value.

Maybe step 1 is where I should look? I'm thinking the original recordset
must be updatable before I do anything else.

Not necessarily, it is surely easier that way, but you can fix this up at
the XML level.

First, at the recordset level, you must set the rs:Updatable attribute of
the s:Schema/s:ElementType node to true:

Set oElementType = dom.selectSingleNode("xml/s:Schema/s:ElementType")
oElementType.setAttribute "rs:updatable", "true"

Then, at the field level, you must make sure the rs:writeunknown attribute
is true, for each of the child nodes of oElementType -- unless that node
represents a hidden field, or an identity column (because setting
writeunknown to true for either of those will render the xml invalid to
ADO):

Set oAttributeTypes = oElementType.ChildNodes
For i = oAttributeTypes.length - 1 To 0 Step -1
DoEvents
Set oAttributeType = oAttributeTypes.Item(i)

If IsNull(oAttributeType.getAttribute("rs:hidden")) _
And
IsNull(oAttributeType.getAttribute("rs:AutoIncrement")) Then
oAttributeType.setAttribute "rs:writeunknown", "true"
End If
Next


If you ever want to push those changes back up to the server it becomes more
complicated, post back to this thread if that is the case.

Altering persisted recordsets at the XML level is the one and only way you
can extend the functionality of ADODB.Recordset. Of course, this practice
is neither supported nor recommended by Microsoft, but it does work reliably
and it accomplishes an otherwise impossible goal... that's enough for me.
:-)


-Mark







"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:%23Eg1nHkqIHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
1. Did you use the adLockBatchOptimistic locktype?
2. Could the recordset be edited before you saved it to xml?

Barry Seymour wrote:
Many thanks to Bob Barrows for help on my first problem ( see XML to
ADO Recordset and Back Again). Now here's another.

I am trying to create a disconnected, editable recordset from a stored
procedure call in VB6. I've Googled all the relevant code but I can't
get anything
that will let me *edit* the recordset. I always get an error saying
the recordset cannot be edited:

Here are my steps:
1. Use ADO command to get a stored procedure.
2. Convert to an XML document
3. When needed, create a new ADO Recordset, load XML into it
4. Try to insert/update/delete a row. I get this error:

Run-time error '3251':
Current Recordset does not support updating. This may be a
limitation of the provider, or of the selected locktype.

The recordset comes from a stored procedure, not a table. I fetch it
by way of an ADO command object. Are these indicators to ADO that the
recordset should be read-only?

I want to be able to edit the local recordset in order to avoid
repeated trips to the database for updated data.

Any help or pointers would be appreciated. Thanks in advance.

Barry

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.





.

Relevant Pages

  • Re: convert query into xml-file
    ... Transform to transform the XML from the ADO recordset schema to the ...
    (microsoft.public.access.queries)
  • Re: How does ADO 2 update a SQL stored procedure recordset?
    ... > Generally when I want to use ADO 2 to update a recordset based on a MSSQL ... > call a second stored procedure to perform the update. ... Well if you have SELECTed the columns in the recordset that form the Primary ... You are running SQL Server. ...
    (microsoft.public.data.ado)
  • Re: Returning results from a temporary table using ADO
    ... > I 'm trying to invoke a stored procedure using ADO from a web page. ... > The error manifest itself as an empty recordset or a closed recordset ... > creates and populates a temporary table and to have the results of that ...
    (microsoft.public.vb.database.ado)
  • Re: Creating XML docs from SQL server and Updating SQL server from XML
    ... What you need to do is to call Save method of ADO ... recordset with the adPersistXML option and your recordset will be saved into ... > Option 1 create a dom and build your own xml file ...
    (microsoft.public.vb.database.ado)
  • Re: Recordset load XML
    ... file, if you are using a version of ADO that supports the Stream object, you ... new Recordset ... Recordset return so I save the RecordSet to an XML string: ...
    (microsoft.public.dotnet.languages.csharp)