ADO Recordset not updateable

[Newbie]

Hi I have and ADO Recordset created from the following line

set rsQuote = cmdQuote.execute (this runs an SP in SQL Server)

The text field in the recordset includes LineFeed characters which I want to
strip out - for this I use the code ** below but I get the following error.

Error: 3251
Current Recordset does not support updating. This may be a limitation of the
provider, or of the selected locktype.

How can I overcome this?

Here is the code to update the field have stripped the LineFeed characters:

rsQuote.MoveFirst
Do Until rsQuote.EOF
tmpStr = rsQuote.Fields("Text")
If Nz(tmpStr, "") = "" Then
rsQuote.MoveNext
Else

For i = 1 To (Len(Trim(tmpStr)))
If Mid(tmpStr, i, 1) = vbLf Then
strNotes = strNotes + " "
If Mid(tmpStr, i, 2) = vbLf + vbLf Then
i = i + 1

End If

Else
strNotes = strNotes + Mid(tmpStr, i, 1)

End If

Next
rsQuote.Fields("Text").Value = strNotes
rsQuote.MoveNext

End If
Loop

 

[Douglas Marquardt]

Hi ?

afik, the result set from a sp is read only.
I do believe that if you want an updateable result set, then you
will have to use an SQL statement instead of a sp.

Hope this helps,

Doug.

 

[Newbie]

Thanks for that - I have got round it by creating another recordset once all
the records are in the table and then updating the text field
Thanks again for your help

 

[Jeff Johnson [MVP: VB]]

afik, the result set from a sp is read only.
I do believe that if you want an updateable result set, then you
will have to use an SQL statement instead of a sp.

Nope, not correct. There are several factors which will determine whether
you can update a resultset returned by an SP, but direct updating IS
possible.

 

[Douglas Marquardt]

Hi Jeff:

What are those factors?

Nope, not correct. There are several factors which will determine whether
you can update a resultset returned by an SP, but direct updating IS
possible.

 

[Ben Taylor]

Probably,
1) Whether the data is all from one table and not aggregated
2) Whether SQL server feels like it

 

[Dan Artuso]

Hi,
First, the lock type has to be updateable.
The default is adLockReadOnly which is not updateable.
adLockOptimistic or adLockBacthOptimistic will work.

There are a few more reasons but the the main one is that if you
use the cmd.Execute method instead of rs.Open, your cursor will
ALWAYS be read only.

 

[Jeff Johnson [MVP: VB]]

What are those factors?

Mostly the same as for any SQL statement: no aggregates, no DISTINCTs, etc.
If you can open a recordset with a given SQL statement directly, you should
be able to open it from a stored procedure that contains the same statement.

 

[Ben Taylor]

RETURNS the same statement, you mean.

Mostly the same as for any SQL statement: no aggregates, no DISTINCTs, etc.
If you can open a recordset with a given SQL statement directly, you should
be able to open it from a stored procedure that contains the same statement.

 

[Newbie]

Thanks but is it possible to open a recordset based on an Stored procedure
(with parameters) using the .Open command instead of cmd.exceute?
If yes, how do I do that?

Thanks
Al

 

[Eduardo A. Morcillo [MS MVP]]

Thanks but is it possible to open a recordset based on an Stored

procedure (with parameters) using the .Open command instead of
cmd.exceute?
If yes, how do I do that?

Rs.Open "EXEC SP_MySP 'Param1',10"

 

[Douglas Marquardt]

Hi Jeff:

Yeah... I knew that -- I must have been thinking cn.Execute when I replied.

Doug.