Editing in ADO Code

I have used Data1.Recordset.Edit for Editing record in DAO.
Now i am trying to use ADODB.Connection with ADODB.Recordset. I want to check the Status of the Recordset (EditMode) during Update. But I didn't get Edit Properties for the ADODB.Recordset.

How Can i check EDITMODE for Recordset if it's in Editmode ?
thangarajAsked:
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.

p_biggelaarCommented:
First of all, you can check the EditMode property to see whether some kind of edit is pending:

xx=rs.Editmode

Returnvalues are:
0: adEditNone
1: adEditInProgress
2: adEditAdd
4: adEditDelete

If you want to know whether you can update your recordset use :

xx=rs.Supports(adUpdate)

It will return true for an updatable recordset. See the MSDN library for the other conditions you can check with the Supports Method (adDelete, adAddNew etc...)
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
thangarajAuthor Commented:
How Could make Editinprogress. It means
like Data1.Recordset.Edit in DAO.

Any properties in ADO ?

0
p_biggelaarCommented:
is read-only and informs you whether there is no edit going on, or that the addnew command has been invoked, but the record is not yet saved (adEditAdd) etc...

When your recordset supports update (see proposed answer), you should be able to edit records without calling something like Edit in DAO. Just go ahead and do it  (though remember to throw in an rs.Update to commit the changes)

If the recordset is not updatable, try playing with CursorLocation, CursorType and LockType. This code for example will create an updatable dynamic recordset, with a clientside cursor:

    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockBatchOptimistic
    rs.Open "Select * from table", cnnActive
'Now I can go ahead and edit my data:
    rs.Fields(0).Value="New Value"
    rs.Fields(1).Value=0
    rs.Update
    rs.Close

0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

p_biggelaarCommented:
Sorry, in my Copy & Paste action from Word, I forgot to include the first line of my comment. Here we go:

No. You don't have to explicitly tell the recordset to go into editmode. The Editmode property in ADO is read-only and informs you whether there is no edit going on, or that the addnew command has been invoked, but the record is not yet saved (adEditAdd) etc...

When your recordset supports update (see proposed answer), you should be able to edit records without calling something like Edit in DAO. Just go ahead and do it  (though remember to throw in an rs.Update to commit the changes)

If the recordset is not updatable, try playing with CursorLocation, CursorType and LockType. This code for example will create an updatable dynamic recordset, with a clientside cursor:

    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockBatchOptimistic
    rs.Open "Select * from table", cnnActive
'Now I can go ahead and edit my data:
    rs.Fields(0).Value="New Value"
    rs.Fields(1).Value=0
    rs.Update
    rs.Close

Good luck!

0
thangarajAuthor Commented:
So When will get EDITMODE = editinprogress ?

So, U mean there is no way to make Recordset into EDITMODE like ADDNEW ?
0
thangarajAuthor Commented:
I have EDIT Button in the Toolbar. Now I am making True/False the LOCKED Property of the TEXT BOX while EDIT and ADDNEW.

But in DAO, I have done like ADDNEW, EDIT. So, we can get Recordset.EDITMODE = 0,1,2 respectively. This same i am trying to do in ADO.
0
p_biggelaarCommented:
Editmode will return adEditInProgress after you made changes, but before you committed data:

    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockBatchOptimistic
    rs.Open "Select * from table", cnnActive
'Now I can go ahead and edit my data:

'rs.Editmode = adEditNone    
    rs.Fields(0).Value="New Value"
'rs.Editmode = adEditInProgress
    rs.Fields(1).Value=0
'rs.Editmode = adEditInProgress
    rs.Update
'rs.Editmode=adEditNone
    rs.Close

If you want the user to be able to toggle between editmode and viewmode, you'll have to write some code, simply locking the editboxes at the proper time. You don't have to worry about the underlying code for editing, because nothing will be edited. Even if you want to be careful and make sure that nothing gets edited you can use a boolean variable that indicates whether edit/addnew is allowed and check the value of the boolean before you execute any edit or addnew code.

If you want to display whether the current recordset can be edited, or can only be viewed, use the Supports method.
To add a new record, you still have to use syntax like:

rs.Addnew
rs.fields(0)="New record"
rs.Update
0
p_biggelaarCommented:
So, in short:
- to get a recordset into editmode -> do nothing special. Simply assign new values to your fields and commit the changes by calling the Update method. It will be in editmode as long as you opened a recordset that supports adUpdate

- to get a recordset into addmode -> first call the AddNew method. (Make sure the recordset supports adAddNew)

- to delete a record, use the delete method

- to make sure a record cannot be edited or added either open the recordset (using the default locktype), so that supports(adUpdate) and Supports(adAddNew) will return false OR use variables to hold the mode that you want to allow and write some code to programmatically control the behavior.
0
p_biggelaarCommented:
The way you could use the editmode property is actually the same as you told you were doing in DAO.
0
thangarajAuthor Commented:
Thank U Very Much
0
p_biggelaarCommented:
you're welcome
0
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
Visual Basic Classic

From novice to tech pro — start learning today.