MichaelaLee
Programmer
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
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