asp_databases thread: ODBC Drivers error '80040e10' Too few parameters.
Message #1 by "Betty Harris" <psyche@o...> on Sun, 27 May 2001 21:57:54
|
|
Hiya,
I have a nested query that runs fine when pasted into Access (from the
output of a response.write statement on my asp page)... however it's
throwing the following error when I attempt to execute it from the asp
page using a DSNless connection:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8.
Has anyone experienced anything like this?
Do you have any ideas for troubleshooting this?
Thanks,
Betty
The query follows:
SELECT iif(t1.age < 20, "< 20", iif(t1.age > 19 and t1.age < 30, "20 to
29", iif(t1.age > 29 and t1.age < 40, "30 to 39", iif(t1.age > 39 and
t1.age < 50, "40 to 49", iif(t1.age > 49 and t1.age < 60, "50 to 59", iif
(t1.age > 59 and t1.age < 70, "60 to 69", iif(t1.age > 69, "70+","Age
Missing"))))))) as lbl, count(t1.age) as N, Avg(t1.Total) AS ATotal, Avg
(t1.Insight) AS AInsight, Avg(t1.Independence) AS AIndependence, Avg
(t1.Relationships) AS ARelationships, Avg(t1.Initiative) AS AInitiative,
Avg(t1.Creativity) AS ACreativity, Avg(t1.Morality) AS AMorality, Avg
(t1.gGeneral) AS AGeneral, Avg(t1.Sensing) AS Asensing, Avg(t1.Knowing) AS
AKnowing, Avg(t1.Understanding) AS AUnderstanding, Avg(t1.Separating) AS
ASeparating, Avg(t1.Distancing) AS ADistancing, Avg(t1.Recruiting) AS
ARecruiting, Avg(t1.Attaching) AS AAttaching, Avg(t1.ProblemSolving) AS
AProblemSolving, Avg(t1.Generating) AS AGenerating, Avg
(t1.DivergentThinking) AS ADivergentThinking, Avg(t1.Creating) AS
ACreating, Avg(t1.Humor) AS AHumor, Avg(t1.judging) AS AJudging, Avg
(t1.Serving) AS AServing, Avg(t1.Persistence) AS APersistence, Avg
(t1.Flexibility) AS AFlexibility FROM (SELECT ((Insight*50)+
(Independence*50)+(Relationships*50)+(Initiative*50)+(Creativity*50)+
(Morality*60)+(gGeneral*50))/360 AS Total,
(RAS01+RAS02+RAS03+RAS04+RAS05+RAS06+RAS07+RAS08+RAS09+RAS10)/50 AS
Insight, (RAS11+RAS12+RAS13+RAS14+RAS15+RAS16+RAS17+RAS18+RAS19+RAS20)/50
AS Independence,
(RAS21+RAS22+RAS23+RAS24+RAS25+RAS26+RAS27+RAS28+RAS29+RAS30)/50 AS
Relationships,
(RAS31+RAS32+RAS33+RAS34+RAS35+RAS36+RAS37+RAS38+RAS39+RAS40)/50 AS
Initiative,
(RAS41+RAS42+RAS43+RAS44+RAS45+RAS46+RAS47+RAS48+RAS49+RAS50)/50 AS
Creativity,
(RAS51+RAS52+RAS53+RAS54+RAS55+RAS56+RAS57+RAS58+RAS59+RAS60+RAS61+RAS62)/6
0 AS Morality,
(RAS63+RAS64+RAS65+RAS66+RAS67+RAS68+RAS69+RAS70+RAS71+RAS72)/50 AS
gGeneral, (RAS01+RAS04+RAS05+RAS08)/20 AS Sensing, (RAS02+RAS03+RAS06)/15
AS Knowing, (RAS07+RAS09+RAS10)/15 AS Understanding,
(RAS11+RAS13+RAS15+RAS19)/20 AS Separating,
(RAS12+RAS14+RAS16+RAS17+RAS18+RAS20)/30 AS Distancing,
(RAS22+RAS23+RAS24+RAS25+RAS27+RAS28)/30 AS Recruiting,
(RAS21+RAS26+RAS29+RAS30)/20 AS Attaching,
(RAS31+RAS32+RAS33+RAS35+RAS37)/25 AS ProblemSolving,
(RAS34+RAS36+RAS38+RAS39+RAS40)/25 AS Generating,
(RAS42+RAS43+RAS46+RAS50)/20 AS DivergentThinking, (RAS41+RAS44+RAS45)/15
AS Creating, (RAS47+RAS48+RAS49)/15 AS Humor,
(RAS51+RAS52+RAS53+RAS56+RAS57+RAS58+RAS59+RAS60)/40 AS judging,
(RAS54+RAS55+RAS61+RAS62)/20 AS Serving,
(RAS63+RAS64+RAS65+RAS66+RAS68)/25 AS Persistence,
(RAS67+RAS69+RAS70+RAS71+RAS72)/25 AS Flexibility, age FROM RAS ) as t1
group by iif(t1.age < 20, "< 20", iif(t1.age > 19 and t1.age < 30, "20 to
29", iif(t1.age > 29 and t1.age < 40, "30 to 39", iif(t1.age > 39 and
t1.age < 50, "40 to 49", iif(t1.age > 49 and t1.age < 60, "50 to 59", iif
(t1.age > 59 and t1.age < 70, "60 to 69", iif(t1.age > 69, "70+","Age
Missing"))))))) ;
Message #2 by =?iso-8859-1?Q?H=E5kan_Frennesson?= <hakan@c...> on Mon, 28 May 2001 09:37:32 +0200
|
|
Hi!
I know that for a fact Access sometimes throws in a few extra parenthesis
(maybe at the end), the problem is just to find where....nasty query you
have there ;-)
Hakan
-----Original Message-----
From: Betty Harris [mailto:psyche@o...]
Sent: den 27 maj 2001 21:58
To: ASP Databases
Subject: [asp_databases] ODBC Drivers error '80040e10' Too few
parameters.
Hiya,
I have a nested query that runs fine when pasted into Access (from the
output of a response.write statement on my asp page)... however it's
throwing the following error when I attempt to execute it from the asp
page using a DSNless connection:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8.
Has anyone experienced anything like this?
Do you have any ideas for troubleshooting this?
Thanks,
Betty
The query follows:
SELECT iif(t1.age < 20, "< 20", iif(t1.age > 19 and t1.age < 30, "20 to
29", iif(t1.age > 29 and t1.age < 40, "30 to 39", iif(t1.age > 39 and
t1.age < 50, "40 to 49", iif(t1.age > 49 and t1.age < 60, "50 to 59", iif
(t1.age > 59 and t1.age < 70, "60 to 69", iif(t1.age > 69, "70+","Age
Missing"))))))) as lbl, count(t1.age) as N, Avg(t1.Total) AS ATotal, Avg
(t1.Insight) AS AInsight, Avg(t1.Independence) AS AIndependence, Avg
(t1.Relationships) AS ARelationships, Avg(t1.Initiative) AS AInitiative,
Avg(t1.Creativity) AS ACreativity, Avg(t1.Morality) AS AMorality, Avg
(t1.gGeneral) AS AGeneral, Avg(t1.Sensing) AS Asensing, Avg(t1.Knowing) AS
AKnowing, Avg(t1.Understanding) AS AUnderstanding, Avg(t1.Separating) AS
ASeparating, Avg(t1.Distancing) AS ADistancing, Avg(t1.Recruiting) AS
ARecruiting, Avg(t1.Attaching) AS AAttaching, Avg(t1.ProblemSolving) AS
AProblemSolving, Avg(t1.Generating) AS AGenerating, Avg
(t1.DivergentThinking) AS ADivergentThinking, Avg(t1.Creating) AS
ACreating, Avg(t1.Humor) AS AHumor, Avg(t1.judging) AS AJudging, Avg
(t1.Serving) AS AServing, Avg(t1.Persistence) AS APersistence, Avg
(t1.Flexibility) AS AFlexibility FROM (SELECT ((Insight*50)+
(Independence*50)+(Relationships*50)+(Initiative*50)+(Creativity*50)+
(Morality*60)+(gGeneral*50))/360 AS Total,
(RAS01+RAS02+RAS03+RAS04+RAS05+RAS06+RAS07+RAS08+RAS09+RAS10)/50 AS
Insight, (RAS11+RAS12+RAS13+RAS14+RAS15+RAS16+RAS17+RAS18+RAS19+RAS20)/50
AS Independence,
(RAS21+RAS22+RAS23+RAS24+RAS25+RAS26+RAS27+RAS28+RAS29+RAS30)/50 AS
Relationships,
(RAS31+RAS32+RAS33+RAS34+RAS35+RAS36+RAS37+RAS38+RAS39+RAS40)/50 AS
Initiative,
(RAS41+RAS42+RAS43+RAS44+RAS45+RAS46+RAS47+RAS48+RAS49+RAS50)/50 AS
Creativity,
(RAS51+RAS52+RAS53+RAS54+RAS55+RAS56+RAS57+RAS58+RAS59+RAS60+RAS61+RAS62)/6
0 AS Morality,
(RAS63+RAS64+RAS65+RAS66+RAS67+RAS68+RAS69+RAS70+RAS71+RAS72)/50 AS
gGeneral, (RAS01+RAS04+RAS05+RAS08)/20 AS Sensing, (RAS02+RAS03+RAS06)/15
AS Knowing, (RAS07+RAS09+RAS10)/15 AS Understanding,
(RAS11+RAS13+RAS15+RAS19)/20 AS Separating,
(RAS12+RAS14+RAS16+RAS17+RAS18+RAS20)/30 AS Distancing,
(RAS22+RAS23+RAS24+RAS25+RAS27+RAS28)/30 AS Recruiting,
(RAS21+RAS26+RAS29+RAS30)/20 AS Attaching,
(RAS31+RAS32+RAS33+RAS35+RAS37)/25 AS ProblemSolving,
(RAS34+RAS36+RAS38+RAS39+RAS40)/25 AS Generating,
(RAS42+RAS43+RAS46+RAS50)/20 AS DivergentThinking, (RAS41+RAS44+RAS45)/15
AS Creating, (RAS47+RAS48+RAS49)/15 AS Humor,
(RAS51+RAS52+RAS53+RAS56+RAS57+RAS58+RAS59+RAS60)/40 AS judging,
(RAS54+RAS55+RAS61+RAS62)/20 AS Serving,
(RAS63+RAS64+RAS65+RAS66+RAS68)/25 AS Persistence,
(RAS67+RAS69+RAS70+RAS71+RAS72)/25 AS Flexibility, age FROM RAS ) as t1
group by iif(t1.age < 20, "< 20", iif(t1.age > 19 and t1.age < 30, "20 to
29", iif(t1.age > 29 and t1.age < 40, "30 to 39", iif(t1.age > 39 and
t1.age < 50, "40 to 49", iif(t1.age > 49 and t1.age < 60, "50 to 59", iif
(t1.age > 59 and t1.age < 70, "60 to 69", iif(t1.age > 69, "70+","Age
Missing"))))))) ;
Message #3 by "Betty Harris" <psyche@o...> on Tue, 29 May 2001 09:26:01 -0500
|
|
Hi,
Hakan wrote:
> I know that for a fact Access sometimes throws in a few extra parenthesis
> (maybe at the end), the problem is just to find where....nasty query you
> have there ;-)
*chuckle* Yes, it's a long one and I wrote it in a text editor. The thing
is... it works when run from Access which makes me think that the query is
syntatically happy. I'm wondering if the ADO software on the server has
limitations on query length and is not passing the entire query to the
db...tho I tried moving the IIF statements to the source query and then
referenced it in the group by in the outer query to cut down on the query
length... again it ran in Access but not via the asp page. I'm almost
tempted to write an age category field to the main data table when I store
the record to get around the problem query for the moment. That's not an
optimal solution tho...
Out for now,
Betty
|
View Cart
