[RESOLVED] error sql query too complex

I am trying to run a query which can be very long. For example:

SELECT * FROM Archief WHERE Id=1 Or Id=2 Or Id=3 Or Id=4 Or Id=5 Or Id=6 Or Id=7 Or Id=8 Or Id=9 Or Id=10 Or Id=11 Or Id=12 Or Id=13 Or Id=14 Or Id=15 Or Id=16 Or Id=17 Or Id=18 Or Id=19 Or Id=20 Or Id=21 Or Id=22 Or Id=23 Or Id=24 Or Id=25 Or Id=26 Or Id=27 Or Id=28 Or Id=29 Or Id=30 Or Id=31 Or Id=32 Or Id=33 Or Id=34 Or Id=35 Or Id=36 Or Id=37 Or Id=38 Or Id=39 Or Id=40 Or Id=41 Or Id=42 Or Id=43 Or Id=44 Or Id=45 Or Id=46 Or Id=47 Or Id=48 Or Id=49 Or Id=50 Or Id=51 Or Id=52 Or Id=53 Or Id=54 Or Id=55 Or Id=56 Or Id=57 Or Id=58 Or Id=59 Or Id=60 Or Id=61 Or Id=62 Or Id=63 Or Id=64 Or Id=65 Or Id=66 Or Id=67 Or Id=68 Or Id=69 Or Id=70 Or Id=71 Or Id=72 Or Id=73 Or Id=74 Or Id=75 Or Id=76 Or Id=77 Or Id=78 Or Id=79 Or Id=80 Or Id=81 Or Id=82 Or Id=83 Or Id=84 Or Id=85 Or Id=86 Or Id=87 Or Id=88 Or Id=89 Or Id=90 Or Id=91 Or Id=92 Or Id=93 Or Id=94 Or Id=95 Or Id=96 Or Id=97 Or Id=98 Or Id=99 Or Id=100 Or Id=101 Or Id=102 Or Id=103 Or Id=104 Or Id=105 Or Id=106 Or Id=107 Or Id=108 Or Id=109 Or Id=110 Or Id=111 Or Id=112 Or Id=113 Or Id=114 ORDER BY [Id];

When I try to load it in a datatable I run into an error:

System.Data.OleDb.OleDbException: 'The query is too complex

Code:

---

Public Function GetMyTable(strSQL As String) As DataTable
        ' Create new DataTable instance.
        Dim table As New DataTable
        Dim cmd As New OleDbCommand(strSQL, con)
        Using dr As OleDbDataReader = cmd.ExecuteReader
            table.Load(dr)
        End Using

        Return table
End Function

---

Is the query really too long or should I create another object to load it?

I would use a BETWEEN clause ( WHERE ID BETWEEN (X and Y) )

Quote:

---

Originally Posted by GaryMazzone

I would use a BETWEEN clause ( WHERE ID BETWEEN (X and Y) )

---

That's not possible in my case since the records are selected based on some criteria:

Code:

---

          If Not File.Exists(sArchive & sDoc) = True Then
                iNum += 1
                sql = sql & "Id=" & row.Item("Id") & " Or "
            End If

---

well using "Select *" is never a good idea, you are selecting all the field in the Table

but here you go,
see if this works, I didn't test it

Code:

---

SELECT .*, Archief.id, Archief.id FROM Archief
          WHERE (((Archief.id)>=1 And (Archief.id)<115) AND ((Archief.id)=3));

---

if there is a Id = 3 it will show that record
if not your query will return nothing


EDIT:
what is this for, something with files ?

Use IN:

https://www.w3schools.com/Sql/sql_in.asp

Build up your array of values rather than concatenating a bunch of OR statements together. It will be faster and FAR more manageable.

Quote:

---

Originally Posted by ChrisE

well using "Select *" is never a good idea, you are selecting all the field in the Table

---

I did a bit of testing on that, and it isn't always going to be true. If you are getting a bunch of fields that you don't need, then that is a waste, but if you need all the fields, then SELECT * isn't going to perform worse than spelling out all the fields.

Quote:

---

Originally Posted by Shaggy Hiker

I did a bit of testing on that, and it isn't always going to be true. If you are getting a bunch of fields that you don't need, then that is a waste, but if you need all the fields, then SELECT * isn't going to perform worse than spelling out all the fields.

---

I never really tested it, I just write out the fields I need

Quote:

---

Originally Posted by Shaggy Hiker

Use IN:

https://www.w3schools.com/Sql/sql_in.asp

Build up your array of values rather than concatenating a bunch of OR statements together. It will be faster and FAR more manageable.

---

Still returned the same error:

Code:

---

sSQLMissing = "SELECT * FROM Archief WHERE Id In (" & sql & ") ORDER BY [Id];"

---

What is in the variable sql when it happens?

What database system are you running this against?

Quote:

---

Originally Posted by si_the_geek

What is in the variable sql when it happens?

What database system are you running this against?

---

MS Access

SELECT * FROM Archief WHERE Id=1 Or Id=2 Or Id=3 Or Id=4 Or Id=5 Or Id=6 Or Id=7 Or Id=8 Or Id=9 Or Id=10 Or Id=11 Or Id=12 Or Id=13 Or Id=14 Or Id=15 Or Id=16 Or Id=17 Or Id=18 Or Id=19 Or Id=20 Or Id=21 Or Id=22 Or Id=23 Or Id=24 Or Id=25 Or Id=26 Or Id=27 Or Id=28 Or Id=29 Or Id=30 Or Id=31 Or Id=32 Or Id=33 Or Id=34 Or Id=35 Or Id=36 Or Id=37 Or Id=38 Or Id=39 Or Id=40 Or Id=41 Or Id=42 Or Id=43 Or Id=44 Or Id=45 Or Id=46 Or Id=47 Or Id=48 Or Id=49 Or Id=50 Or Id=51 Or Id=52 Or Id=53 Or Id=54 Or Id=55 Or Id=56 Or Id=57 Or Id=58 Or Id=59 Or Id=60 Or Id=61 Or Id=62 Or Id=63 Or Id=64 Or Id=65 Or Id=66 Or Id=67 Or Id=68 Or Id=69 Or Id=70 Or Id=71 Or Id=72 Or Id=73 Or Id=74 Or Id=75 Or Id=76 Or Id=77 Or Id=78 Or Id=79 Or Id=80 Or Id=81 Or Id=82 Or Id=83 Or Id=84 Or Id=85 Or Id=86 Or Id=87 Or Id=88 Or Id=89 Or Id=90 Or Id=91 Or Id=92 Or Id=93 Or Id=94 Or Id=95 Or Id=96 Or Id=97 Or Id=98 Or Id=99 Or Id=100 Or Id=101 Or Id=102 Or Id=103 Or Id=104 Or Id=105 Or Id=106 Or Id=107 Or Id=108 Or Id=109 Or Id=110 Or Id=111 Or Id=112 Or Id=113 Or Id=114 ORDER BY [Id];
...... and so on

Quote:

---

Originally Posted by Shaggy Hiker

I did a bit of testing on that, and it isn't always going to be true. If you are getting a bunch of fields that you don't need, then that is a waste, but if you need all the fields, then SELECT * isn't going to perform worse than spelling out all the fields.

---

The danger though is if your code depends on fields being in a particular position. By using * you can't be guaranteed that fields will always be in the same position. Granted, you shouldn't be using positional indexes in code in the first place, but if you then throw it into a grid... you run the risk of columns jumping around unexpectedly.


-tg

Quote:

---

Originally Posted by clausowitz

MS Access

SELECT * FROM Archief WHERE Id=1 Or Id=2 Or Id=3 Or Id=4 Or Id=5 Or Id=6 Or Id=7 Or Id=8 Or Id=9 Or Id=10 Or Id=11 Or Id=12 Or Id=13 Or Id=14 Or Id=15 Or Id=16 Or Id=17 Or Id=18 Or Id=19 Or Id=20 Or Id=21 Or Id=22 Or Id=23 Or Id=24 Or Id=25 Or Id=26 Or Id=27 Or Id=28 Or Id=29 Or Id=30 Or Id=31 Or Id=32 Or Id=33 Or Id=34 Or Id=35 Or Id=36 Or Id=37 Or Id=38 Or Id=39 Or Id=40 Or Id=41 Or Id=42 Or Id=43 Or Id=44 Or Id=45 Or Id=46 Or Id=47 Or Id=48 Or Id=49 Or Id=50 Or Id=51 Or Id=52 Or Id=53 Or Id=54 Or Id=55 Or Id=56 Or Id=57 Or Id=58 Or Id=59 Or Id=60 Or Id=61 Or Id=62 Or Id=63 Or Id=64 Or Id=65 Or Id=66 Or Id=67 Or Id=68 Or Id=69 Or Id=70 Or Id=71 Or Id=72 Or Id=73 Or Id=74 Or Id=75 Or Id=76 Or Id=77 Or Id=78 Or Id=79 Or Id=80 Or Id=81 Or Id=82 Or Id=83 Or Id=84 Or Id=85 Or Id=86 Or Id=87 Or Id=88 Or Id=89 Or Id=90 Or Id=91 Or Id=92 Or Id=93 Or Id=94 Or Id=95 Or Id=96 Or Id=97 Or Id=98 Or Id=99 Or Id=100 Or Id=101 Or Id=102 Or Id=103 Or Id=104 Or Id=105 Or Id=106 Or Id=107 Or Id=108 Or Id=109 Or Id=110 Or Id=111 Or Id=112 Or Id=113 Or Id=114 ORDER BY [Id];
...... and so on

---

The suggestion to use "In" wasn't intended for you to then keep the rest of your SQL statement exactly as it was in your first post.

If you use this line:

Code:

---

sSQLMissing = "SELECT * FROM Archief WHERE Id In (" & sql & ") ORDER BY [Id];"

---

Then the contents of your "sql" variable should look like this:

Code:

---

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

---

...and so on.

I get it. Now its working fine.