Update Record Problem

[Mobius]

I have a class written for the database functions: Insert, update, delete, retrieve.


Scenario:
Load form:

NAME

ADDRESS

City

State

Zip

[Close][Apply][Delete][Add][Cancel]

This forms allow users to make multiple changes without committing each change. User can cancel all changes if he so desires. I am using BeginTrans, CommitTrans and RollbackTrans.

Problem is with the updates. Is there a recordset property that set it to an "updated" state? I need to update and requery everytime a user scrolls through the records and only if a change has been made. Is there an easy way to do this?

Thanks.

Hi, i dont know if this information could help you. ADO recordsets support BatchUpdate property along with CancelBatchUpdata. Also you would be interested in MarshalOptions property which takes two values either adall or admodified (something like these) which could be used as appropriate.

Hope the information could give you atleast some idea.

All the best...vijay

[Mobius]

I figured out that my locktype keeps resetting back to a readonly lock. I am using stored procedures to do add, delete and updates using the command object. Is that a problem?

Here is part of my code in the retrieve function that returns a recordset:

Public Function RetrieveAll(ByVal param1 As String, _
ByVal param2 As String, _
ByVal param3 As String, _
ByVal param4 As String) As Variant

Dim rs As ADODB.Recordset
Dim strCommand As String
Dim SelectClause As String
Dim WhereClause As String
Dim OrderByClause As String

...
...

Set rs = new ADODB.Recordset
With rs
.ActiveConnection = cn
.LockType = adLockPessimistic
.Cursortype = adOpenKeySet
.Source = strCommand

' here the locktype and
' cursortype gets set back to the default
.Open
end With

' Return results to caller
Set RetrieveAll = rs

I have been stuck for days. Can someone help me, please?

[monte96]

Command objects and connection objects return only readonly, forwardonly recordsets. If you are using stored procs, you'll need to construct a execute query for your proc that passes your parameters and use the rs.open method and the query.

Command objects are great for getting data for recordsets that populate controls, but you can't perform updates with recordsets retrieved from them. You could use a stored proc that does the insert or update and you pass your values that change to it. Not really an option for what you are describing though.

[Mobius]

Thanks to everyone for helping.

I feel so confused and stupid. I am really new to VB.
Anyway, I had to set the cursortype to adUseClient.
Spent hours before I finally tried it.

I had to rewrite most of my code. I was using Command object and stored procedures but I give up. I cannot get it to work the way its specified. I have to use the recordset
methods to do batchUpdate etc.

I was hoping to use
Classes since we are developing both a desktop app and web based app.
Does anyone have any suggestions on how to use classes when using the recordset object?

[monte96]

Instead of the command object, use the recordset to execute your stored proc.. see this thread:

http://209.207.250.147/showthread.php?threadid=36746