Query is too complex.

Question

Hello

To delete many selected records at once:

In a loop I populate question marks: QuestionMarks = QuestionMarks + "?, "

Then:

"DELETE FROM table WHERE theID = ? AND fld IN (" + QuestionMarks + ")"

And:

For Each MyRow As DataGridViewRow In DataGridView.SelectedRows

...Parameters.AddWithValue("@fld", MyRow.Cells.Item(0).Value.ToString)

Next

For small number of records, it works, I tested on 20,000 records and got error:

Query is too complex.

Answer 1

Hi @StewartBW ,

You can try using a temporary table to store the IDs of the rows to be deleted, and then perform a single delete operation based on this temporary table.

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To avoid inserting or deleting data row-by-row, consider operating on group of rows, for example:

For Each chunk In DataGridView.SelectedRows.Cast(Of DataGridViewRow).Chunk(30)

    ' build QuestionMarks based on 'chunk.Length'
    ' ...

    For Each MyRow In chunk

        '... Parameters.AddWithValue("@fld", MyRow.Cells.Item(0).Value.ToString)

    Next

    ' execute 'DELETE FROM table WHERE theID = ? AND fld IN (" + QuestionMarks + ")"'
    ' ...
Next

Try any value instead of 30 that is accepted by Access.

(Chunk is available in .NET 7 etc.)

Answer 3

You can do this with Dapper where in the example below one condition is done but can be done with one than one condition.

The identifiers would be collected by first iterating the DataGridView SelectedRows.

public void Remove(List<int> identifiers)
{
    var statement =
        """
         DELETE FROM table 
         WHERE theID IN @Identifier
        """;
    _cn.Execute(statement, identifiers);
}

Public Sub Remove(ByVal identifiers As List(Of Integer))
	Dim statement = "DELETE FROM table WHERE theID IN @Identifier"
	_cn.Execute(statement, identifiers)
End Sub