form1.frm

vb数据库编程资料

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   2850
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   5550
   LinkTopic       =   "Form1"
   ScaleHeight     =   2850
   ScaleWidth      =   5550
   StartUpPosition =   3  '窗口缺省
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   1815
      Left            =   240
      TabIndex        =   3
      Top             =   120
      Width           =   5175
      _ExtentX        =   9128
      _ExtentY        =   3201
      _Version        =   393216
   End
   Begin VB.CommandButton Command3 
      Caption         =   "关闭"
      Height          =   495
      Left            =   3960
      TabIndex        =   2
      Top             =   2160
      Width           =   1095
   End
   Begin VB.CommandButton Command2 
      Caption         =   "插入并显示"
      Height          =   495
      Left            =   2040
      TabIndex        =   1
      Top             =   2160
      Width           =   1455
   End
   Begin VB.CommandButton Command1 
      Caption         =   "连接并显示"
      Height          =   495
      Left            =   480
      TabIndex        =   0
      Top             =   2160
      Width           =   1215
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim Conn As New ADODB.Connection  '连接变量
Dim SqlString As String            '查询字符串
Dim Connstring As String           '连接字符串
Dim RS1 As New ADODB.Recordset     '记录集变量
Dim Cmd As New ADODB.Command
Private Sub Command1_Click()
  Connstring = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & App.Path & "\教学.mdb"
  '设置连接字符串
  With Conn
     .ConnectionString = Connstring '给ConnectionString属性赋值
     .ConnectionTimeout = 10
     .Open '给连接的ConnectionString属性赋值，然后使用求带参数的Open方法打开连接
  End With
  MsgBox ("连接成功")
   Set Cmd.ActiveConnection = Conn   '设置Command对象的ActiveConnection为某个打开的连接
 SqlString = "教师表" '形成查询字符串1
 Cmd.CommandText = SqlString     '设置Command对象执行的命令字符串
 Cmd.CommandType = adCmdTable    '设置命令类型
 Set RS1 = Cmd.Execute           '执行命令得到一个结果记录集
 Call ShowData(RS1, MSFlexGrid1) '调用过程显示记录集中的数据
 Command2.Enabled = False  '显示按钮不可用
 Command3.Enabled = True  '关闭按钮可用
End Sub
Private Sub Command2_Click()
 SqlString = "insert into 教师表 values('05003','杨权','女',#09/22/69#,'讲师','计算机学院',3855.50)" '形成查询字符串1
 Cmd.CommandText = SqlString     '设置Command对象执行的命令字符串
 Cmd.CommandType = adCmdText   '设置命令类型
 Cmd.Execute           '执行命令得到一个结果记录集
 SqlString = "教师表" '形成查询字符串1
 Cmd.CommandText = SqlString     '设置Command对象执行的命令字符串
 Cmd.CommandType = adCmdTable    '设置命令类型
 Set RS1 = Cmd.Execute           '执行命令得到一个结果记录集
 Call ShowData(RS1, MSFlexGrid1) '调用过程显示记录集中的数据
 Command2.Enabled = False  '显示按钮不可用
 Command3.Enabled = True  '关闭按钮可用
End Sub

Private Sub ShowData(Rs As ADODB.Recordset, Dgrid As MSFlexGrid)
     '该过程用来在Dgrid网格中显示记录集RS中的内容
     Dim RowNum As Integer
     RowNum = 1                    '代表MsFlexGrid控件的行数
     Dgrid.Rows = RowNum              '设置MsFlexGrid控件的行数
     Dgrid.Cols = Rs.Fields.Count    '设置MsFlexGrid控件的列数
     For j = 0 To Rs.Fields.Count - 1  '遍列所有列
        Dgrid.TextMatrix(0, j) = Rs.Fields(j).Name  '把列名显示在设置MsFlexGrid控件的首行
     Next j
     Do While Not Rs.EOF  '如果不是记录集的结尾
        RowNum = RowNum + 1 '行数加1
        Dgrid.Rows = RowNum   '设置MsFlexGrid控件的行数
        For j = 0 To Rs.Fields.Count - 1  '遍列所有列
          If Not IsNull(Rs.Fields(j).Value) Then  '如果列中的数据不为空
             Dgrid.TextMatrix(RowNum - 1, j) = Rs.Fields(j).Value  '把列的数据显示在MsFlexGrid的相应网格中
          End If
        Next j
        Rs.MoveNext  '记录指针下移
     Loop
     Command2.Enabled = False
End Sub

Private Sub Command3_Click()
   RS1.Close    '关闭记录集
   Conn.Close    '关闭连接
   End           '结束程序运行
End Sub