frmmain.frm

vb连接SQL的小程序

        rsRecordSet.MovePrevious
        'Check to see if you are at the front of the record set.
        'If you are not, then you can move forward.
        
        If rsRecordSet.BOF = True Then
            'This will prevent the user from moving to
            'the BOF marker if he or she is on the first record.
            rsRecordSet.MoveFirst
        End If
    Else
        If rsRecordSet.EOF Then
            'Check to see if there is any data in the record set.
            MsgBox "There is no data in the record set!", , "Oops"
        Else
            rsRecordSet.MoveFirst
            'If the user is at the BOF marker, then move
            'to the first record.  There are several other
            'ways to handle this. For example, you could
            'loop the user to the last record by using
            'rsRecordset.MoveLast after the else statement.
            
        End If
    End If
    
    lblBOF.Caption = rsRecordSet.BOF
    lblEOF.Caption = rsRecordSet.EOF
End Sub

Private Sub cmdNext_Click()
    
    If rsRecordSet.EOF = False Then
        rsRecordSet.MoveNext
        
        If rsRecordSet.EOF Then
            'This will prevent the user from moving to
            'the EOF Marker if he or she is on the last
            'record.
            
            rsRecordSet.MoveLast
        End If
    Else
        If rsRecordSet.BOF Then
            'Check to see if there is any data in the recordset.
        
            MsgBox "There is no data in the record set!", , "Oops"
        Else
            rsRecordSet.MoveLast
            'Move the user to the last record if he or she
            'tries to move past the last record.  You
            'could let the user add a record by moving
            'past the EOF marker or you could loop back
            'to the first record.
        End If
    End If

    lblBOF.Caption = rsRecordSet.BOF
    lblEOF.Caption = rsRecordSet.EOF
End Sub

'***********
'*14th Step*    Add cmdAdd and cmdSave to the form.
'***********

'***********
'*15th Step*
'***********

Private Sub DisableNavigation()
    
    cmdFirst.Enabled = False
    cmdLast.Enabled = False
    cmdNext.Enabled = False
    cmdPrevious.Enabled = False
End Sub

Private Sub EnableNavigation()
    
    cmdFirst.Enabled = True
    cmdLast.Enabled = True
    cmdNext.Enabled = True
    cmdPrevious.Enabled = True
End Sub

'***********
'*16th Step*
'***********

Private Sub cmdAdd_Click()
    
    If cmdAdd.Caption = "&Add" And _
        cmdCloseConnection.Enabled = True Then
        'Allow adds when the connection is opened.
        
        cmdAdd.Caption = "&Cancel"
        'Change the caption to Cancel and use this button
        'to prevent a new record from being saved.
    
        cmdSave.Enabled = True
        'Since a new record is being added, the user
        'should be allowed to save the data.
        
        Call DisableNavigation
        'the user should not be allowed to navigate during an
        'add.
        
        mblnAddMode = True
        'We are now in addmode.
        
        Call ClearControls
        'This sub just clears the controls in preparation for
        'adding new data.
        
        cmdEdit.Enabled = False
        cmdDelete.Enabled = False
        'Coded later to add more stability.
        
        txtFirstName.Locked = False
        txtLastName.Locked = False
        txtTitle.Locked = False
        'Allow the user to enter items into the text boxes.
        
        txtFirstName.SetFocus
        'Go to the first field.
        
    ElseIf cmdAdd.Caption = "&Cancel" Then
        cmdAdd.Caption = "&Add"
        'If a user cancels, allow another add.
        
        cmdSave.Enabled = False
        'There will be no record to save.
        
        Call EnableNavigation
        'The user should have freedom to move now.
        
        cmdEdit.Enabled = True
        cmdDelete.Enabled = True
        'Added later to provide more security.
        
        mblnAddMode = False
        'We are no longer in addmode.
        
        txtFirstName.Locked = True
        txtLastName.Locked = True
        txtTitle.Locked = True
        'Allow the user to enter items into the text boxes.
        
        If cmdCloseConnection.Enabled = True Then
            'Make sure that the database connection is
            'open before you try to add data back to
            'the controls.
            
            Call LoadDataInControls
            'reload the data.
        End If
    End If
End Sub

'***********
'*17th Step*
'***********

Private Sub WriteDataFromControls()
    
    
    'Again, there are several ways to manipulate field
    'values.  Some are shown.
    
    rsRecordSet("Login").Value = txtFirstName.Text
    
    rsRecordSet.Fields("Password1").Value = txtLastName.Text
    
    rsRecordSet!Level = txtTitle.Text
    
End Sub

'***********
'*18th Step*
'***********

Private Sub cmdSave_Click()
    
    If cmdAdd.Caption = "&Cancel" Then
        rsRecordSet.AddNew
    End If
    'This calls the Recordset MoveComplete event.
    'Before cmdEdit was added, there was no if-then statement.
    
    Call WriteDataFromControls
    'Write the data from the text boxes to the appropriate
    'fields.
    
    rsRecordSet.Update
    'No data is saved until the update method is executed.
    
    mblnAddMode = False
    'Saving closes add mode.
    
    cmdSave.Enabled = False
    'Save does not need to be enabled after the save is executed.
    
    If cmdAdd.Caption = "&Cancel" Then
        cmdAdd.Caption = "&Add"
    End If
    
    
    If cmdEdit.Caption = "&Cancel" Then
        cmdEdit.Caption = "&Edit"
    End If
    'Changes captions back to original.
    
    'These three steps prevent the user from adding
    'blank data or accidently changing data.
    txtFirstName.Locked = True
    txtLastName.Locked = True
    txtTitle.Locked = True
    
    Call EnableNavigation
    'Allow movement again.
    
    cmdEdit.Enabled = True
    cmdAdd.Enabled = True
    cmdDelete.Enabled = True
    'Add later to provide stability and consistency.
    
    rsRecordSet.Close
    rsRecordSet.Open
    'This is not a very elegant solution to a problem I have
    'with this database.  If I add a new record and then
    'immediately delete it, it reappears the next time the
    'database is opened.  If anyone has a slicker solution
    'let me know.
    
    lblEOF = rsRecordSet.EOF
    lblBOF = rsRecordSet.BOF
End Sub

'***********
'*19th Step* Add cmdDelete to the form.
'***********

'***********
'*20th Step*
'***********

Private Sub cmdDelete_Click()

    If rsRecordSet.EOF = False And _
        rsRecordSet.BOF = False And _
        cmdCloseConnection.Enabled = True Then
        'Check to see if there is data in the database
        'and make sure it is open.

        On Error Resume Next
        'If there is an error, ignore it.
        
        adoDataConn.begtrans
        'Deleting a record is important so the begtrans method
        'is used.  It makes sure all actions between begtrans
        'and committrans are done at the same time.
        rsRecordSet.Delete
        'Delete the record.
        
        adoDataConn.CommitTrans
        'The actions have been committed.
        
        rsRecordSet.MoveNext
        If rsRecordSet.EOF = True Then
            rsRecordSet.MoveLast
            'If the user deletes the record in the last position
            'go to the new record in the last position.
            
            If rsRecordSet.BOF = True Then
                Call ClearControls
                'If the last record is deleted, clear the text
                'boxes.
                
                MsgBox "There is no data in the recordset!", , "Oops!"
                'Alert the user that there is no more data in
                'the database.
            End If
        End If
    ElseIf rsRecordSet.EOF = True And rsRecordSet.BOF = True Then
        'Warn the user that he or she is trying to delete data
        'from a database with no records.
        
        MsgBox "There is no data in the recordset!", , "Oops!"
    End If
    
    lblBOF.Caption = rsRecordSet.BOF
    lblEOF.Caption = rsRecordSet.EOF
End Sub

'***********
'*21st Step*  Add cmdEdit to the form and code its click event.
'***********

Private Sub cmdEdit_Click()
    'This sub is very similar to cmdAdd.
    
    '********Some changes were made to cmdSave in order to
    '********allow edits to be saved.
    
    If cmdEdit.Caption = "&Edit" And _
        cmdCloseConnection.Enabled = True Then
        'Allow adds when the connection is opened.
            
        cmdEdit.Caption = "&Cancel"
        'Change the caption to Cancel and use this button
        'to prevent a new record from being saved.
    
        cmdSave.Enabled = True
        'Since a new record is being added, the user
        'should be allowed to save the data.
        
        Call DisableNavigation
        'No moves during edit.
        
        cmdAdd.Enabled = False
        cmdDelete.Enabled = False
        'Coded later to add more stability.
        
        txtFirstName.Locked = False
        txtLastName.Locked = False
        txtTitle.Locked = False
        'Allow the user to enter items into the text boxes.
        
        txtFirstName.SetFocus
        'Go to the first field.
        
    ElseIf cmdEdit.Caption = "&Cancel" Then
        cmdEdit.Caption = "&Edit"
        'If a user cancels, allow another add.
        
        cmdSave.Enabled = False
        'There will be no record to save.
        
        Call EnableNavigation
        'Allow movement.
        
        cmdAdd.Enabled = True
        cmdDelete.Enabled = True
        'Coded later to add more stability.
        
        txtFirstName.Locked = True
        txtLastName.Locked = True
        txtTitle.Locked = True
        'Allow the user to enter items into the text boxes.
        
        If cmdCloseConnection.Enabled = True Then
            'Make sure that the database connection is
            'open before you try to add data back to
            'the controls.
            
            Call LoadDataInControls
            'reload the data.
        End If
    End If
End Sub

'***********
'*22nd Step*  Add cmdExit to the form and code its click event.
'***********

Private Sub cmdExit_Click()
    
    If cmdCloseConnection.Enabled = True Then
        'If the connection is opend, close it.
        Call cmdCloseConnection_Click
    End If
    
    Unload Me
    'Remove the form from memory.
    
    End
    'Terminate the project.
End Sub

'Summary.
'
'I.     Check Microsoft ActiveX DataObject 2.0 Library from the
'       Project Menu's Reference Menu.
'
'II.    A.  Declare your connection variarable.
'       B.  Declare your recordset variable.
'       C.  Declare a module level addstate boolean variable.
'
'III.   A.  Decide where to locate the code to open the
'           connection.
'           1.  Declare a connection string
'               a.  The string must have a provider.
'               b.  The string must have a path
'               c.  The string must have a database's name
'           2.  Set the connection object equal to a new ADODB
'               connection.
'           3.  Set the connection object's cursor location.
'           4.  Open the connection string with the connection
'               object.
'           5.  Open the connection object.
'           6.  Set the recordset object equal to a new ADODB
'               recordset.
'           7.  Set the recordset's properties.
'               a.  Select a cursor type.
'               b.  Select a curor location.
'               c.  Select a lock type.
'               d.  Select a source.
'               e.  Select a connection object.
'           8.  Open the recordset.
'       B.  Decide where to locate the code to close the
'           connection.
'           1.  Close the connection object.
'           2.  Set the connection object equal to nothing.
'
'IV.    Create controls on the form to view and enter data.
'
'V.     Create subs to load and clear controls holding data.
'
'VI.    Program the MoveComplete event to load data if the
'       program is not in add state.
'
'VII.   Provide recordset navigationi.
'       A.  Add command buttons for MoveFirst, MoveLast,
'           Previous, and Next.
'       B.  Check for BOF and EOF on Previous and Next.
'
'VIII.  Provide the ability to add records.
'       A.  Add a command button for adding records.
'       B.  Provide a method to cancel the current add.
'       C.  Add a command button that allows the user
'           to save a new addition.
'       D.  Code a procedure to move data from the form
'           to the database.
'
'IX.    Provide the user the ability to delete records.
'       A.  Add a command button for deleting records.
'       B.  Check for records before allowing deletes.
'
'X.     Provide the user the ability to edit records.
'       A.  Add a command button for editing recors.
'       B.  Allow the user to cancel the edit.
'       C.  Allow the user to save the changes.

'****************Extra Procedures for convience only************
Private Sub txtFirstName_KeyPress(KeyAscii As Integer)
    
    If KeyAscii = 13 Then
        SendKeys "{TAB}"
    End If
End Sub

Private Sub txtLastName_KeyPress(KeyAscii As Integer)
    
    If KeyAscii = 13 Then
        SendKeys "{TAB}"
    End If
End Sub

Private Sub txtTitle_KeyPress(KeyAscii As Integer)
    
    If KeyAscii = 13 Then
        SendKeys "{TAB}"
    End If
End Sub