Solved

ADO VB6 - Validating Input

Posted on 2012-03-13
8
1,105 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
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:HooKooDooKu
Comment Utility
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 29

Author Comment

by:gowflow
Comment Utility
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
Comment Utility
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
 
LVL 29

Author Comment

by:gowflow
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 16

Expert Comment

by:HooKooDooKu
Comment Utility
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 29

Author Comment

by:gowflow
Comment Utility
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 500 total points
Comment Utility
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 29

Author Closing Comment

by:gowflow
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now