?
Solved

ADO VB6 - Validating Input

Posted on 2012-03-13
8
Medium Priority
?
1,194 Views
Last Modified: 2012-06-27
Hello,

I am struggling with ADO and unfortunately do not have books around and need your help.

I have a VB6 App that has an ADODC data control with some 15 textboxes bound to that data control. the connection string is the following
Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DB.mdb;

I am having all the trouble in the world when it comes to data validation for update.

I usually in all my software uses the Data_validate event to trap if the record need to be saved and prompt the user "Do you want to Save Data ?" if he answers No then the Save=false and the record is not saved.

Here in ADO it is a bit more complex as we have several events that trap this
WillChangeField
WillChangeRecord
WillChangeRecordset
... AND
RecordChangeComplete
RecordsetChangeComplete
MoveComplete

So With all these events I am a bit lost ...
Have found some code that test events in the WillChangeRecord which I will attach here and is actually giving me trouble.

I also noticed that as soon as 1 field is changed then it kicks in the WillChangeRecord event and update the recordset with the changes !!!
I do not want that I want the user to be prompt only when he finishes updating the record to be prompt for Do you want to Save ? and he says no I need the record to remain intact and not be saved. I am not able to acheive this as even if the user answers No it is saving the record.

Here is my code

Private Sub datPrimaryRS_WillChangeRecord(ByVal adReason As adodb.EventReasonEnum, ByVal cRecords As Long, adStatus As adodb.EventStatusEnum, ByVal pRecordset As adodb.Recordset)
'This is where you put validation code
'This event gets called when the following actions occur
Dim bCancel As Boolean
Dim i As Long
Dim Before As String



Select Case adReason
    Case adRsnAddNew
    Case adRsnClose
    Case adRsnDelete
    Case adRsnFirstChange
    Case adRsnMove
    Case adRsnRequery
    Case adRsnResynch
    Case adRsnUndoAddNew
    Case adRsnUndoDelete
    Case adRsnUndoUpdate
    Case adRsnUpdate
        If Form1.BLLookup(0).Checked Then
            x = MsgBox("This record Cannot be altered as Read-Only database !", vbCritical, "[Details mid]")
            adStatus = adStatusCancel
        Else
            If UpdateCommon = False Then
                If MsgBox("Are you sure you want to change record ?", vbYesNo, "Change [Details mis]") = vbYes Then
                    
                    'pRecordset.UpdateBatch
                    UpdateCommonFields txtFields(4).Text, "Details mid"
                    If adReason = adRsnUpdate Then adStatus = adStatusUnwantedEvent
                    UpdateCommon = True
                    'pRecordset.UpdateBatch
                    'cmdRefresh_Click
                    
                Else
                    UpdateCommon = False
                    bCancel = True
                    adReason = adRsnUndoUpdate
                    'adStatus = adStatusCancel
                    If adReason = adRsnUpdate Then adStatus = adStatusUnwantedEvent
                End If
            End If
        End If
        'End If
End Select

'If bCancel Then adStatus = adStatusCancel


End Sub

Open in new window



I forgot to mention that the Recodsource is a complex Shape SQL that I could post later if necessary as on top of the textboxes I have a flexgrid that is filled with the details and the textboxes fills the header.

Also, I have an Update button there that has adodc.recordset.update there but ususally I rely on the move buttons to kick in the save record question. If user move left/right/beg/end on hte adodc then it will kick in the willchangerecord event and there I need the NO no to save the data.
Tks for your help
Rgds
Gowflow
0
Comment
Question by:gowflow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 37718430
I haven't used the events you are describing, but I'll take a stab at it.

First, let me make sure I understand the problem.

You want the user to make changes to multiple fields, then once all the fields are updated, update the whole record.  But the problem is that the database is attempting to be updated with each field change.

Well, given that the field is 'linked' to the database, then it makes sense that the database would be updated as soon as a field is updated.

So if you want to hold off on updates until the whole record is ready to be updated, then it would seem that you would have to use fields that are NOT linked to the database.  

So either unlink your fields and take full control over the database, or add a 2nd set of fields used for updating.  Once all the update fields have been set, you could copy the data from the update fields to the linked field, using a flag to only prompt the user for changes on the 1st change, and then set the flag and skip all additional warnings until the last field has been updated.  Then reset the flag.
0
 
LVL 31

Author Comment

by:gowflow
ID: 37718848
Well tks your advice and appreciate your attempt to propose a solution but unfortunately this is a bit cumbersome and immagine I have 16 text boxes with 10 lables and 1 grid control all linked to the database and need to duplicate all this ... !!! a bit too heavy.

This technique is actually used in my past data control like Data Control in VB and DAO but seems that ADO kickin this event for each field. where as in the others it would kick in on a move or expressly on an addnew/update event.

Anyway to make it a short story I need the following:

When I enter this procedure and adReason = adRsnUpdate if the user answer NO I need to know what is the UndoUpdate that need to be performed so it bring back the intial record as it was prior to updating.

gowflow
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 37720170
The easiest way I can think to be able to roll back changes on the entire record is to do a transaction.

Basically, before the 1st field is updated, issue ADO's equivalent of 'Begin Transaction'.  When you know all fields have been modified because you are going to load a new record, issue a 'Commit Transaction'.  But if at any time the user answers 'No', then do a 'Roll Back' and refresh the record.  All database changes since the Begin Transaction should automatically be undone.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 31

Author Comment

by:gowflow
ID: 37720661
well there is no begintrans and endtrans and rollback in ADO ! I am using the MSshape oledb
tks for your try. However if you have the correct syntax then I will give it a try.

gowflow
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 37721397
The ADO Connection object should have a '.BeginTrans', '.CommitTrans', and '.RollbackTrans' procedure.

So somewhere in your project should be the ADODB.Connection object.  That is the object that should have these procedures that you can call.
0
 
LVL 31

Author Comment

by:gowflow
ID: 37724937
Yes I found that but the main question how do you incorporate this to the willchangerecord event in practical code. Tks to adivse working solution in code
gowflow
0
 
LVL 15

Accepted Solution

by:
eemit earned 2000 total points
ID: 37729400
Hi gowflow,
try this:

Private m_bRecordChanged As Boolean
Private m_bCommonFieldChanged As Boolean

Private Sub datPrimaryRS_WillChangeField( _
                          ByVal cFields As Long, _
                          Fields As Variant, _
                          adStatus As ADODB.EventStatusEnum, _
                          ByVal pRecordset As ADODB.Recordset _
                          )
                          
  If Fields(0).Name = txtFields(4).DataField Then
      Debug.Print Fields(0).Value
      Debug.Print txtFields(4).Text
      
     If Fields(0).Value <> txtFields(4).Text Then
          m_bCommonFieldChanged = True
      End If
  End If

End Sub

Private Sub datPrimaryRS_WillChangeRecord( _
                          ByVal adReason As ADODB.EventReasonEnum, _
                          ByVal cRecords As Long, _
                          adStatus As ADODB.EventStatusEnum, _
                          ByVal pRecordset As ADODB.Recordset _
                          )
  
  If adReason = adRsnFirstChange Then
      m_bRecordChanged = True
  End If

End Sub

Private Sub datPrimaryRS_WillMove( _
                        ByVal adReason As ADODB.EventReasonEnum, _
                        adStatus As ADODB.EventStatusEnum, _
                        ByVal pRecordset As ADODB.Recordset _
                        )

  If m_bRecordChanged = True Then
      
      If Form1.BLLookup(0).Checked Then
          'x = MsgBox("This record Cannot be altered as Read-Only database !", vbCritical, "[Details mid]")
          MsgBox "This record Cannot be altered as Read-Only database !", vbCritical, "[Details mid]"
          
          datPrimaryRS.Recordset.CancelUpdate
          
      Else
          If m_bCommonFieldChanged = True Then
              
              If MsgBox("Are you sure you want to change record ?", vbYesNo, "Change [Details mis]") = vbNo Then
                  datPrimaryRS.Recordset.CancelUpdate
              End If
              
              m_bCommonFieldChanged = False
          End If
      End If
  
      m_bRecordChanged = False
  End If

End Sub

Open in new window

0
 
LVL 31

Author Closing Comment

by:gowflow
ID: 37770215
Hi eemit

I am sorry for the delay in replying. Indeed the key reside in the DatprimaryRS.Recordset.CancelUpdate method that I found but somehow could not get it to work.

I can tell you that puting it in the WillMove event is already too late as it will not undo the update I simply put it under the reason Update in the WillUpdateRecord and it did it perfectly.

The key issue was also to trap and first update in the
If adReason = adRsnFirstChange Then
      m_bRecordChanged = True
  End If
which you did very nicely and smartly.

Tks for your help
gowflow
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Simple Linear Regression

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question