ADO Recordset not updateable

  • Thread starter Thread starter Newbie
  • Start date Start date
N

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
 
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.
 
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
 
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.
Click to expand...

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.
 
Hi Jeff:

What are those factors?


Jeff Johnson said:
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.
Click to expand...
 
Probably,
1) Whether the data is all from one table and not aggregated
2) Whether SQL server feels like it
 
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.
 
What are those factors?
Click to expand...

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.
 
RETURNS the same statement, you mean.

Jeff Johnson said:
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.
Click to expand...
 
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
 
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?
Click to expand...

Rs.Open "EXEC SP_MySP 'Param1',10"
 
Hi Jeff:

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

Doug.
 
Back
Top