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语法。 “`