VB6 Recordet to insert into database example

Recordset_AddNew_Update.vb

'
' 此例使用VB6之 RecordSet 新增資料到資料庫
'
Private Sub OKButton_Click()
    On Error GoTo ErrorHandler

    '# resource
    Dim rs As New ADODB.Recordset
    Dim fields(8) As Variant ' 注意陣列大小需與下面的應用呼應
    Dim values(8) As Variant ' 注意陣列大小需與下面的應用呼應
    
    '# GO
    '#!! open empty recordset for data buffer, used to insert into database
    If DE.connMain.State <> adStateOpen Then DE.connMain.Open 'open ADO Connection when not open
    rs.Open "SELECT * FROM MyTable WHERE 1=0", DE.connMain, adOpenKeyset, adLockOptimistic 'get empty recordet
    
    Dim itm As ListItem
    For Each itm In lsvData.ListItems 
        If itm.Checked Then 'take the checked items
            '# parse item value
            Debug.Print "check item: " & itm.Text
                
            ' get SN / FILE_NO
            Dim sSN As String
            GetSn sSN, "MyTable" ' calculate SN
                        
            '#!! set field-value
            ' log fieds
            fields(0) = "CREATE_UID"
            values(0) = guFunction.UserID
                    
            fields(1) = "CREATE_DATE"
            values(1) = guSystemInfo.ServerFullTime
                    
            fields(2) = "MODIFY_UID"
            values(2) = guFunction.UserID
                    
            fields(3) = "MODIFY_DATE"
            values(3) = guSystemInfo.ServerDate
                       
            ' logical fields, 
            fields(4) = "CARD_NO"
            values(4) = itm.Text
            
            fields(5) = "HOLDER_NAME"
            values(5) = itm.SubItems(1)
            
            fields(6) = "HOLDER_IDN"
            values(6) = itm.SubItems(2)
            
            ' other required fill fields, 
            fields(7) = "FILE_NO"
            values(7) = sSN
            
            fields(8) = "DISPUTE_DATE"
            values(8) = Format(Now, "yyyy/mm/dd")
                    
            '#!! add new row into reocreset
            rs.AddNew fields, values
        End If
    Next
        
    '# do insert into database
    If rs.RecordCount = 0 Then
        MsgBox "未選取任何資料。", vbOKOnly, "訊息"
    Else
        '#!! do insert into database
        rs.Update
        ' post close message
        Unload Me
    End If
    
    ' success
    DialogResult = "Success"
        
FINALLY:
    'release resource
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox Erl & "OKButton_Click " & err.Description
    GoTo FINALLY
End Sub