Filter a row using a column from another data table-CodePudding

I have a (Household) table which have two columns: firstname and lastname. The second table (Complaints) also has two columns: respondents and remarks. Now im trying to check when the selected row in (household) has the same name values to the respondents column and has "UNSETTLED" value in remarks column. Then it will show household has records. If the value of respondents has a match but the remarks is = SETTLED then then the household has no records.

Where the ID in the Households table is the Primary Key and has a one to many relationship to the HouseholdID Foreign Key in the Complaints table.

I am guessing that DataGridView1 contains the Households and the first column contains the ID column.

The Using block includes the connection and the command. Both need to be disposed. End Using also closes the connection.

When you are using a literal string in the Where criteria, it needs to be surrounded by single quotes.

Since you are only retrieving a single value from the database you can use ExecuteScalar() which returns an Object, thus the CInt().

Once the connection is safetly closed and disposed with the End Using, you can check your returned value, count, and take the required actions.

Private OPConStr As String = "Your connection string."

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim count As Integer
    Dim i = DataGridView1.CurrentRow.Index
    Dim householdid = CInt(DataGridView1.Item(0, i).Value)
    Try
        Using con As New OleDbConnection(OPConStr),
            cmd As New OleDbCommand("SELECT count(*)  FROM Complaints WHERE HouseholdID = @ID AND Remarks = 'UNSETTLED';", con)
            cmd.Parameters.Add("@id", OleDbType.Integer).Value = householdid
            con.Open()
            count = CInt(cmd.ExecuteScalar())
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try
    If count >= 1 Then
        BrgyclearanceWithRecords.Label17.ForeColor = Color.Red
        BrgyclearanceWithRecords.Label17.Text = "Resident's Name has a match with an Existing unsettled complain!"
    Else
        BrgyclearanceWithRecords.Label17.ForeColor = Color.Green
        BrgyclearanceWithRecords.Label17.Text = "Resident's Name has no match with an Existing unsettled complain!"
    End If
    BrgyclearanceWithRecords.Show()
    BrgyclearanceWithRecords.BringToFront()
End Sub