Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Recordset does not support updating

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
naqayya
Asked:
naqayya
  • 6
  • 4
  • 4
  • +9
1 Solution
 
corvanderlindenCommented:
Do not use rsDonations.CursorType = adOpenDynamic
Just
Set rsDonations = myCmd.Execute
0
 
BahnassCommented:
I think just add

rsDonations.locktype=adlockoptimistic


before executing
0
 
naqayyaAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kir_ram_909Commented:
I think you should add the cursortype as adlockoptimistic
0
 
corvanderlindenCommented:
Look at your stored procedure
0
 
naqayyaAuthor Commented:
kir_ram_909:

adlockoptimistic can be assigned to locktype, not to cursortype.

Thanks.
0
 
naqayyaAuthor Commented:
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
 
corvanderlindenCommented:
I think that because of the Join you have a not updateable recordset
0
 
naqayyaAuthor Commented:
corvanderlinden:

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

Thanks.
0
 
corvanderlindenCommented:
Then I just do not know. Never had this problem with my sp's
0
 
naqayyaAuthor Commented:
Thanks anyway corvanderlinden.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mdouganCommented:
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
 
Richie_SimonettiIT OperationsCommented:
My two cents: it appears like a problem with sql statement.
0
 
rkot2000Commented:
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
 
mmcmillenCommented:
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
 
mlmccCommented:
listening
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mmcmillenCommented:
OK angell. I've always made my update stored procs  update statements.
0
 
mdouganCommented:
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
 
mdouganCommented:
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
 
naqayyaAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no objections. Glad we could help
CHeers
0
 
n_narayananCommented:
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
 
MoondancerCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 4
  • 4
  • +9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now