VB.NET+Access+ADODB场景下避免数据库重复值的实现问题

Hey, let's work through your duplicate check issue step by step—your current code has a reversed logic for detecting duplicates, plus some other pitfalls that could cause problems. Here's what you need to do:

1. First: Add a Unique Constraint in the Database (Most Reliable Fix) #

No matter how solid your code is, database-level constraints are the best way to prevent duplicates. Open your Access database:

• Open the rrQuestionaryGroup table in Design View
• Hold Ctrl to select both ID_Questionary and ID_Group fields
• Go to the Indexes menu, create a combined index for these two fields, and check the "Unique" box
  This way, the database itself will reject any duplicate combinations, even if there's a bug in your code.

2. Fix the Button Click Logic #

Your current code checks if the query executes successfully (which it does even if no duplicates are found) and treats that as a duplicate. Instead, you need to check if the query returns any records. We'll also switch to parameterized queries to avoid SQL injection and syntax errors:

Private Sub btnAddGroup_Click(sender As Object, e As EventArgs) Handles btnAddGroup.Click
    Dim checkSql As String = "SELECT ID_Questionary FROM rrQuestionaryGroup WHERE ID_Questionary = ? AND ID_Group = ?"
    Dim rs As New ADODB.Recordset

    ' First, check for duplicates
    Using con As New ADODB.Connection
        Try
            con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users.mdb;Persist Security Info=true")
            con.CursorLocation = ADODB.CursorLocationEnum.adUseClient

            Dim cmd As New ADODB.Command With {
                .ActiveConnection = con,
                .CommandText = checkSql
            }
            ' Add parameters to avoid injection and syntax issues
            cmd.Parameters.Append(cmd.CreateParameter("qID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Val(Trim(lblIDQuestionary.Text))))
            cmd.Parameters.Append(cmd.CreateParameter("gID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Val(Trim(lblIDGroup.Text))))

            rs.Open(cmd, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)

            If Not rs.EOF Then
                ' Duplicate found
                MsgBox("Group already in this questionary")
            Else
                ' No duplicate, proceed to add
                rs.Close()

                ' Insert the new group with parameterized query
                Dim insertCmd As New ADODB.Command With {
                    .ActiveConnection = con,
                    .CommandText = "INSERT INTO rrQuestionaryGroup (ID_Questionary, ID_Group, [Order]) VALUES (?, ?, ?)"
                }
                insertCmd.Parameters.Append(insertCmd.CreateParameter("qID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Val(Trim(lblIDHoldQuestionary.Text))))
                insertCmd.Parameters.Append(insertCmd.CreateParameter("gID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Val(Trim(lblIDHoldGroup.Text))))
                insertCmd.Parameters.Append(insertCmd.CreateParameter("orderVal", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Val(Trim(lblIDHoldOrder.Text))))

                insertCmd.Execute()
                MsgBox("Group added successfully")
                ' Refresh your DataGridViews here to show the new entry
                LoadQuestionaryGroups() ' Replace with your actual refresh method
            End If
        Catch ex As Exception
            MsgBox($"Error: {ex.Message}")
        Finally
            ' Clean up resources
            If rs.State = ADODB.ObjectStateEnum.adStateOpen Then
                rs.Close()
            End If
            If con.State = ADODB.ObjectStateEnum.adStateOpen Then
                con.Close()
            End If
            rs = Nothing
        End Try
    End Using
End Sub

3. Fix Your getRS Function (If You Still Want to Use It) #

Your original getRS has issues with connection leaks, error handling, and parameter passing. Here's a corrected version:

Public Function getRS(ByVal sql As String, ByRef rs As ADODB.Recordset, ByVal isReadOnly As Boolean, ByRef errorMsg As String) As Boolean
    errorMsg = ""
    Dim con As New ADODB.Connection
    Dim lockType As ADODB.LockTypeEnum = If(isReadOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.LockTypeEnum.adLockOptimistic)

    Try
        con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users.mdb;Persist Security Info=true")
        con.CursorLocation = ADODB.CursorLocationEnum.adUseClient

        rs.Open(sql, con, ADODB.CursorTypeEnum.adOpenDynamic, lockType)
        ' Return True if query executed successfully (even if no records)
        Return True
    Catch ex As Exception
        errorMsg = ex.Message
        ' Clean up on error
        If rs IsNot Nothing AndAlso rs.State = ADODB.ObjectStateEnum.adStateOpen Then
            rs.Close()
        End If
        If con.State = ADODB.ObjectStateEnum.adStateOpen Then
            con.Close()
        End If
        Return False
    Finally
        ' Ensure connection is closed
        If con.State = ADODB.ObjectStateEnum.adStateOpen Then
            con.Close()
        End If
        con = Nothing
    End Try
End Function

4. Key Takeaways #

• Database Constraint: This is non-negotiable—it's your last line of defense against duplicates, even if code has bugs.
• Parameterized Queries: Always use them instead of string concatenation to avoid SQL injection and syntax errors (even if you're using Val today).
• Resource Management: Always close connections and recordsets after use to prevent memory leaks.

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