basequery.frm

关于VB模块的

VERSION 5.00
Begin VB.MDIForm BaseQuery 
   BackColor       =   &H8000000C&
   Caption         =   "数据访问通用模块"
   ClientHeight    =   3675
   ClientLeft      =   165
   ClientTop       =   555
   ClientWidth     =   5595
   LinkTopic       =   "MDIForm1"
   StartUpPosition =   1  'CenterOwner
   WindowState     =   2  'Maximized
   Begin VB.Menu mnuFile 
      Caption         =   "文件"
      Begin VB.Menu mnuExit 
         Caption         =   "退出"
      End
   End
   Begin VB.Menu mnuDatabase 
      Caption         =   "数据库"
      Begin VB.Menu mnuCreatedatabase 
         Caption         =   "创建数据库"
      End
      Begin VB.Menu mnuDeletedatabase 
         Caption         =   "删除数据库"
         Enabled         =   0   'False
      End
   End
   Begin VB.Menu mnuTable 
      Caption         =   "表"
      Begin VB.Menu mnuAddTable 
         Caption         =   "创建表"
      End
      Begin VB.Menu mnuDeleteTable 
         Caption         =   "删除表"
      End
   End
   Begin VB.Menu mnuSelect 
      Caption         =   "数据选择"
      Begin VB.Menu mnuSelectdata 
         Caption         =   "选择指定数据"
      End
      Begin VB.Menu mnuSelectDatahe 
         Caption         =   "选择合成数据"
      End
      Begin VB.Menu mnuSelectmutitable 
         Caption         =   "选择多表"
      End
      Begin VB.Menu mnuSeletewhere 
         Caption         =   "筛选数据"
      End
   End
   Begin VB.Menu mnuModify 
      Caption         =   "数据修改"
      Begin VB.Menu mnuAddrecord 
         Caption         =   "添加数据"
      End
      Begin VB.Menu mnuModifyrecored 
         Caption         =   "修改数据"
      End
      Begin VB.Menu mnuDeleterecord 
         Caption         =   "删除数据"
      End
   End
End
Attribute VB_Name = "BaseQuery"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub MDIForm_Load()
    Dim txtSQL As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset
    
    txtSQL = "select * from students"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
        
    If mrc Is Nothing Then
        mnuDeleteTable.Enabled = False
        mnuAddTable.Enabled = True
    Else
        mnuDeleteTable.Enabled = True
        mnuAddTable.Enabled = False
    
    End If
    
End Sub

Private Sub mnuAddrecord_Click()
    Dim auID As String
    Dim auLname As String
    Dim auFname As String
    Dim auPhone As String
    Dim auAddress As String
    Dim auCity As String
    Dim auState As String
    Dim auZip As String
    Dim auContract As String
    Dim txtInsert As String
    Dim recT As ADODB.Recordset
    Dim sSQL As String
    Dim MsgText As String
    
    
    auID = InputBox("请输入插入纪录内容，作者编号为", "添加记录", Default)
    auLname = InputBox("请输入插入纪录内容，作者姓为", "添加记录", Default)
    auFname = InputBox("请输入插入纪录内容，作者名为", "添加记录", Default)
    auPhone = InputBox("请输入插入纪录内容，作者电话为", "添加记录", Default)
    auAddress = InputBox("请输入插入纪录内容，作者地址为", "添加记录", Default)
    auCity = InputBox("请输入插入纪录内容，作者所在城市为", "添加记录", Default)
    auState = InputBox("请输入插入纪录内容，作者所在省份为", "添加记录", Default)
    auZip = InputBox("请输入插入纪录内容，作者邮编为", "添加记录", Default)
    auContract = InputBox("请输入插入纪录内容，作者是否有合同为（输入0或者1）", "添加记录", Default)
    
    'If Trim(auID) <> "" Then
    sSQL = "select * from new_authors"
    Set recT = ExecuteSQL(sSQL, MsgText)
    
    If recT Is Nothing Then
        sSQL = "CREATE TABLE new_authors ( au_id    varchar(40), au_lname varchar(40), au_fname varchar(20), phone    varchar(40), address  varchar(40), city     varchar(20), state    varchar(40), zip         varchar(40), contract varchar(40))"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
        sSQL = "insert new_authors values ('172-32-1177','Liu','Tao','234','sadf','sda','f','1234','1')"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
        sSQL = "insert new_authors values ('172-32-1178','Test','Test','234','sadf','sda','f','1234','1')"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
        
    End If
    
    If (Trim(auID) <> "") And (Trim(auLname) <> "") And (Trim(auFname) <> "") And (Trim(auPhone) <> "") And (Trim(auAddress) <> "") And (Trim(auCity) <> "") And (Trim(auState) <> "") And (Trim(auZip) <> "") And (Trim(auContract) <> "") Then
        frmSQL.txtQuery = "insert new_authors values ('" & Trim(auID) & "','" & Trim(auLname) & "','" & Trim(auFname) & "','" & Trim(auPhone) & "','" & Trim(auAddress) & "','" & Trim(auCity) & "','" & Trim(auState) & "','" & Trim(auZip) & "','" & Trim(auContract) & "')"
        frmSQL.cmdShow.Enabled = True
        frmRecordlist.txtSQL = "select au_id,au_lname,au_fname,phone,address,city,state,zip from new_authors"
        frmSQL.Show 1
    Else
        MsgBox "没有完整输入数据，请重新输入！", vbOKOnly, "添加记录"
    End If
End Sub

Private Sub mnuAddTable_Click()
    'frmRecordlist.txtSQL = "CREATE TABLE students(student_id  smallint      IDENTITY(1,1)      PRIMARY KEY CLUSTERED,student_desc varchar(50)     NOT NUll      Default 'New Position - title not formalized yet',min_lvl tinyint NOT NULL     CHECK (min_lvl >= 10),max_lvl tinyint NOT NULL   CHECK (max_lvl <= 250) )"
    'MsgBox "创建表SQL语法：CREATE TABLE students(student_id  smallint      IDENTITY(1,1)      PRIMARY KEY CLUSTERED,student_desc varchar(50)     NOT NUll      Default 'New Position - title not formalized yet',min_lvl tinyint NOT NULL     CHECK (min_lvl >= 10),max_lvl tinyint NOT NULL   CHECK (max_lvl <= 250) )", vbOKOnly, "创建表"
    'frmRecordlist.Show
    frmSQL.txtQuery = "CREATE TABLE students(student_id  smallint      IDENTITY(1,1)      PRIMARY KEY CLUSTERED,student_desc varchar(50)     NOT NUll      Default 'New Position - title not formalized yet',min_lvl tinyint NOT NULL     CHECK (min_lvl >= 10),max_lvl tinyint NOT NULL   CHECK (max_lvl <= 250) )"
    mnuAddTable.Enabled = False
    mnuDeleteTable.Enabled = True
    frmSQL.Show 1
End Sub

Private Sub mnuCreatedatabase_Click()
    frmSQL.txtQuery = "CREATE DATABASE Sales ON ( NAME = Sales_dat,FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log',   FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB )"
    mnuCreatedatabase.Enabled = False
    mnuDeletedatabase.Enabled = True
    frmSQL.Show 1
End Sub


Private Sub mnuDeletedatabase_Click()
    frmSQL.txtQuery = "drop database sales"
    mnuCreatedatabase.Enabled = True
    mnuDeletedatabase.Enabled = False
    frmSQL.Show 1
End Sub

Private Sub mnuDeleterecord_Click()
    Dim recT As ADODB.Recordset
    Dim sSQL As String
    Dim MsgText As String
    Dim txtUpdate As String

    sSQL = "select * from new_authors"
    Set recT = ExecuteSQL(sSQL, MsgText)
    
    If recT Is Nothing Then
        sSQL = "CREATE TABLE new_authors ( au_id    varchar(40), au_lname varchar(40), au_fname varchar(20), phone    varchar(40), address  varchar(40), city     varchar(20), state    varchar(40), zip         varchar(40), contract varchar(40))"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
        sSQL = "insert new_authors values ('172-32-1177','Liu','Tao','234','sadf','sda','f','1234','1')"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
        sSQL = "insert new_authors values ('172-32-1178','Test','Test','234','sadf','sda','f','1234','1')"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
    End If

    If MsgBox("删除作者姓名为Test的纪录", vbOKCancel, "删除纪录") = vbOK Then
        mnuDeleterecord.Enabled = False

        frmSQL.txtQuery = "delete from new_authors where au_lname= 'test'"
        frmSQL.cmdShow.Enabled = True
        frmRecordlist.txtSQL = "select au_id,au_lname,au_fname,phone,address,city,state,zip from new_authors"
        
        frmSQL.Show 1
    Else
        Unload Me
    End If
End Sub

Private Sub mnuDeleteTable_Click()
    frmSQL.txtQuery = "drop table students"
    mnuDeleteTable.Enabled = False
    mnuAddTable.Enabled = True
    frmSQL.Show 1
End Sub

Private Sub mnuExit_Click()
    End
End Sub

Private Sub mnuModifyrecored_Click()
    Dim recT As ADODB.Recordset
    Dim sSQL As String
    Dim MsgText As String
    Dim txtUpdate As String

    sSQL = "select * from new_authors"
    Set recT = ExecuteSQL(sSQL, MsgText)
    
    If recT Is Nothing Then
        sSQL = "CREATE TABLE new_authors ( au_id    varchar(40), au_lname varchar(40), au_fname varchar(20), phone    varchar(40), address  varchar(40), city     varchar(20), state    varchar(40), zip         varchar(40), contract varchar(40))"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
        sSQL = "insert new_authors values ('172-32-1177','Liu','Tao','234','sadf','sda','f','1234','1')"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
        sSQL = "insert new_authors values ('172-32-1178','Test','Test','234','sadf','sda','f','1234','1')"
        Set recT = ExecuteSQL(sSQL, MsgText)
        
    End If

    txtUpdate = InputBox("输入要修改后的作者姓名：", "修改纪录", Default)
    

    frmSQL.txtQuery = "UPDATE new_authors   SET new_authors.au_lname = '" & Trim(txtUpdate) & " '   WHERE au_fname = 'TAO'"
    frmSQL.cmdShow.Enabled = True
    frmRecordlist.txtSQL = "select au_id,au_lname,au_fname,phone,address,city,state,zip from new_authors"
    
    frmSQL.Show 1
End Sub

Private Sub mnuSelectdata_Click()
    frmSQL.txtQuery = "select au_id,au_lname,au_fname,phone,address,city,state,zip from authors"
    'MsgBox "选择数据SQL:'select au_id,au_lname,au_fname,phone,address,city,state,zip from authors'", vbOKOnly, "SQL语法"
    frmSQL.cmdShow.Enabled = True
    frmSQL.Show 1

End Sub

Private Sub mnuSelectDatahe_Click()
    frmSQL.txtQuery = "SELECT au_lname + ', ' + au_fName  AS authorname,au_id,phone,address,city,state,zip,contract From authors ORDER BY au_lname, au_fname ASC     "
    'MsgBox "选择数据SQL:'select au_id,au_lname,au_fname,phone,address,city,state,zip from authors'", vbOKOnly, "SQL语法"
    frmSQL.cmdShow.Enabled = True
    frmSQL.Show 1

End Sub

Private Sub mnuSelectmutitable_Click()
    frmSQL.txtQuery = "SELECT a.au_lname, a.au_fname, t.title,a.phone,a.address,a.city,a.state,a.zip  FROM authors a INNER JOIN titleauthor ta  ON a.au_id = ta.au_id JOIN titles t   ON ta.title_id = t.title_id WHERE t.type = 'trad_cook' ORDER BY t.title ASC"
    frmSQL.cmdShow.Enabled = True
    frmSQL.Show 1
End Sub

Private Sub mnuSeletewhere_Click()
    frmSQL.txtQuery = "SELECT au_lname, au_fname,phone,address,city,state,zip,contract From authors WHERE au_id IN    (SELECT au_id   From titleauthor   WHERE royaltyper < 50)"
    frmSQL.cmdShow.Enabled = True
    frmSQL.Show 1
End Sub