Query too long ERROR

Question

0.00/5 (No votes)

See more:

My Query is

SQL

Select * from Order_Table where OrderNo 
in(1,2,3,4,5,6,7,8,9,10,11,12,13,14,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,
...
Many, many similar rows deleted to make this less of a PITA to load...
...
21063,21065,21066,21067,21069,21070,21071,21072,21073,21074,21075,21076,21077,21078,21079,21080,21081,21082,21083,21084,21085,21086,21087,21088,21089,21090,21091,21092)

it gives me error like "Query is too long" i have to delete record except this no what i have to do. please help me.... Database MS ACcess 2002-2003

[edit]Rows in HUGE query removed - was 20,000+ IDs - OriginalGriff[/edit]

Posted 15-Apr-13 22:32pm

hareshdgr8

Updated 15-Apr-13 23:34pm

OriginalGriff

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-04-15T22:49:00

Solution 1

113875 characters.

That is how long your query is.
The help file http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx[^] says that the maximum query length is "Approximately 64,000 characters" so you are exceeding this by a factor of two.

That is a spectacularly silly query! I have no idea why you think this is a good idea, but have a look at your design, because there has to be a better way than this!

Posted 15-Apr-13 22:49pm

OriginalGriff

Comments

hareshdgr8 16-Apr-13 4:57am

sir specially i have to say thank you for every time you help me and out me by problem.
sir actually i have to fire delete query included not in syntax i.e.

delete from Order_Table where OrderNo not in (Above Order No)

OriginalGriff 16-Apr-13 5:07am

You do realize that there are 20797 ids in the query string above? And the chances are that the number of ids that you have to exclude will only increase? Put it like this: out of curiosity, I loaded your query into PSPad - a good text editor, and told it to replace each ',' with a '?' so I could find out how many ids there were without writing any software. It crashed trying to do it after blocking a whole core for a minute or so. (So I wrote something to count 'em instead, which was quicker).

Even if you could get this query into SQL (or access) the amount of processing time it would take is ridiculous!

Why do you have to delete everything "not in this list"? There may be a better way to try, because you aren't going to get that to work in any reasonable time frame, even if you pass the list through as a parameter (which needs special processing at the database end, and that will take even longer to run)

Member 2707515 16-Apr-13 5:23am

dude do one thing create another table store your information on that along with order number
and than create a query e.g

Select * from Order_Table where OrderNo where order_no in (
select order_no from nextTable)

Maciej Los 16-Apr-13 5:45am

My virtual 5!

hareshdgr8 16-Apr-13 5:27am

because except this record all records are useless for me thats why i have to delete......

OriginalGriff 16-Apr-13 5:35am

So how did you generate the query? What makes a row a "keeper" instead of a "deleter"?
Because I am sure you didn't type that lot out manually...

hareshdgr8 16-Apr-13 9:00am

i just use my variable like
String strValue="";
while reader.next()
{
strvalue=strvalue & reader(1) & ","
}
delete from Order_Table where OrderNo not in(strvalue)

OriginalGriff 16-Apr-13 9:29am

A) Don't concatenate strings like that, ever! Strings are immutable, so every time you add another ID to the string, you are allocating another chunk of memory big enough to hold it. Use a StringBuilder instead!
B) So what is the query which generates the DataReader?

Maciej Los 16-Apr-13 5:44am

3 stars???
Very good answer!
+5!

Maciej Los · Answer 2 · 2013-04-15T23:53:00

Solution 2

First of all read all comments to OriginalGriff answer.

As Member 2707515 had mentioned, you can use another table to store values to future use.

SQL

SELECT t1.*
FROM Table1 AS t1 RIGHT JOIN Table2 AS t2 ON t1.OrderNo = t2.OrderNo

Another way is to use between ... and ... statement, but it would be helpful if OrderNo is continuos way.

SQL

SELECT *
FROM Table1
WHERE OrderNo BETWEEN 1 AND 21092

Posted 15-Apr-13 23:53pm

Maciej Los

Comments

hareshdgr8 16-Apr-13 8:54am

this is not a good idea because if one of the record are not in this than it would be deleted so not a good idea.......

Maciej Los 16-Apr-13 9:02am

Which idea is bad idea? You got 2 solutions in 1.

@HareshPrj, be more precisely and shortly describe your problem. I hope it will help us to understand your needs.
Use "Improve question" widget.