advancequery.frm

本例中详细介绍了数据库连接的通用模块

VERSION 5.00
Begin VB.MDIForm AdvanceQuery 
   BackColor       =   &H8000000C&
   Caption         =   "高级查询"
   ClientHeight    =   3600
   ClientLeft      =   165
   ClientTop       =   555
   ClientWidth     =   5535
   LinkTopic       =   "MDIForm1"
   StartUpPosition =   1  'CenterOwner
   WindowState     =   2  'Maximized
   Begin VB.Menu mnuFile 
      Caption         =   "文件"
      Begin VB.Menu mnuExit 
         Caption         =   "退出"
      End
   End
   Begin VB.Menu mnuView 
      Caption         =   "视图"
      Begin VB.Menu mnuCreateview 
         Caption         =   "创建视图"
      End
      Begin VB.Menu mnuDeleteview 
         Caption         =   "删除视图"
         Enabled         =   0   'False
      End
   End
   Begin VB.Menu mnuProcedure 
      Caption         =   "存储过程"
      Begin VB.Menu mnuCreateprocedure 
         Caption         =   "创建存储过程"
      End
      Begin VB.Menu mnuDeleteprocedure 
         Caption         =   "删除存储过程"
         Enabled         =   0   'False
      End
   End
   Begin VB.Menu mnuTrigger 
      Caption         =   "触发器"
      Begin VB.Menu mnuCreatetrigger 
         Caption         =   "创建触发器"
      End
      Begin VB.Menu mnuDeletetrigger 
         Caption         =   "删除触发器"
         Enabled         =   0   'False
      End
   End
   Begin VB.Menu mnutransaction 
      Caption         =   "事务"
      Begin VB.Menu mnuBegintransaction 
         Caption         =   "开始事务"
      End
      Begin VB.Menu mnuCommittransaction 
         Caption         =   "确认事务"
      End
   End
End
Attribute VB_Name = "AdvanceQuery"
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
    flagTrigger = False
    
End Sub

Private Sub mnuBegintransaction_Click()
    frmSQL.txtQuery = "BEGIN TRANSACTION RoyaltyUpdate With MARK 'Update royalty values' Update roysched  Set royalty = royalty * 1.1    WHERE title_id LIKE 'Pc%'  COMMIT TRANSACTION RoyaltyUpdate "
    frmSQL.Show 1
End Sub

Private Sub mnuCommittransaction_Click()
    frmSQL.txtQuery = "BEGIN TRANSACTION UPDATE titles SET advance = advance * 1.25 WHERE ytd_sales > 8000 COMMIT"
    frmSQL.Show 1
End Sub

Private Sub mnuCreateprocedure_Click()
    Dim sSQL As String
    Dim Msgtext As String
    Dim recT As ADODB.Recordset
    
    sSQL = "IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P')  DROP PROCEDURE au_info"
    Set recT = ExecuteSQL(sSQL, Msgtext)
    
    
    frmSQL.txtQuery = "CREATE PROCEDURE au_info @lastname varchar(40),@firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name  FROM authors a INNER JOIN titleauthor ta   ON a.au_id = ta.au_id INNER JOIN titles t  ON t.title_id = ta.title_id INNER JOIN publishers p  ON t.pub_id = p.pub_id  WHERE  au_fname = @firstname AND au_lname = @lastname"
    frmSQL.cmdShow.Enabled = True
    frmRecordlist.txtSQL = "EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'"
    mnuCreateprocedure.Enabled = False
    mnuDeleteprocedure.Enabled = True
    frmSQL.Show 1
    
End Sub

Private Sub mnuCreatetransaction_Click()

End Sub

Private Sub mnuCreatetrigger_Click()
    Dim sSQL As String
    Dim Msgtext As String
    Dim recT As ADODB.Recordset
    
    sSQL = "IF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_trigger' AND type = 'TR')  DROP TRIGGER employee_trigger"
    Set recT = ExecuteSQL(sSQL, Msgtext)
    
    
    frmSQL.txtQuery = "CREATE TRIGGER employee_trigger  " & _
                       " ON employee FOR INSERT, UPDATE AS " & _
                       " DECLARE @min_lvl tinyint, " & _
                       "    @max_lvl tinyint, " & _
                       "    @emp_lvl tinyint, " & _
                       "    @job_id smallint " & _
                       " SELECT @min_lvl = min_lvl, " & _
                       "    @max_lvl = max_lvl, " & _
                       "    @emp_lvl = i.job_lvl, " & _
                       "    @job_id = i.job_id " & _
                       " FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id " & _
                       "    JOIN jobs j ON j.job_id = i.job_id " & _
                       " IF (@job_id = 1) and (@emp_lvl <> 10) " & _
                       " BEGIN " & _
                       "    RAISERROR ('触发器提示:Job id为1却省level应为10.', 16, 1) " & _
                       "    ROLLBACK TRANSACTION " & _
                       " End " & _
                       " Else " & _
                       " IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl) " & _
                       " BEGIN " & _
                       "   RAISERROR ('The level for job_id:%d should be between %d and %d.', " & _
                       "       16, 1, @job_id, @min_lvl, @max_lvl) " & _
                       "    ROLLBACK TRANSACTION " & _
                       " End "

    frmSQL.cmdShow.Enabled = True
    frmSQL.txtTrigger = "insert employee values ('PMA42628M','Paolo','M','Accorti','1','213','0877','2001-1-1')"
    mnuCreatetrigger.Enabled = False
    mnuDeletetrigger.Enabled = True
    flagTrigger = True
    frmSQL.Show 1
End Sub

Private Sub mnuCreateview_Click()
    Dim sSQL As String
    Dim Msgtext As String
    Dim recT As ADODB.Recordset
    
    sSQL = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'accounts')  DROP VIEW accounts"
    Set recT = ExecuteSQL(sSQL, Msgtext)
    
    
    frmSQL.txtQuery = "CREATE VIEW accounts (title, advance, amt_due) With ENCRYPTION AS SELECT title, advance, price * royalty * ytd_sales From titles WHERE price > $5    "
    frmSQL.cmdShow.Enabled = True
    frmRecordlist.txtSQL = "select * from accounts"
    mnuCreateview.Enabled = False
    mnuDeleteview.Enabled = True
    frmSQL.Show 1
End Sub

Private Sub mnuDeleteprocedure_Click()
    frmSQL.txtQuery = "DROP PROCEDURE au_info"
    mnuCreateprocedure.Enabled = True
    mnuDeleteprocedure.Enabled = False
    frmSQL.Show 1

End Sub

Private Sub mnuDeletetrigger_Click()
    frmSQL.txtQuery = "DROP TRIGGER employee_trigger"
    mnuCreatetrigger.Enabled = True
    mnuDeletetrigger.Enabled = False
    frmSQL.Show 1

End Sub

Private Sub mnuDeleteview_Click()
    frmSQL.txtQuery = "drop view accounts"
    mnuCreateview.Enabled = True
    mnuDeleteview.Enabled = False
    frmSQL.Show 1

End Sub

Private Sub mnuExit_Click()
    End
End Sub

Private Sub mnuRollbacktransaction_Click()

End Sub