form5.frm

一个数据库课程设计

VERSION 5.00
Begin VB.Form Form5 
   Caption         =   "查询"
   ClientHeight    =   4890
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7350
   LinkTopic       =   "Form5"
   ScaleHeight     =   4890
   ScaleWidth      =   7350
   StartUpPosition =   3  '窗口缺省
   Begin VB.PictureBox Picture1 
      Height          =   1935
      Left            =   720
      ScaleHeight     =   1875
      ScaleWidth      =   4755
      TabIndex        =   5
      Top             =   3960
      Width           =   4815
   End
   Begin VB.CommandButton Command1 
      Caption         =   "查询"
      Height          =   1095
      Left            =   360
      TabIndex        =   4
      Top             =   2160
      Width           =   1695
   End
   Begin VB.TextBox Text2 
      Height          =   615
      Left            =   2640
      TabIndex        =   1
      Text            =   "Text2"
      Top             =   840
      Width           =   2175
   End
   Begin VB.TextBox Text1 
      Height          =   615
      Left            =   360
      TabIndex        =   0
      Text            =   "Text1"
      Top             =   720
      Width           =   1215
   End
   Begin VB.Label Label2 
      Caption         =   "工作电话"
      Height          =   375
      Left            =   2880
      TabIndex        =   3
      Top             =   240
      Width           =   1695
   End
   Begin VB.Label Label1 
      Caption         =   "名字"
      Height          =   375
      Left            =   480
      TabIndex        =   2
      Top             =   240
      Width           =   1095
   End
End
Attribute VB_Name = "Form5"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub Data1_Validate(Action As Integer, Save As Integer)

End Sub

Private Sub DataGrid1_Click()

End Sub


























'初始化
Private Sub Init()
  Dim i As Integer
  Dim j As Integer
  
  msgrid1.TextMatrix(0, 0) = "num"
  msgrid1.TextMatrix(0, 1) = "单位"
  msgrid1.TextMatrix(0, 2) = "地址"
  msgrid1.TextMatrix(0, 3) = "名称"
  msgrid1.TextMatrix(0, 4) = "工作电话"
  msgrid1.TextMatrix(0, 4) = "类别"
  
  For i = 1 To msgrid1.Rows - 1
     For j = 0 To msgrid1.Cols - 1
       msgrid1.TextMatrix(i, j) = ""
     Next
  Next
End Sub
Private Sub QueryResult()
  Dim DBConn As ADODB.Connection
  Dim DBRst As ADODB.Recordset
  Dim SqlText As String
  Dim i As Integer
  
  Init
  
  Set DBConn = CreateObject("ADODB.Connection")
   
  'Set DBConn = CreateObject("ADODB.Recordset")
  Set DBRst = New ADODB.Recordset
  
  DBConn.ConnectionString = "DBQ=" + App.Path + "\db1;Driver={Microsoft Access Driver (*.mdb)}"
  DBConn.Open
  
   
  '根据用户选择构造SQL查询语句
  SqlText = "Select * from data where " + "text1.Text" + "=data.name"
  
  
  '**************  年龄 ****************
  Select Case Combo1(0).ItemData(Combo1(0).ListIndex)
    Case 0  '等于
       SqlText = SqlText + " Stu_Age= " + Text1(0).Text
    Case 1  '大于
       SqlText = SqlText + " Stu_Age>= " + Text1(0).Text
    Case 2  '小于
       SqlText = SqlText + " Stu_Age<= " + Text1(0).Text
  End Select
  '*************  性别  ******************
  Select Case Combo1(1).ItemData(Combo1(1).ListIndex)
    Case 0
      SqlText = SqlText + " and Stu_Sex=0 "
    Case 1
      SqlText = SqlText + " and Stu_Sex=1 "
    Case 2
      SqlText = SqlText
  End Select
  '*************** 名字********************
  If Len(Trim(Text1(1).Text)) > 0 Then
    SqlText = SqlText + " and Stu_Name like '%" + Trim(Text1(1).Text) + "%'"
  Else
    SqlText = SqlText
  End If
  
  'Set DBRst.ActiveConnection = DBConn
  'DBRst.Open SqlText, , 0, 1    'Open recordset.
  Set DBRst = DBConn.Execute(SqlText)
 
  i = 1
  While Not DBRst.EOF And Not DBRst.BOF
    msgrid1.TextMatrix(i, 0) = i
    msgrid1.TextMatrix(i, 1) = DBRst("Stu_Id")
    msgrid1.TextMatrix(i, 2) = DBRst("Stu_Name")
    msgrid1.TextMatrix(i, 3) = DBRst("Stu_Age")
    If DBRst("Stu_Sex") = 0 Then
      msgrid1.TextMatrix(i, 4) = "男"
    Else
      msgrid1.TextMatrix(i, 4) = "女"
    End If
    i = i + 1
    DBRst.MoveNext
  Wend
  
  DBRst.Close
  DBConn.Close
End Sub

Private Sub Command1_Click()
   QueryResult
End Sub

Private Sub Form_Load()
   Init
   Text1(0).Text = ""
   Text1(1).Text = ""
   Combo1(0).ListIndex = 0
   Combo1(1).ListIndex = 0
End Sub