frmfind.frm

ado连接数据库的例子,对初学者很有帮助.

          'Get the absolute position
          intPosition = adoFind.AbsolutePosition
          'We found it, update bFound now
          bFound = True
       End If
     End If
     
     If bFound = True Then 'If we found it then
        Exit Do            'exit from this looping
     End If
     
     adoFind.MoveNext      'Process to next record
     DoEvents
     
     If adoFind.EOF Then   'If we are in EOF
        adoFind.MoveLast   'move to last record
        'Display message if we don't find it in looping
        MsgBox "'" & cboFind.Text & "' not found " & _
            "in field '" & cboField.Text & "'.", _
            vbExclamation, "Finished Searching"
        cmdFindNext.Enabled = False
        Exit Do
     End If
  Loop
  
  'If user check this checkbox
  If chkKonfirmasi.Value = 1 And _
     bFound = True And intCount <> 0 Then
     strFound = "Found '" & cboFind.Text & _
                 "' in record number " & adoFind.AbsolutePosition & vbCrLf
     'This iteration will get the name of all fields in
     'recordset, in order that we will display all data
     'in that record we found
     For i = 0 To adoFind.Fields.Count - 1
       'Check if the name contain "ChildCMD", exit from
       'iteration, we will not display this one.
       If adoFind.Fields(i).Name = "ChildCMD" Then
          Exit For
       End If
       'This will keep all data in record we found
       strFound = strFound & vbCrLf & _
            adoFind.Fields(i).Name & ": " & _
            vbTab & adoFind.Fields(i).Value
     Next i
     'Show the complete data in messagebox
     MsgBox strFound, vbInformation, "Found"
  End If

  If (adoFind.EOF) Then
     adoFind.MoveLast
     bFound = False 'We haven't found it, yet
     'Show messagebox
     MsgBox "'" & cboFind.Text & "' not found " & _
            "in field '" & cboField.Text & "'.", _
            vbExclamation, "Finished Searching"
     cmdFindNext.Enabled = False
     Exit Sub
  End If
  Exit Sub
Else 'If user select (All Fields)
  FindNextInAllFields  '<-- Call this procedure
  Exit Sub
End If
Message:
     adoFind.MoveLast
     bFound = False
     MsgBox "'" & cboFind.Text & "' not found " & _
            "in field '" & cboField.Text & "'.", _
            vbExclamation, "Finished Searching"
     cmdFindNext.Enabled = False
     Exit Sub
End Sub

Private Sub cmdCancel_Click()
  bCancel = True
  bFound = False
  'Empty string variable that we don't need
  m_ConnectionString = ""
  m_RecordSource = ""
  
  Set adoField = Nothing
  Set rs = Nothing
  Unload Me
  'Me.Hide  'Just hide this form, in order that we still
           'need the data later
End Sub

Private Sub Form_Load()
On Error Resume Next
  bCancel = False
  If cboFind.Text = "" Then
     cmdFindFirst.Enabled = False
     cmdFindNext.Enabled = False
  Else
     cmdFindFirst.Enabled = True
     cmdFindNext.Enabled = False
  End If
  Set cnn = New ADODB.Connection
  cnn.ConnectionString = m_ConnectionString
  cnn.Open
  Set rs = New ADODB.Recordset
  rs.Open m_RecordSource, db, adOpenKeyset, adLockOptimistic, adCmdTable
  cboField.Clear
  cboField.AddItem "(All Fields)"
  'This will get field name
  For Each adoField In rs.Fields
      cboField.AddItem adoField.Name
  Next
  rs.Close
  'Highlight the first item in combobox
  cboField.Text = cboField.List(0)
  'Get setting for this form from INI File
  Call ReadFromINIToControls(frmFind, "Find")
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
  'Save setting this form to INI File
  Call SaveFromControlsToINI(frmFind, "Find")
  'Clear memory
  Set adoFind = Nothing
  Set adoField = Nothing
  Screen.MousePointer = vbDefault
  Unload Me
End Sub

Private Sub CheckDouble()
Dim i As Integer
  If cboFind.Text = "" Then
     MsgBox "It can't not be a empty string!", _
            vbExclamation, "Invalid"
     cboFind.SetFocus
     Exit Sub
  End If
  For i = 0 To cboFind.ListCount - 1
    If cboFind.List(i) = cboFind.Text Then
       cboFind.SetFocus
       SendKeys "{Home}+{End}"
       Exit Sub
    End If
  Next i
  cboFind.AddItem cboFind.Text
  cboFind.Text = cboFind.List(cboFind.ListCount - 1)
End Sub

'This will search data in all fields for the very first time
Private Sub FindFirstInAllFields()
Dim strstrResult As String, strFound As String
Dim i As Integer, j As Integer, k As Integer
  'Always start from first record
  adoFind.MoveFirst
  strFind = cboFind.Text
  CheckDouble
Ulang:
  If adoFind.EOF And adoFind.RecordCount > 0 Then
     adoFind.MoveLast
     MsgBox "'" & strFind & "' not found in '" & cboField.Text & "'.", _
            vbExclamation, "Finished Searching"
     cmdFindNext.Enabled = False
     Exit Sub
  End If
  strstrResult = "":  strFound = ""
  With frmADOCode2
  For i = 0 To 4  'This iteration for data in textbox
      strResult = UCase(.txtFields(i).Text)
      If InStr(1, UCase(.txtFields(i).Text), UCase(strFind)) > 0 Then
         strstrResult = "" & strstrResult & "Found '" & strFind & "' at:" & vbCrLf & _
                      ""
       For j = 0 To 4 'This iteration for data in datagrid
          strResult = UCase(.txtFields(j).Text)
          If InStr(1, UCase(.txtFields(j).Text), UCase(strFind)) > 0 Then
             strFindNext = strFind
             'If we found it, tell user which position
             'it is...
              strstrResult = strstrResult & "" & vbCrLf & _
                 "  Record number " & CStr(adoFind.AbsolutePosition) & "" & vbCrLf & _
                 "  - Field name: " & .txtFields(j).DataField & "" & vbCrLf & _
                 "  - Contains: " & .txtFields(j).Text & "" & vbCrLf & _
                 "  - Columns number: " & j + 1 & " in DataGrid."
             For k = 0 To adoFind.Fields.Count - 1
                If adoFind.Fields(k).Name = "ChildCMD" Then
                  Exit For
               End If
               strFound = strFound & vbCrLf & _
                         adoFind.Fields(k).Name & ": " & _
                         vbTab & adoFind.Fields(k).Value
             Next k
             'Because we found, make cmdFindNext active...
             cmdFindNext.Enabled = True
             'If chkKonfirmasi was checked by user
             If chkKonfirmasi.Value = 1 Then
                'Display data
                 MsgBox strstrResult & vbCrLf & _
                        strFound, _
                        vbInformation, "Found"
             End If
          Else
          End If
       Next j  'End of iteration in datagrid
       Exit Sub
    Else
    End If
  Next i  'End of iteration in textBox
  End With
  'If we don't find in first record, move to next record
  adoFind.MoveNext
  GoTo Ulang
End Sub


'This will search data from the record position
'we found in FindFirstInAllFields procedure above.
'
Private Sub FindNextInAllFields()
Dim m As Integer, n As Integer, k As Integer
Dim strstrResult As String, strFound As String
strFindNext = strFind
If Len(Trim(strResult)) = 0 Then
   FindFirstInAllFields
   Exit Sub
End If
'Start from record position we found in FindFirstInAllFields
adoFind.MoveNext
strFound = "": strstrResult = ""
Ulang:
  'If we don't find it
  If adoFind.EOF And adoFind.RecordCount > 0 Then
     adoFind.MoveLast
     MsgBox "'" & strFindNext & "' not found in '" & cboField.Text & "'.", _
            vbExclamation, "Finished Searching"
     Exit Sub
  End If
  With frmADOCode2
  For n = 0 To 4  'This iteration for textbox
    strResult = UCase(cboFind.Text)
    'If we found it, all or similiar to it
    If InStr(1, UCase(.txtFields(n).Text), UCase(strFindNext)) > 0 Then
       strstrResult = "" & strstrResult & "Found '" & strFindNext & "' at:" & vbCrLf & _
                      ""
       For m = 0 To 4 'This iteration for datagrid
          strResult = UCase(cboFind.Text)
          If InStr(1, UCase(.txtFields(m).Text), UCase(strFindNext)) > 0 Then
             'If we found, tell user which record position
             'it is..
             strstrResult = strstrResult & vbCrLf & _
                 "  Record number " & CStr(adoFind.AbsolutePosition) & "" & vbCrLf & _
                 "  - Field name: " & .txtFields(m).DataField & "" & vbCrLf & _
                 "  - Contains: " & .txtFields(m).Text & "" & vbCrLf & _
                 "  - Column number: " & m + 1 & " in DataGrid."
             For k = 0 To adoFind.Fields.Count - 1
                If adoFind.Fields(k).Name = "ChildCMD" Then
                  Exit For
               End If
               'Get all data we found in that record
               strFound = strFound & vbCrLf & _
                         adoFind.Fields(k).Name & ": " & _
                         vbTab & adoFind.Fields(k).Value
             Next k
             'If chkKonfirmasi was checked by user
             If chkKonfirmasi.Value = 1 Then
                 'Display all data in that record we found
                 MsgBox strstrResult & vbCrLf & _
                        strFound, _
                        vbInformation, "Found"
                 cmdFindNext.Enabled = True
             End If
             Exit Sub
          Else
          End If
       Next m  'End of iteration in DataGrid
       Exit Sub
    Else
    End If
  Next n  'End of iteration in TextBox
  End With
  adoFind.MoveNext
  GoTo Ulang
End Sub