使用另一数据表列筛选行：VB.NET匹配住户与未结清投诉代码问题排查

原有代码核心错误点 #

• 逻辑执行错误：最终计数调用的是查询Household表的command对象，完全未执行投诉表查询的cmd1，导致判断逻辑完全失效
• LIKE参数语法错误：SQL中写的'% @lastname %'会将@lastname识别为普通字符串而非参数，无法匹配实际姓名
• 逻辑运算优先级错误：未加括号的情况下，AND优先级高于OR，原有SQL实际执行逻辑为「姓氏匹配 或者 （名字匹配且投诉未结案）」，不符合需求
• 常量语法错误：remarks=UNSETTLED未加单引号，会被数据库识别为字段名而非字符串值，引发语法错误
• 冗余操作：姓名值已经可以从DataGridView直接获取，不需要额外查询Household表
• 方法误用：SELECT查询调用ExecuteNonQuery()没有任何作用，该方法仅用于增删改操作

修正后代码 #

Dim i As Integer = DataGridView1.CurrentRow.Index
' 直接从表格取姓名
Dim lastName = DataGridView1.Item(1, i).Value?.ToString()
Dim firstName = DataGridView1.Item(2, i).Value?.ToString()
Dim rowcount As Integer = 0

Try
    If con.State = ConnectionState.Closed Then
        con.Open()
    End If

    ' 修正SQL：加括号调整逻辑优先级，参数拼接%，UNSETTLED加单引号
    Dim cmd1 As New OleDbCommand("SELECT count(*) FROM Complaint WHERE (respondents LIKE '%' + @lastname + '%' OR respondents LIKE '%' + @firstname + '%') AND remarks='UNSETTLED'", con)
    With cmd1
        .Parameters.Add("@lastname", OleDb.OleDbType.VarChar).Value = If(lastName, DBNull.Value)
        .Parameters.Add("@firstname", OleDb.OleDbType.VarChar).Value = If(firstName, DBNull.Value)
    End With

    ' 执行投诉表查询获取计数
    rowcount = Convert.ToInt32(cmd1.ExecuteScalar())

    ' 判定结果
    If rowcount >= 1 Then
        BrgyclearanceWithRecords.Label17.ForeColor = Color.Red
        BrgyclearanceWithRecords.Label17.Text = "该住户姓名匹配到未结案的投诉记录！"
    Else
        BrgyclearanceWithRecords.Label17.ForeColor = Color.Green
        BrgyclearanceWithRecords.Label17.Text = "该住户姓名未匹配到未结案的投诉记录！"
    End If

    con.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
    ' 异常时兜底关闭连接
    If con.State = ConnectionState.Open Then con.Close()
End Try

BrgyclearanceWithRecords.Show()
BrgyclearanceWithRecords.BringToFront()

内容的提问来源于stack exchange，提问作者rg64