Solved

Recordset does not support updating

Posted on 2002-04-19
26
174 Views
Last Modified: 2013-11-26
I use some VBA code to connect to SQL Server and run an sp.

This is the code:

------------------------------------------
Dim myCmd As New ADODB.Command
Dim rsDonations As New ADODB.Recordset

...

myCmd.ActiveConnection = "Provider = SQLOLEDB; Data Source = ..."
myCmd.CommandType = adCmdStoredProc
myCmd.CommandText = "sp_Receipts_ByBatchNo"
myCmd.Parameters("@myBatchNo") = i
               
rsDonations.CursorType = adOpenDynamic
Set rsDonations = myCmd.Execute

rsDonations!RcptPrintDate = Date
rsdonations.update
...
------------------------------------------------------

I get the 'recordset does not support updating' error when I try to update the recordset. What do I have to do make this work (ie run sp and be able to update)?

Thanks.
0
Comment
Question by:naqayya
  • 6
  • 4
  • 4
  • +9
26 Comments
 
LVL 2

Expert Comment

by:corvanderlinden
ID: 6952988
Do not use rsDonations.CursorType = adOpenDynamic
Just
Set rsDonations = myCmd.Execute
0
 
LVL 3

Expert Comment

by:Bahnass
ID: 6953000
I think just add

rsDonations.locktype=adlockoptimistic


before executing
0
 

Author Comment

by:naqayya
ID: 6953055
corvanderlinden and Bahnass:

Sorry, your solutions do not work; I still get the same error message:

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Any suggestions.
Thanks.
0
 

Expert Comment

by:kir_ram_909
ID: 6953068
I think you should add the cursortype as adlockoptimistic
0
 
LVL 2

Expert Comment

by:corvanderlinden
ID: 6953094
Look at your stored procedure
0
 

Author Comment

by:naqayya
ID: 6953095
kir_ram_909:

adlockoptimistic can be assigned to locktype, not to cursortype.

Thanks.
0
 

Author Comment

by:naqayya
ID: 6953103
corvanderlinden: This is my sp:

REATE PROCEDURE sp_Receipts_ByBatchNo

@myBatchNo int
AS

select a.DonationID, a.PrintRcpts, a.RcptPrintDate, a.DonorRqst, d.RcptMailStatus, d.Country, b.Status
from tblDonors d inner join tblDonations a on d.DonorID = a.DonorID inner join tblBatchDetails b on a.BatchNo = b.BatchNo
where (a.BatchNo = @myBatchNo) and (a.PrintRcpts = 1) and (d.RcptMailStatus = 1) and (b.Status = 'CHECKED')
order by d.Country, a.DonorRqst, a.DonationID
GO
0
 
LVL 2

Expert Comment

by:corvanderlinden
ID: 6953144
I think that because of the Join you have a not updateable recordset
0
 

Author Comment

by:naqayya
ID: 6953166
corvanderlinden:

No, this recordset is updatable; I have updated a recordset created using the same SQL statement.

Thanks.
0
 
LVL 2

Expert Comment

by:corvanderlinden
ID: 6953199
Then I just do not know. Never had this problem with my sp's
0
 

Author Comment

by:naqayya
ID: 6953209
Thanks anyway corvanderlinden.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 60 total points
ID: 6953410
Hi,
your problem is a typical error:

replace
Set rsDonations = myCmd.Execute
by
rs.Open myCmd

The problem in your line is that cmd.Execute returns a completely new recordset object, erasing everything you set in the previous lines.

Cheers
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6953418
of course, read
rsDonations.Open
instead of
rs.open

BTW, you should not declare your variables AS NEW, unless really necessary. You have to add 1 line of code per variable, but if you stick with AS NEW, vb will add on each line it uses such a variable if it is already set, and does this line for you. Of course this might seem conveniant, but will slow down you code at long time.

CHeers
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 18

Expert Comment

by:mdougan
ID: 6953520
angellll has the right answer.  If you want to set recordset properties, like cursor type and cursor location, then you have to use the recordset open command, and not the command execute command.

So, with angellll's suggestion, and just one additional suggestion of my own, your code would be:

Dim myCmd As ADODB.Command
Dim rsDonations As ADODB.Recordset

...

Set myCmd = New ADODB.Command
myCmd.ActiveConnection = "Provider = SQLOLEDB; Data Source = ..."
myCmd.CommandType = adCmdStoredProc
myCmd.CommandText = "sp_Receipts_ByBatchNo"
myCmd.Parameters("@myBatchNo") = i
               
Set rsDonations = New ADODB.Recordset
rsDonations.CursorType = adOpenDynamic
' I think the default is a server side cursor which may not be updatable
rsDonations.CursorLocation = adUseClient
Set rsDonations.Source = myCmd
rsDonations.Open

rsDonations!RcptPrintDate = Date
rsdonations.update

' Always a good idea to clean house
rsDonations.Close
Set rsDonations = Nothing
Set myCmd = Nothing
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6953524
My two cents: it appears like a problem with sql statement.
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6953552
Command.execute returns a readonly recordset.

Dim myCmd As New ADODB.Command
Dim rsDonations As  ADODB.Recordset

...

myCmd.ActiveConnection = "Provider = SQLOLEDB; Data Source = ..."
myCmd.CommandType = adCmdStoredProc
myCmd.CommandText = "sp_Receipts_ByBatchNo"
myCmd.Parameters("@myBatchNo") = i
Set rsDonations As  ADODB.Recordset              

rsDonations.CursorLocation = adUseClinet
rsDonations.open myCmd,, adOpenDynamic, adLockOptimictic

rsDonations!RcptPrintDate = Date
rsdonations.update
0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 6953572
Where is the update in the sql statement?
I see selects. To update dont you have to SET some fields to a certain value?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 6953636
listening
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6953649
mmcmillen:
using ADO, you issue a select, and generate the ADO Recordset object. Using the ADO Recordset Object, you can update a field, and the update method will submit the necessary update statement to the underlying database...

CHeers
0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 6953662
OK angell. I've always made my update stored procs  update statements.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6954380
You can try adOpenKeyset instead of adOpenDynamic.

Richie could be right, when you're joining tables, sometimes a recordset will make itself Read-Only if it can't figure out which table to update, or, if it doesn't have a unique identifier for a row in the table.

In your SQL you are returning the DonationID which is hopefully the primary key of the tblDoners, and if you were only selecting columns from this table, then the recordset should be updatable.  However, since you're also selecting columns from other tables, your recordset may need to be read-only.  It's possible that if you included the primary key from each of the tables that you would be updating, the recordset might then be updatable, so, by adding d.DonationID and b.BatchNo to the list of selected columns it might work.

Also, I seem to remember reading somewhere about a new property available in ADO that let's you specify which underlying table in a recordset based on a join is the table that the recordset will allow you to update.  So, it could be that you will be able to update any of the columns from tblDoners, but not the columns from the other tables....
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6954390
Opps, got the table aliases confused, should read:

In your SQL you are returning the DonationID which is hopefully the primary key of the tblDonations, and
if you were only selecting columns from this table, then the recordset should be updatable.  
...
so, by adding d.DonerID and b.BatchNo to the list of selected columns it might work.


0
 

Author Comment

by:naqayya
ID: 6959743
mdougan:

I used CursorType = adOpenDynamic but had to use LockType = adLockOptimistic. As for client side or server side, I think updates can only be done on server side (I didn't set CursorLocation = adUseClient).

It worked this way, which means that my SQL was OK too.

I will split the points between angelIII and mdougan: 60, 40 resp.

Hope that is OK. Thanks all!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6959756
no objections. Glad we could help
CHeers
0
 
LVL 3

Expert Comment

by:n_narayanan
ID: 6959767
Normally this error will come you your database does not have any index.

Try indexing one of the Field and issue the update command.

Narayanan
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6959784
Thank you, I have reduced this question value to 60 points so you can award one here and post a new question for the other expert in this topic area to complete your requested point split award.  Please include this question link in the Points for question to provide the crossfoot.
Thank you,
Moondancer - EE Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now