[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Update data using ADO

I opened an ADO recordset using the following statement.

    objRS.Open "dbo.sp_837p_claim (" & TranDetailId & ", " & ClaimId & ")", _
    m_objConn, adOpenKeyset, adLockOptimistic, adCmdStoredProc

I tried to update data from the screen in this recordset by doing the following

objRS!ClmNum = txtClmNum
objRS!ProviderId = txtProviderId
objRS.Update

When I come back into the record the data has not been changed.

What should be done to update the data?    I do know about using an UPDATE SQL statement and maybe that is route I will take but I am trying to figure out why this does not work with the update method of the recordset.



0
mvwilliams
Asked:
mvwilliams
1 Solution
 
Wayne_OwenCommented:
Shot in the Dark but try

objRS.edit
objRS!ClmNum = txtClmNum
objRS!ProviderId = txtProviderId
objRS.Update

Any error messages after the .Update ?
0
 
mvwilliamsAuthor Commented:
When I check the status of the recordset I receive the record was successfully updated.   If I change the recordset to adLockBatchOptimistic in the recordset open statement and use .UpdateBatch, I receive status of 8 which I think means the data was not modified.

I tried to use .edit as you suggested but only .EditMode was available and it was more a status of the edit than setting up the recordset for editing.
0
 
suprapto45Commented:
Hi mvwilliams,

Try this.

objRS!ClmNum = txtClmNum
objRS!ProviderId = txtProviderId
objRS.Update
objRS.Refresh
objRS.Requery

I am not sure, I leave VB 6 months ago. Perhaps you could change to this
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20183143.html

Regards
Dave
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
suprapto45Commented:
0
 
fiSBoN3Commented:
what type of database driver are you using? (oracle, ms access, firebird, etc.)
0
 
jaigan1979Commented:
IF THE STORED PROCEDURE THAT YUOU USED TO OPEN THE RRECORDSET CONTAINS NESTED QUERIES...I MEAN INNER OR OUTER JOINS, THE YOU MAY NOT BE ABLE TO UPDATE...EVEN IF THE STATUS RETURS TRUE THE RECORDS WILL REMAIN UNCHANGED....TRY CHANGING THE STORED PROCEDURE SUCH THAT IT HAS ONLY ONE TABLE REFERENCE IN IT.
0
 
jaigan1979Commented:
OR USE THE UPDATE STATEMENT....ITS VERY EACY....

m_objConn.EXECUTE "UPDATE <TABLE NAME> SET <FIELD1>='<VALUE1>', FIELD 2= 'VALUE2' WHERE <CONDITION>"

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now