Current Recordset does not support updating.

Current Recordset does not support updating.

Post by TWljaGFlb » Thu, 23 Dec 2004 03:31:07

Hi everyone.
I'm using SQL Server 2000, and I'm having a slight problem with a recordset
from a stored procedure. When the query is first opened all settings indicate
that the recordset is editable. As soon as I try to add/Modify a record I get
the following error:
Current Recordset does not support updating. This may be a limitation of the
provider, or of the selected locktype.

If I check the LockType right after executing a addnew to the recordset the
property changes to adLockReadOnly. This is a simple query too.
There is one other thing that I thought it could be. I have one field in the
table set to uniqueidentifier and I have newid() in the Default value field
in Enterprise manager. If this is the cause, I can change that easy. Please
let me know what you all think.
Here is an example of the code thats loading the recordset:
If rsInsurance.State <> adStateClosed Then
End If
Set mCommand = New ADODB.Command
mCommand.CommandType = adCmdStoredProc
mCommand.CommandText = "nb_GetInsurance"
mCommand.ActiveConnection = conn
mCommand.Parameters("@PatientID").Value = lPatientId
mCommand.Parameters("@AdmissionDate").Value = AdmissionDate
rsInsurance.CursorLocation = adUseClient
rsInsurance.CursorType = adOpenStatic
rsInsurance.LockType = adLockOptimistic
Set rsInsurance = mCommand.Execute
Here is the Stored procedure:

CREATE PROCEDURE [dbo].[nb_GetInsurance]
@PatientID nvarchar(12),
@AdmissionDate smalldatetime
Select KeyId, PatientID, AdmissionDate, Insurance_Name, Insurance_Number
From Insurance
Where PatientId = @PatientId and AdmissionDate = @AdmissionDate

I do have the CursorLocation set for the connection object. I added the
above CursorLocation to see if problem went away, but it didn't.

I have changed the above slightly but I'm getting the same results. I tried
two things so far.
1. I changed the CursorType to adOpenKeyset. But the same problem.
2. I also tried getting rid of the uniqueidentifier field and I had the same

Question. Would ahaving 3 fields as the primary key affect this problem.
That will be my next test. Thanks for any help.

Thanks again for any help.
Michael Lee

Current Recordset does not support updating.

Post by Kelly » Thu, 23 Dec 2004 03:48:37

I noticed that your curser type is adOpenStatic, static cursors = not

Try a dynamic cursor .. if that doesn't work, it may be a limitation of your
query driving your dataset. if you are selecting from multiple tables with
particular joins, you cannot add a record to that dataset.



Current Recordset does not support updating.

Post by TWljaGFlb » Thu, 23 Dec 2004 04:43:03

i Kelly,
I found the problem after finding a KB on the subject. I replaced the line:
Set rsInsurance = mCommand.Execute
with mCommand

And it seems as though I get a editable recordset now. Thanks for the reply.
Michael Lee

"Kelly C" wrote:


Current Recordset does not support updating.

Post by Mark J. Mc » Fri, 24 Dec 2004 17:37:47

"Kelly C" < XXXX@XXXXX.COM > wrote in message

Not sure where you got that, but sadly, it would be wrong. If the
underlying data source is writable, adOpenStatic does not change that in the