-
Apr 22nd, 2021, 10:23 AM
#1
Thread Starter
Lively Member
[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?
-
Apr 22nd, 2021, 10:32 AM
#2
Re: error sql query too complex
I would use a BETWEEN clause ( WHERE ID BETWEEN (X and Y) )
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 22nd, 2021, 10:35 AM
#3
Thread Starter
Lively Member
Re: error sql query too complex
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
-
Apr 22nd, 2021, 10:54 AM
#4
Re: error sql query too complex
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 ?
Last edited by ChrisE; Apr 22nd, 2021 at 11:02 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Apr 22nd, 2021, 10:54 AM
#5
Re: error sql query too complex
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.
My usual boring signature: Nothing
-
Apr 22nd, 2021, 10:55 AM
#6
Re: error sql query too complex
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.
My usual boring signature: Nothing
-
Apr 22nd, 2021, 11:00 AM
#7
Re: error sql query too complex
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
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Apr 22nd, 2021, 11:06 AM
#8
Thread Starter
Lively Member
Re: error sql query too complex
Originally Posted by Shaggy Hiker
Still returned the same error:
Code:
sSQLMissing = "SELECT * FROM Archief WHERE Id In (" & sql & ") ORDER BY [Id];"
-
Apr 22nd, 2021, 11:15 AM
#9
Re: error sql query too complex
What is in the variable sql when it happens?
What database system are you running this against?
-
Apr 22nd, 2021, 11:21 AM
#10
Thread Starter
Lively Member
Re: error sql query too complex
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
-
Apr 22nd, 2021, 11:23 AM
#11
Re: error sql query too complex
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
-
Apr 22nd, 2021, 11:37 AM
#12
Re: error sql query too complex
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.
-
Apr 22nd, 2021, 12:00 PM
#13
Thread Starter
Lively Member
Re: error sql query too complex
I get it. Now its working fine.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|