ADO Data Control and Oracle Sequences

I came across an interesting problem that Google didn’t find a solution for. I have a small Visual Basic application that manages a small table that maps Perception Questionmark exams to Course Codes in Training Server 4.8. Very small table, however I need for the Exam Manager to manage this table since I don’t have the resources or time to dedicate to making changes for her. So I wrote this real small Visual Basic application to manage the table for her. The problem came when the OleDB Oracle driver began to mangle the large numbers used in one of the fields used to map to the Perception Session ID. Changing from the Oracle OleDB driver to an ODBC connection corrected that issue, however I had to manually create a primary key field based off of an auto-generating sequence. This really wouldn’t be an issue, except for two little problems. First, I am using the ADO Data Control, which does not auto-generate a new primary key, and second is that I cannot create a trigger to reside on the server due to policy restrictions set up by the DBA.

The solution I used is not necessarily elegant, but it did do the job without having to scrap what I had, keeping the same interface the user was already used to using, and solved the problem. Basically what I had to do is modify the ADO Data Controls WillMove and WillChangeRecord functions to call the Oracle Sequence and store in a hidden bound text control on the form.

The logic works like this. When the ADODC creates a new record, it will call the WillChangeRecord function, passing in the variable adReason set to adRsnAddNew. When the user fills in the bound text fields with the appropriate data, they can either hit the update button or use the ADODB to move, and the record will insert into the database. If they click on the Update button, this calls WillChangeRecord with adReason set to adRsnUpdate. If they click move, the function I want to focus on is the WillMove function. Both scenarios will do the same thing, have a custom code based ADO call to Oracles sequence to generate the next value and save it into the hidden text control for the key. However, I do not want to have this happen when update is called to modify an existing record. So what I do is create a global Boolean value that gets set when WillChangeRecord is called with adReason is set to adRsnAddNew. Once the update is complete, this flag gets set back to false. Below is the code demonstrating this.

'Crappy module wide flag determining if we are adding a new record
'if set, certain if branches will execute updateKeyValue function
'Module Scope within Form1
Dim needtoupdate As Boolean

‘Procedure to update the Form1.txtKey value to the next sequence value.
‘This textbox is bound to the primary key of the test map table
Private Sub updateKeyValue()
    'Store the new key generated from Oracle
    Dim mintNewKey As Integer
    
    'The three ADO components
    Dim com As ADODB.Command
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    On Error GoTo ErrOnUpdate
    
    'Create new objects for the command and connection
    Set con = New ADODB.Connection
    Set com = New ADODB.Command

    'Use the same connection string as the ADO Data Control and open
    'the connection
    con.ConnectionString = Adodc1.ConnectionString
    con.CursorLocation = adUseClient
    con.Mode = adModeRead
    con.Open
        
    'Set up the active connection
    Set com.ActiveConnection = con
    com.CommandType = adCmdText
    com.CommandText = "select SEQ_TESTMAP.nextval from dual"
    
    'Execute the command and get the recordset
    Set rs = com.Execute
        
    'Store the new key value into the mintNewKey variable
    mintNewKey = rs("nextval").Value
    
    'Save this to the hidden bound text control on Form1
    Form1.txtKey.Text = mintNewKey
    
    'Reset the update flag to false
    needtoupdate = False
    
    'Free objects
    If Not (rs Is Nothing) Then
        Set rs = Nothing
    End If
    
    If Not (com Is Nothing) Then
        Set com = Nothing
    End If
    
    If Not (con Is Nothing) Then
        If con.State = ADODB.adStateOpen Then
            con.Close
        End If
        
        Set con = Nothing
    End If
    
    Exit Sub
ErrOnUpdate:
    If Not (rs Is Nothing) Then
        Set rs = Nothing
    End If
    
    If Not (com Is Nothing) Then
        Set com = Nothing
    End If
    
    If Not (con Is Nothing) Then
        If con.State = ADODB.adStateOpen Then
            con.Close
        End If
        
        Set con = Nothing
    End If
    
    MsgBox "There was an error updating the database: " & vbNewLine & Err.Number & " - " & Err.Description, vbCritical, "Error"
End Sub

Private Sub Adodc1_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    'If the new record has already been inserted, this function was called
    'to issue and update, and the update flag is set, then call the
    'update key value function
    If (adReason = adRsnUpdate) And (needtoupdate) Then
        updateKeyValue
    End If
    
    'If we are creating a new record, set the need to update flag to true
    If (adReason = adRsnAddNew) Then
        needtoupdate = True
    End If
End Sub

Private Sub Adodc1_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    If (needtoupdate) Then
        updateKeyValue
    End If
End Sub

Typically I never use the ADO Data Control for this very reason, however time constraints and drive prevented me from hand coding all the controls for navigating through he recordset, and despite this one hurdle, using the ADO Data Control was relatively painless. While I have worked with similar components in Delphi in the past, which were much more robust in my opinion, for the task at hand this was sufficient. Had triggers been allowed, this would have been a simple matter to address on the backend. However the constraints of the operating environment did not allow for this, so I had to adapt the solution to the client side. I had to admit surprise at not finding a solution online. Perhaps it was on a VB forum somewhere and the thread has been depreciated and archived. Once .Net gets pushed out as SOE here, I can finally retire VB6, and I hate to admit it, but I will be sad to see it go. I will have to do more research into why the Oracle OleDB driver mangled those large numbers, that seems really strange.