frmhwbmquery.frm

用vb和SQLSERVER编译的关于数据库的源程序例子。

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.ocx"
Begin VB.Form frmHwbmQuery 
   Caption         =   "货物查询"
   ClientHeight    =   4950
   ClientLeft      =   1605
   ClientTop       =   2445
   ClientWidth     =   8805
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   ScaleHeight     =   4950
   ScaleWidth      =   8805
   Begin VB.CommandButton Command 
      Caption         =   "退出(&X)"
      Height          =   345
      Index           =   1
      Left            =   7500
      TabIndex        =   7
      Top             =   630
      Width           =   1125
   End
   Begin VB.CommandButton Command 
      Caption         =   "查询(&R)"
      Height          =   345
      Index           =   0
      Left            =   7500
      TabIndex        =   6
      Top             =   180
      Width           =   1125
   End
   Begin VB.Frame Frame 
      Caption         =   "查询条件"
      Height          =   1275
      Index           =   0
      Left            =   120
      TabIndex        =   1
      Top             =   90
      Width           =   7245
      Begin VB.TextBox Text 
         Height          =   330
         Index           =   1
         Left            =   1230
         TabIndex        =   5
         Top             =   720
         Width           =   2025
      End
      Begin VB.TextBox Text 
         Height          =   330
         Index           =   0
         Left            =   1230
         TabIndex        =   3
         Top             =   300
         Width           =   2025
      End
      Begin VB.Label Label 
         Caption         =   "货物名称:"
         Height          =   195
         Index           =   1
         Left            =   180
         TabIndex        =   4
         Top             =   780
         Width           =   825
      End
      Begin VB.Label Label 
         Caption         =   "货物编码:"
         Height          =   195
         Index           =   0
         Left            =   180
         TabIndex        =   2
         Top             =   360
         Width           =   825
      End
   End
   Begin MSFlexGridLib.MSFlexGrid Flex 
      Height          =   3405
      Left            =   150
      TabIndex        =   0
      Top             =   1440
      Width           =   7215
      _ExtentX        =   12726
      _ExtentY        =   6006
      _Version        =   393216
      Cols            =   6
      AllowUserResizing=   3
      FormatString    =   "|<货物分类码|<货物分类名称|<货物编码|<货物名称|>货物单价"
   End
End
Attribute VB_Name = "frmHwbmQuery"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

'定义文本框索引对应的常量
Const TxtHwBmCode = 0
Const TxtHwBmMc = 1

'定义命令按钮对应的常量
Const CmdQuery = 0
Const CmdExit = 1

'点击按钮事件处理,Index是命令按钮的索引值
Private Sub Command_Click(Index As Integer)
On Error GoTo Errorhandle

   Select Case Index
   Case CmdQuery
         LoadData
   Case CmdExit
         Unload Me
   End Select

Exit Sub
Errorhandle:
   MsgBox Err.Description
End Sub
   
'从数据库取出数据在网格列表中列出
Private Sub LoadData()
   Dim ItemStr As String
   Dim WhereStr As String, SqlStr As String
   Dim ConnStr As String
   Dim Conn As ADODB.Connection
   Dim Rs As ADODB.Recordset
On Error GoTo Errorhandle
   
   '将列表内容清空，只保留一个标题行
   Flex.Rows = 1
   
   WhereStr = ""
   
   
   '产生连接字符串，连接数据库
   ConnStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=fiterp;Data Source=ERP002"

   Set Conn = New ADODB.Connection
   Conn.ConnectionString = ConnStr
   Conn.Open
   
   
   '根据界面上文本框的内容构造查询条件
   If Trim(Text(TxtHwBmCode).Text) <> "" Then
      WhereStr = WhereStr & " AND HWBMCODE LIKE '" & Trim(Text(TxtHwBmCode).Text) & "%'"
   End If
   
   If Trim(Text(TxtHwBmMc).Text) <> "" Then
      WhereStr = WhereStr & " AND HWBMMC LIKE '%" & Trim(Text(TxtHwBmMc).Text) & "%'"
   End If
   
   '产生新的记录集，使用记录集执行构造的SQL语句
   Set Rs = New ADODB.Recordset
   Set Rs.ActiveConnection = Conn
   
   SqlStr = "SELECT HWFLCODE,HWFLMC,HWBMCODE,HWBMMC,HWBMPRICE FROM HWBMREC,HWFLREC WHERE HWFLCODE=HWBMFLCODE "
   SqlStr = SqlStr & WhereStr
   SqlStr = SqlStr & " ORDER BY HWBMFLCODE,HWBMCODE"
   
   Rs.Open SqlStr
   
   '对记录集进行循环，将记录一行一行显示在列表中
   Do While Not Rs.EOF
      ItemStr = vbTab & Rs("HWFLCODE") & vbTab & Rs("HWFLMC") & vbTab & Rs("HWBMCODE") & vbTab & Rs("HWBMMC") & vbTab & Rs("HWBMPRICE")
      Flex.AddItem ItemStr
      Rs.MoveNext
   Loop
   
   Rs.Close
   Set Rs = Nothing
   
   Conn.Close
   Set Conn = Nothing
   
Exit Sub
Errorhandle:
   Set Rs = Nothing
   Set Conn = Nothing
   Err.Raise vbObjectError + 1, , Err.Description
End Sub