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.



mvwilliamsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.