SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

Hi,
I run this sql in Access and it runs fine. Then I cut and paste it as a
literal constant into an ASP page in VS2003 and it returns this error..

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
1.
/Scuba/ASP/sportsearch2.asp, line 91

Can anyone point me in the right direction? A Google search suggested
that I might've spelt field names wrongly, however I have not 'typed'
anything...

SQL:
SELECT ss_ResortsT.ResortID, ss_ResortsT.ResortName,
ss_ActivitiesT.ActivityID FROM (ss_ResortsT INNER JOIN ss_ResSeasonsT
ON ss_ResortsT.ResortID = ss_ResSeasonsT.fkResortID) INNER JOIN
(ss_ResActCentresT INNER JOIN (ss_ActivitiesT INNER JOIN ss_CentActsT
ON ss_ActivitiesT.ActivityID = ss_CentActsT.fkActivityID) ON
ss_ResActCentresT.ResActCentreID = ss_CentActsT.fkResActCentreID) ON
ss_ResortsT.ResortID = ss_ResActCentresT.fkResortID WHERE
(([ActivityID]=2 And [ss_CentActsT]![CentActSkillCode] Like "*B*") )
AND
([ss_ResSeasonsT]![SeasonStartDate]<=#01/11/2005#<=[ss_ResSeasonsT]![SeasonStartDate]+[ss_ResSeasonsT]![SeasonDays])
GROUP BY ss_ResortsT.ResortID, ss_ResortsT.ResortName,
ss_ActivitiesT.ActivityID;

Thank you in advance

OJ

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

update: I have narrowed it down to this bit of sql...

WHERE
(([ActivityID]=2 And [ss_CentActsT]![CentActSkillCode] Like "*B*") )
AND
([ss_ResSeasonsT]![SeasonStartDate]<=#01/11/2005#<=[ss_ResSeasonsT]![SeasonStartDate]+[ss_ResSeasonsT]![SeasonDays])


if I remove
(([ActivityID]=2 And [ss_CentActsT]![CentActSkillCode] Like "*B*") )
AND

then the quey runs from the asp page...

Thx..
OJ

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

OJ wrote:
> update: I have narrowed it down to this bit of sql...
>
> WHERE
> (([ActivityID]=2 And [ss_CentActsT]![CentActSkillCode] Like "*B*") )
> AND
>
([ss_ResSeasonsT]![SeasonStartDate]<=#01/11/2005#<=[ss_ResSeasonsT]![SeasonS
tartDate]+[ss_ResSeasonsT]![SeasonDays])
>
>
> if I remove
> (([ActivityID]=2 And [ss_CentActsT]![CentActSkillCode] Like "*B*") )
> AND
>
> then the quey runs from the asp page...
>
You need to remove the entire expression? What if you remove either

[ActivityID]=2

or

[ss_CentActsT]![CentActSkillCode] Like "*B*"

instead of removing them both?

I'm unclear about what you are showing us here? Is this sql the result of a
response.write satatement in your vbscript code? Or are you simply copying
and pasting it from the code itself? Best practice is to assign your sql
statement to a variable:

sSQL = "Select ... "

So you can response.write it to verify it contains what you expect it to
contain in the event of errors:

Response.Write sSQL

Are you using dynamic sql? if so you should consiter using parameters,
either via saved parameter queiries:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

or via using a Command object to pass data to a sql string containing odbc
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e


I'm surprised it runs at all from ASP given the exclamation points. Are
ss_CentActsT and ss_ResSeasonsT tables in your database? f so, you should
be using a period in your sql to qualify the field names, not an exclamation
point, which is usually reserved for form and recordset objects.

The following points have nothing to do with your problem, but keep them in
mind:

When running a query using ADO from ASP, you need to use the ODBC wildcards
(% and _) instead of the jet wildcards (* and ?)

You should also supply dates in a non-ambiguous format: yyyy-mm-dd, instead
of whichever format you are using (is it mm/dd/yyyy?)

Your object names have no unusual characters, so there is no need to
surround them with brackets unless they are parameters.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

OJ wrote on 26 okt 2005 in microsoft.public.inetserver.asp.db:

> (([ActivityID]=2 And [ss_CentActsT]![CentActSkillCode] Like "*B*") )
>

! is a unary operator

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

"OJ" wrote in message
news:1130348555.674590.94040@g43g2000cwa.googlegroups.com...
> Hi,
> I run this sql in Access and it runs fine. Then I cut and paste it as a
> literal constant into an ASP page in VS2003 and it returns this error..
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
> [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
> 1.
> /Scuba/ASP/sportsearch2.asp, line 91
>
> Can anyone point me in the right direction? A Google search suggested
> that I might've spelt field names wrongly, however I have not 'typed'
> anything...
>
> SQL:
> SELECT ss_ResortsT.ResortID, ss_ResortsT.ResortName,
> ss_ActivitiesT.ActivityID FROM (ss_ResortsT INNER JOIN ss_ResSeasonsT
> ON ss_ResortsT.ResortID = ss_ResSeasonsT.fkResortID) INNER JOIN
> (ss_ResActCentresT INNER JOIN (ss_ActivitiesT INNER JOIN ss_CentActsT
> ON ss_ActivitiesT.ActivityID = ss_CentActsT.fkActivityID) ON
> ss_ResActCentresT.ResActCentreID = ss_CentActsT.fkResActCentreID) ON
> ss_ResortsT.ResortID = ss_ResActCentresT.fkResortID WHERE
> (([ActivityID]=2 And [ss_CentActsT]![CentActSkillCode] Like "*B*") )
> AND
> ([ss_ResSeasonsT]![SeasonStartDate]<=#01/11/2005#<=[ss_ResSeasonsT]![SeasonStartDate]+[ss_ResSeasonsT]![SeasonDays])
> GROUP BY ss_ResortsT.ResortID, ss_ResortsT.ResortName,
> ss_ActivitiesT.ActivityID;
>
> Thank you in advance
>
> OJ



Not sure what else might be wrong with it, but the first thing I would do
is use an OLEDB driver not ODBC. ODBC is flaky and an added complication
you can do without.

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.room-booking-software.com - Schedule rooms & equipment bookings
for your meeting/class over the web.
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
http://www.outlook-find-replace.com - Find & Replace in Emails, Contacts,
Appointments, Tasks and Notes

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

Firstly, Thanks for your prompt responses...

Let me clarify. The SQL is the result of a response.write statement
directly above...

adoRS.Open sqlFinal, adoConn, 1, 2

Which is the line of code where the error occurs. I copy/paste the sql
into Access and it returns required results. When I change the ! to .
then I still get the same error...

WHERE (([ActivityID]=2 And [ss_CentActsT].[CentActSkillCode] Like
"*B*") ) AND
([ss_ResSeasonsT].[SeasonStartDate]<=#01/11/2005#<=[ss_ResSeasonsT].[SeasonStartDate]+[ss_ResSeasonsT].[SeasonDays])


any object whose name ends in a T is a table. These are all tables in
this query. Once again, any help is much appreciated....

Thanks,
OJ

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

OJ wrote:
> ([ss_ResSeasonsT]![SeasonStartDate]<= #01/11/2005#
<=[ss_ResSeasonsT]![SeasonStartDate]+[ss_ResSeasonsT]![SeasonDays])
>
Also, I'm not sure this is doing what you think it is doing. What you are
meaning to say is:

#01/11/2005# BETWEEN ss_ResSeasonsT.SeasonStartDate AND
ss_ResSeasonsT.SeasonStartDate + ss_ResSeasonsT.SeasonDays

Some people prefer this style:
ss_ResSeasonsT.SeasonStartDate < = #01/11/2005# AND
ss_ResSeasonsT.SeasonStartDate + ss_ResSeasonsT.SeasonDays >= #01/11/2005#

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

I fear my naivity is betraying me...is that not what my statement is
saying?
I
shall try to expand.....
These elements of my SQL string will come from variables in the
VBscript of my ASP:

[ActivityID]=2 <- The 2
<=#01/11/2005#<= <- the date string
[ss_CentActsT].[CentActSkillCode] Like
"*B*") <- The B

The SQL response.write works in Access and was generated from an
original access query....I'm not sure what more to add!

Thanks,
OJ

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

OJ wrote:
> Firstly, Thanks for your prompt responses...
>
> Let me clarify. The SQL is the result of a response.write statement
> directly above...
>
> adoRS.Open sqlFinal, adoConn, 1, 2
>
> Which is the line of code where the error occurs. I copy/paste the sql
> into Access and it returns required results. When I change the ! to .
> then I still get the same error...
>
> WHERE (([ActivityID]=2 And [ss_CentActsT].[CentActSkillCode] Like
> "*B*") ) AND
>
([ss_ResSeasonsT].[SeasonStartDate]<=#01/11/2005#<=[ss_ResSeasonsT].[SeasonS
tartDate]+[ss_ResSeasonsT].[SeasonDays])
>
>
> any object whose name ends in a T is a table. These are all tables in
> this query. Once again, any help is much appreciated....
>
Given that it runs in Access, there must be a word in this query that Access
can resolve to sume object in your database, but which Jet on its own
cannot.
You did not anwer whether both ActivityID and
[ss_CentActsT].[CentActSkillCode] needed to be removed to enable the query
to be run. Are ActivityID and CentActSkillCode both fields in a database
table?

Again, you need to use %, not * when running sql via ADO: "%B%" not "*B*"

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

Bob,
Thank you. Problem solved. The problems were the the " double quotes
instead of ' single quotes and the wildcard character. Please forgive
more delayed comprehension....

Regards,
OJ

Re: SQL Runs in Access and not in ASP: 0x80040E10 Too few parameters

OJ wrote:
> I fear my naivity is betraying me...is that not what my statement is
> saying?

No. Your expression is saying the equivalent of

x <= (y <= z)

which in English is:
x is less than or equal to the result of the comparison between y and z. So
if y is less than or equal to z, then the expression becomes:

x <= true

or

x <= -1

if y is grater than z, then you get:

x <= false
or
x <= 0


> I shall try to expand.....
> These elements of my SQL string will come from variables in the
> VBscript of my ASP:

This is irrelevant to the point I am trying to make.

>
> The SQL response.write works in Access and was generated from an
> original access query....I'm not sure what more to add!
>
Do you get the results you intend to get? Do some testing with just this
comparison in your sql. I've just run some tests of my own and in my tests,
this type of comparison did not return the correct results.
I'd be surprised if you are getting your expected results. Access must be
rewriting your logic if it is returning your intended results. I do not
think Jet and ADO will be so forgiving.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.