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
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]
Select KeyId, PatientID, AdmissionDate, Insurance_Name, Insurance_Number
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.