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
rsInsurance.Close
End If
Set mCommand = New ADODB.Command
mCommand.CommandType = adCmdStoredProc
mCommand.CommandText = "nb_GetInsurance"
mCommand.ActiveConnection = conn
mCommand.Parameters.Refresh
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
AS
Select KeyId, PatientID, AdmissionDate, Insurance_Name, Insurance_Number
From Insurance
Where PatientId = @PatientId and AdmissionDate = @AdmissionDate
GO

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
problem.

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
updatable.

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.





recordset
indicate
get
the
the
the
field
Please
Insurance_Number
tried
same

 
 
 

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
rsInsurance.open 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
news:9e_xd.538914$Pl.359700@pd7tw1no...

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
least.

-Mark




>