Current Recordset does not support updating 1

[MichaelaLee]

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. Thanks again for any help.
Michael Lee

 

[MichaelaLee]

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.
Michael Lee

 

[RoyVidar]

I think executing a command object like that, would give you a readonly recordset. I think you could perhaps try something like this:

[tt]set Set rsInsurance = new adodb.recordset
rsInsurance.open mCommand,,adopenstatic,adlockoptimistic,adcmdstoredprocedure[/tt]

Roy-Vidar

 

[MichaelaLee]

Hi Roy,
I just found a KB from Microsoft (KB188857) that talks to that some. This is a mayjor problem. I need to pass parameters to the stored proc. Its either that or open the recordset and then use the filter property to filter the data. I was hoping I would not have to do that. All this is causing me more problem because I was looking to bind the recordset to a TDBGrid (Component One Grid) and if I only have a read only recordset, this does me no good at all. WHY IS MICROSOFT DOING THIS FOR A SIMPLE QUERY. Thanks for you reply.
Michael Lee

 

[MichaelaLee]

Hi Roy,
After taing a look at the KB that I found. I tried the following. I replaced the line:
Set rsInsurance = mCommand.Execute
with
rsInsurance.open mCommand
And it seems to work fine. Thanks again for the help.
Michael Lee

 

[BobRodes]

I have a few observations of a general nature that might be helpful.

If your stored proc is a select query, then recordset.open is the right method, if it's an action query, then command.execute.

The cursortype is always static if the cursorlocation is client, and the latter overrides the former. Changing cursortype to keyset would therefore have no effect.

If you want a client side cursor to be updatable, the locktype must be adlockbatchoptimistic, and you have to use the updatebatch method. (Read up on "disconnected recordsets" if you're interested.)

I did an faq called "nickel tour of ado's different cursor types" (faq 222-3670, sorry, I haven't learned how to plug the hyperlink in here) if you're interested.

Bob

 

[johnwm]

(faq 222-3670, sorry, I haven't learned how to plug the hyperlink in here) if you're interested.

Just remove the space between faq and 222 - like this faq222-3670

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[BobRodes]

thanks john. faq222-3670 Looks like I got it. Star to Johnwm for going to the trouble to read through so many postings.

Bob