Query is too complex.

Question

Query is too complex.

StewartBW 1,805

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)

Query is too complex.

Jiachen Li-MSFT 34,221 Reputation points Microsoft External Staff

2024-05-31T05:48:41.7633333+00:00

May I know if you have got any chance to check the answers? I am glad to help if you have any other questions.

3 answers

Your answer

Jiachen Li-MSFT 34,221 Reputation points Microsoft External Staff

2024-05-31T05:48:41.7633333+00:00

May I know if you have got any chance to check the answers? I am glad to help if you have any other questions.

Answer 1

Jiachen Li-MSFT 34,221 Microsoft External Staff

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.

StewartBW 1,805 Reputation points

2024-05-24T09:56:04.6733333+00:00

Thanks for the tip, can you please be so kind and give me some tips on the sql commands of those steps :)

Sorry for the inconvenience.

Jiachen Li-MSFT 34,221 Microsoft External Staff

Check the code example.

' Create the temporary table
Dim createTempTableQuery As String = "CREATE TABLE TempToDelete (fld LONG)"
Using cmd As New OleDbCommand(createTempTableQuery, connection)
    cmd.ExecuteNonQuery()
End Using

' Insert the selected rows into the temporary table
For Each MyRow As DataGridViewRow In DataGridView.SelectedRows
    Dim insertTempTableQuery As String = "INSERT INTO TempToDelete (fld) VALUES (@fld)"
    Using cmd As New OleDbCommand(insertTempTableQuery, connection)
        cmd.Parameters.AddWithValue("@fld", MyRow.Cells.Item(0).Value.ToString())
        cmd.ExecuteNonQuery()
    End Using
Next

' Perform the Delete Operation
Dim deleteQuery As String = "DELETE FROM table WHERE theID = @theID AND fld IN (SELECT fld FROM TempToDelete)"
Using cmd As New OleDbCommand(deleteQuery, connection)
    cmd.Parameters.AddWithValue("@theID", theIDValue)
    cmd.ExecuteNonQuery()
End Using

' Drop the Temporary Table
Dim dropTempTableQuery As String = "DROP TABLE TempToDelete"
Using cmd As New OleDbCommand(dropTempTableQuery, connection)
    cmd.ExecuteNonQuery()
End Using

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

Share via

Query is too complex.

3 answers

Your answer