asked on

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.

Wayne_Owen

Shot in the Dark but try

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

Any error messages after the .Update ?

mvwilliams

ASKER

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.

suprapto45

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
https://www.experts-exchange.com/questions/20183143/SQL-update-statement-using-ADO.html

Regards
Dave

suprapto45

Or this awesome URL

http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=48747&lngWId=1

Regards
Dave

fiSBoN3

what type of database driver are you using? (oracle, ms access, firebird, etc.)

jaigan1979

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.

ASKER CERTIFIED SOLUTION

jaigan1979

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial