mshflexgrid如何聯動數據庫刪除

# MSHFlexGrid如何聯動數據庫刪除

## 引言

MSHFlexGrid是Visual Basic 6.0中常用的網格控件，它提供了強大的數據展示和操作功能。在實際開發中，經常需要實現通過MSHFlexGrid選擇數據并聯動數據庫進行刪除操作。本文將詳細介紹如何實現這一功能，包括環境準備、數據庫連接、刪除邏輯實現以及錯誤處理等內容。

---

## 一、環境準備

### 1.1 控件引用
在VB6中使用MSHFlexGrid前，需確保已添加控件引用：
1. 打開VB6工程
2. 選擇"工程"→"部件"
3. 勾選"Microsoft Hierarchical FlexGrid Control 6.0"

### 1.2 數據庫準備
本文以Access數據庫為例，表結構如下：
```sql
CREATE TABLE Employees (
    ID AUTOINCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary CURRENCY
)

二、數據庫連接配置

2.1 ADO連接設置

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\EmployeeDB.mdb"
    conn.Open
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM Employees", conn, adOpenDynamic, adLockOptimistic
    
    Set MSHFlexGrid1.DataSource = rs
End Sub

2.2 網格屬性設置

With MSHFlexGrid1
    .AllowUserResizing = flexResizeColumns
    .SelectionMode = flexSelectionByRow
    .Cols = 4
    .Rows = 1
    .TextMatrix(0, 0) = "ID"
    .TextMatrix(0, 1) = "Name"
    .TextMatrix(0, 2) = "Department"
    .TextMatrix(0, 3) = "Salary"
End With

三、刪除功能實現

3.1 單行刪除實現

Private Sub cmdDelete_Click()
    If MSHFlexGrid1.RowSel < 1 Then Exit Sub
    
    Dim selectedID As Long
    selectedID = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 0)
    
    On Error GoTo ErrorHandler
    conn.Execute "DELETE FROM Employees WHERE ID=" & selectedID
    
    ' 刷新數據
    rs.Requery
    MSHFlexGrid1.Refresh
    
    MsgBox "刪除成功!", vbInformation
    Exit Sub
    
ErrorHandler:
    MsgBox "刪除失敗: " & Err.Description, vbCritical
End Sub

3.2 多行刪除實現

Private Sub cmdMultiDelete_Click()
    If MSHFlexGrid1.Rows <= 1 Then Exit Sub
    
    Dim i As Integer
    Dim sql As String
    Dim deletedCount As Integer
    
    On Error GoTo ErrorHandler
    conn.BeginTrans
    
    For i = MSHFlexGrid1.Rows - 1 To 1 Step -1
        If MSHFlexGrid1.RowSelected(i) Then
            sql = "DELETE FROM Employees WHERE ID=" & MSHFlexGrid1.TextMatrix(i, 0)
            conn.Execute sql
            deletedCount = deletedCount + 1
        End If
    Next i
    
    conn.CommitTrans
    rs.Requery
    MSHFlexGrid1.Refresh
    
    MsgBox "成功刪除 " & deletedCount & " 條記錄", vbInformation
    Exit Sub
    
ErrorHandler:
    conn.RollbackTrans
    MsgBox "刪除過程中出錯: " & Err.Description, vbCritical
End Sub

四、高級功能實現

4.1 刪除前確認

Private Sub cmdSafeDelete_Click()
    If MSHFlexGrid1.RowSel < 1 Then Exit Sub
    
    Dim response As VbMsgBoxResult
    response = MsgBox("確定要刪除選中的記錄嗎?", vbQuestion + vbYesNo)
    
    If response = vbYes Then
        ' 執行刪除代碼...
    End If
End Sub

4.2 事務處理增強

Private Sub DeleteWithTransaction(ids() As Long)
    On Error GoTo ErrorHandler
    Dim i As Integer
    
    conn.BeginTrans
    For i = LBound(ids) To UBound(ids)
        conn.Execute "DELETE FROM Employees WHERE ID=" & ids(i)
    Next
    
    conn.CommitTrans
    Exit Sub
    
ErrorHandler:
    conn.RollbackTrans
    Err.Raise Err.Number, , "事務處理失敗: " & Err.Description
End Sub

五、錯誤處理與調試

5.1 常見錯誤處理

5.2 調試技巧

1. 使用立即窗口輸出SQL語句：

Debug.Print "DELETE FROM Employees WHERE ID=" & selectedID

1. 啟用錯誤捕獲：

On Error Resume Next
' 操作代碼
If Err.Number <> 0 Then Debug.Print Err.Description

六、性能優化建議

1. 批量刪除優化：

' 使用IN語句替代循環刪除
Dim idList As String
idList = "1,3,5,7" ' 動態生成ID列表
conn.Execute "DELETE FROM Employees WHERE ID IN (" & idList & ")"

1. 分頁加載技術：

Const PAGE_SIZE As Integer = 50
rs.Open "SELECT TOP " & PAGE_SIZE & " * FROM Employees", conn

1. 異步刪除處理：

' 使用ADO異步執行
conn.Execute "DELETE...", , adAsyncExecute

七、完整示例代碼

' 模塊級變量
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
    ' 初始化連接
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\EmployeeDB.mdb"
    
    ' 綁定數據
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM Employees", conn, adOpenDynamic, adLockOptimistic
    Set MSHFlexGrid1.DataSource = rs
    
    ' 網格設置
    With MSHFlexGrid1
        .ColWidth(0) = 800
        .ColWidth(1) = 2000
        .ColAlignment(3) = flexAlignRightCenter
    End With
End Sub

Private Sub cmdDeleteSelected_Click()
    ' 完整刪除實現
    If MSHFlexGrid1.RowSel < 1 Then Exit Sub
    
    On Error GoTo ErrorHandler
    Dim selectedID As Long
    selectedID = CLng(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 0))
    
    If MsgBox("確定刪除ID為" & selectedID & "的記錄嗎?", vbQuestion + vbYesNo) = vbYes Then
        conn.Execute "DELETE FROM Employees WHERE ID=" & selectedID
        rs.Requery
        MsgBox "刪除完成", vbInformation
    End If
    Exit Sub
    
ErrorHandler:
    MsgBox "錯誤 " & Err.Number & ": " & Err.Description, vbCritical
End Sub

Private Sub Form_Unload(Cancel As Integer)
    ' 清理資源
    If rs.State = adStateOpen Then rs.Close
    If conn.State = adStateOpen Then conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

結語

通過本文的詳細介紹，讀者應該已經掌握了使用MSHFlexGrid控件聯動數據庫進行刪除操作的全套方法。關鍵點包括： 1. 正確的ADO連接配置 2. 靈活的網格操作 3. 完善的錯誤處理機制 4. 性能優化技巧

在實際項目中，建議根據具體需求進行適當調整，并始終注意數據安全性和操作確認機制。

注意：本文示例基于VB6+Access環境，其他數據庫可能需要調整連接字符串和SQL語法。 “`