frmmain.frm

vb连接SQL的小程序

VERSION 5.00
Begin VB.Form frmMain 
   Caption         =   "Form1"
   ClientHeight    =   5700
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   5835
   LinkTopic       =   "Form1"
   ScaleHeight     =   5700
   ScaleWidth      =   5835
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton cmdEdit 
      Caption         =   "&Edit"
      Height          =   495
      Left            =   3960
      TabIndex        =   16
      Top             =   4680
      Width           =   1215
   End
   Begin VB.TextBox txtLastName 
      Height          =   495
      Left            =   1800
      Locked          =   -1  'True
      TabIndex        =   6
      Top             =   2880
      Width           =   1215
   End
   Begin VB.CommandButton cmdDelete 
      Caption         =   "&Delete"
      Height          =   495
      Left            =   2760
      TabIndex        =   15
      Top             =   4680
      Width           =   1215
   End
   Begin VB.CommandButton cmdSave 
      Caption         =   "&Save"
      Enabled         =   0   'False
      Height          =   495
      Left            =   1560
      TabIndex        =   14
      Top             =   4680
      Width           =   1215
   End
   Begin VB.CommandButton cmdExit 
      Caption         =   "E&xit"
      Height          =   495
      Left            =   2520
      TabIndex        =   2
      Top             =   120
      Width           =   1215
   End
   Begin VB.CommandButton cmdAdd 
      Caption         =   "&Add"
      Height          =   495
      Left            =   360
      TabIndex        =   13
      Top             =   4680
      Width           =   1215
   End
   Begin VB.CommandButton cmdLast 
      Caption         =   "&Last"
      Height          =   495
      Left            =   3960
      TabIndex        =   12
      Top             =   4200
      Width           =   1215
   End
   Begin VB.CommandButton cmdNext 
      Caption         =   "&Next"
      Height          =   495
      Left            =   2760
      TabIndex        =   11
      Top             =   4200
      Width           =   1215
   End
   Begin VB.CommandButton cmdPrevious 
      Caption         =   "&Previous"
      Height          =   495
      Left            =   1560
      TabIndex        =   10
      Top             =   4200
      Width           =   1215
   End
   Begin VB.CommandButton cmdFirst 
      Caption         =   "&First"
      Height          =   495
      Left            =   360
      TabIndex        =   9
      Top             =   4200
      Width           =   1215
   End
   Begin VB.TextBox txtTitle 
      Height          =   495
      Left            =   1800
      Locked          =   -1  'True
      TabIndex        =   8
      Top             =   3600
      Width           =   1215
   End
   Begin VB.TextBox txtFirstName 
      Height          =   495
      Left            =   1800
      Locked          =   -1  'True
      TabIndex        =   4
      Top             =   2160
      Width           =   1215
   End
   Begin VB.CommandButton cmdCloseConnection 
      Caption         =   "Close Connection"
      Enabled         =   0   'False
      Height          =   495
      Left            =   1320
      TabIndex        =   1
      Top             =   120
      Width           =   1215
   End
   Begin VB.CommandButton cmdOpenConnection 
      Caption         =   "Open Connection"
      Height          =   495
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   1215
   End
   Begin VB.Label lblEOF 
      BorderStyle     =   1  'Fixed Single
      Height          =   255
      Left            =   5040
      TabIndex        =   22
      Top             =   480
      Width           =   735
   End
   Begin VB.Label lblBOF 
      BorderStyle     =   1  'Fixed Single
      Height          =   255
      Left            =   4200
      TabIndex        =   21
      Top             =   480
      Width           =   735
   End
   Begin VB.Label Label6 
      Caption         =   "EOF"
      Height          =   255
      Left            =   5040
      TabIndex        =   20
      Top             =   120
      Width           =   735
   End
   Begin VB.Label Label5 
      Caption         =   "BOF"
      Height          =   255
      Left            =   4200
      TabIndex        =   19
      Top             =   120
      Width           =   735
   End
   Begin VB.Label Label4 
      Alignment       =   1  'Right Justify
      Caption         =   "Title"
      Height          =   495
      Left            =   480
      TabIndex        =   7
      Top             =   3600
      Width           =   1215
   End
   Begin VB.Label Label3 
      Alignment       =   1  'Right Justify
      Caption         =   "Last Name"
      Height          =   495
      Left            =   480
      TabIndex        =   5
      Top             =   2880
      Width           =   1215
   End
   Begin VB.Label Label2 
      Alignment       =   1  'Right Justify
      Caption         =   "First Name"
      Height          =   495
      Left            =   480
      TabIndex        =   3
      Top             =   2160
      Width           =   1215
   End
   Begin VB.Label lblConnectionString 
      BorderStyle     =   1  'Fixed Single
      Height          =   615
      Left            =   120
      TabIndex        =   18
      Top             =   1320
      Width           =   5655
   End
   Begin VB.Label Label1 
      Caption         =   "Connection String:"
      Height          =   255
      Left            =   120
      TabIndex        =   17
      Top             =   960
      Width           =   1455
   End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'This is an example of Connecting to a mySql Databse DSNless
'Most of the code is not mine.  I have just modified it to
'Show how to connect to mySql  it was from a
'very good database tutorial found on this site.


Option Explicit



Private WithEvents adoDataConn As ADODB.Connection
Attribute adoDataConn.VB_VarHelpID = -1
'connConnection is the connection with the DB.
'A more descriptive name is usually desired.  For example:
'If you are connecting with the Northwind DB, connNWind
'would be better.

'The WithEvents keyword means that you抣l be able code
'events for the connection and the record set.  Also, you
'will be able to find the declared object in a code
'window抯 drop-down list and that each object will further
'provide its event procedures in the rightside drop-down
'of the code window.

Private WithEvents rsRecordSet As ADODB.Recordset
Attribute rsRecordSet.VB_VarHelpID = -1
'rsRecordSet is the recordset that will be used with the
'connection.A more descriptive name is usually desired.
'If you are connecting with the employees table,
'rsEmployees would be better.

Dim mblnAddMode As Boolean
'Used to determine whether data should be displayed
'or added.

'***********
'**  Create cmdOpenConnection &
'***********  cmdCloseConnection.

Private Sub cmdOpenConnection_Click()
    'Remember, these steps can be in another sub such
    'as the Form_Load event.
        

    
    Dim strConnect As String
    'This is your connection string.  It will contain
    'information about the provider and the path to
    'the database.
    
    Dim strProvider As String
    'In order to keep from typing long strings, I am
    'breaking the connection string into smaller parts.
    'It should be easier to read this way.
    
    Dim strDataSource As String
    'See note for strProvider.
    
    Dim strDataBaseName As String
    
    
    Dim usr_id As String ' the user id for the database
    Dim pass As String ' the password if used in your database
    Dim mySqlIP As String 'the ip address of the machine with the mySql
    mySqlIP = "127.0.0.1"  ' this is for localhost

    usr_id = "myID" ' user id
    pass = "myPass" 'password
    
    
    ' This is your connection string
    strConnect = "driver={MySQL};server=" & mySqlIP & ";uid=" & usr_id & ";pwd=" & pass & ";database=webcalendar"
    'The connection string is now made.
    
    Set adoDataConn = New ADODB.Connection
    'Preparing the connection object.
    
    
    
    adoDataConn.CursorLocation = adUseClient
    'Use a client side cursor because the data you will
    'be accessing will be on the client machine instead
    'of a server.
    
    adoDataConn.Open strConnect
    'Open the connection object.
    
    lblConnectionString.Caption = strConnect
    'Just in case you are interested in seeing the string.
    


    Set rsRecordSet = New ADODB.Recordset
    'Prepare the recordset.

    rsRecordSet.CursorType = adOpenStatic
    'The only type of curor that you can use with
    'a client side cursor location is adOpenStatic.
    
    rsRecordSet.CursorLocation = adUseClient
    'This application is using a client side cursor.
    
    rsRecordSet.LockType = adLockPessimistic
    'This guarantees that a record that is being edited
    'can be saved.
    
    rsRecordSet.Source = "Select * From tAdmin"  ' change to your table
    'Source should be a SQL statement indicating where to
    'retreive the data from.
    
    rsRecordSet.ActiveConnection = adoDataConn
    'The record set needs to know what connection to use.
    
    rsRecordSet.Open
    'Open the record set.  Opening the recordset will
    'cause the MoveComplete event for the recordset to fire.
    
    cmdOpenConnection.Enabled = False
    'Since the connection is now opened, one should not be
    'allowed to try to open it again.
    
    cmdCloseConnection.Enabled = True
    'Since the connection is open, one should be allowed
    'to close it
    
    'Now, the connection should be open and the recordset
    'ready to work with.

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

'***********
'* *  Create txtFirstName, txtLastName, txtTitle
'***********  Set the locked property to true for each one.

Private Sub ClearControls()

'***********
'* *
'***********

    txtFirstName.Text = ""
    txtLastName.Text = ""
    txtTitle.Text = ""
End Sub

Private Sub cmdCloseConnection_Click()
    
'***********
'* *
'***********
    
    adoDataConn.Close
    Set adoDataConn = Nothing
    
    cmdCloseConnection.Enabled = False
    'The user should not be allowed to close a connection
    'that is not open.
    
    cmdOpenConnection.Enabled = True
    'Since the connection is closed, it is okay to open
    'it again.

    Call ClearControls
    'Clear the textboxes.
    
    lblConnectionString.Caption = ""
    lblBOF.Caption = ""
    lblEOF.Caption = ""
End Sub

Private Sub LoadDataInControls()

'***********
'**
'***********

    If rsRecordSet.BOF = True Or rsRecordSet.EOF = True Then
        Exit Sub
        'If the pointer is at the end of the recordset of
        'at the before the first record, exit the sub and
        'show no data.
    End If
    
    'There are several methods of refering to field
    'contents. Here are examples of some different ways.
    
    txtFirstName.Text = rsRecordSet.Fields("Login").Value & " "
    'set these to fields in your database
    'Notice the " " appended to the end of the field.
    'This is necessary in order to prevent errors
    'loading if the field is empty.  At least a space will
    'be loaded into the text box.
    
    txtLastName.Text = rsRecordSet("Password1").Value & " "
    'The fields property is missing here.
    
    txtTitle.Text = rsRecordSet!Level & " "
    'This is the bang method.  Notice that there are no
    'quotes around the field name.
End Sub

'***********
'**
'***********
Private Sub rsRecordSet_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    
    If mblnAddMode = False Then
        'If we are not in the add mode, load data in the
        'controls.
        
        Call LoadDataInControls
        'This event was fired by opening the recordset in
        'step six.
        
    End If
End Sub


'***********
'**
'***********

Private Sub cmdFirst_Click()

    If rsRecordSet.BOF = False Then
        rsRecordSet.MoveFirst
        'Move to the first record in the record set.
    ElseIf rsRecordSet.BOF = True _
        And rsRecordSet.EOF = True Then
        
        MsgBox "There is no data in the record set!", , "Oops"
    End If
    
    lblBOF.Caption = rsRecordSet.BOF
    lblEOF.Caption = rsRecordSet.EOF
End Sub

Private Sub cmdLast_Click()

    If rsRecordSet.EOF = False Then
        rsRecordSet.MoveLast
        'Move to the last record in the record set.
    ElseIf rsRecordSet.BOF = True _
        And rsRecordSet.EOF = True Then
        
        MsgBox "There is no data in the record set!", , "Oops"
    End If
    
    lblBOF.Caption = rsRecordSet.BOF
    lblEOF.Caption = rsRecordSet.EOF
End Sub

Private Sub cmdPrevious_Click()

    If rsRecordSet.BOF = False Then