Problem with Query

Report · Nov 06, 2008

Hi
I have the following queiry
<cfquery name="searchall" datasource="Ciasa" dbtype="ODBC">
SELECT cd.studentID,
cd.caseworkerid,
cd.collegeid,
cd.studentlastname,
cd.studentfirstname,
cd.staddress,
cw.casworkerfirstname,
cw.casworkerlaststname,
cw.caseworkerid,
b.collegename,
b.collegeid
FROM students cd, caseworker cw, college b
WHERE studentlastname like '#choose#%'
AND cw.caseworkerid = cd.caseworkerid
AND cd.collegeid = b.collegeid
ORDER BY cd.studentlastname
</cfquery>

It is displaying the error
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The error occurred in C:\Inetpub\wwwroot\CFDOCS\ciasa\oct08\login\staff\search1.cfm: line 46

44 : b.collegeid
45 : FROM students cd, caseworker cw, college b
46 : WHERE studentlastname like '#choose#%'
47 : AND cw.caseworkerid = cd.caseworkerid
48 : AND cd.collegeid = b.collegeid

I have used this code before and it works fine but i cannot see what the problem is. Please help me to find the problem

Thanks

Report · Nov 06, 2008

Have you passed this parameter to the page?

WHERE studentlastname like '#choose#%'

--
Ken Ford
Adobe Community Expert Dreamweaver/ColdFusion
Adobe Certified Expert - Dreamweaver CS3
Adobe Certified Expert - ColdFusion 8
Fordwebs, LLC
http://www.fordwebs.com
http://www.cfnoob.com

"cybertek23" <webforumsuser@macromedia.com> wrote in message
news:geur58$e4f$1@forums.macromedia.com...
> Hi
> I have the following queiry
> <cfquery name="searchall" datasource="Ciasa" dbtype="ODBC">
> SELECT cd.studentID,
> cd.caseworkerid,
> cd.collegeid,
> cd.studentlastname,
> cd.studentfirstname,
> cd.staddress,
> cw.casworkerfirstname,
> cw.casworkerlaststname,
> cw.caseworkerid,
> b.collegename,
> b.collegeid
> FROM students cd, caseworker cw, college b
> WHERE studentlastname like '#choose#%'
> AND cw.caseworkerid = cd.caseworkerid
> AND cd.collegeid = b.collegeid
> ORDER BY cd.studentlastname
> </cfquery>
>
> It is displaying the error
> [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
> Microsoft
> Access Driver] Too few parameters. Expected 1.
>
> The error occurred in
> C:\Inetpub\wwwroot\CFDOCS\ciasa\oct08\login\staff\search1.cfm: line 46
>
> 44 : b.collegeid
> 45 : FROM students cd, caseworker cw, college b
> 46 : WHERE studentlastname like '#choose#%'
> 47 : AND cw.caseworkerid = cd.caseworkerid
> 48 : AND cd.collegeid = b.collegeid
>
>
> I have used this code before and it works fine but i cannot see what the
> problem is. Please help me to find the problem
>
> Thanks
>
>

Report · Nov 06, 2008

Your error message should include the sql being sent to access. Post look at it. What is the value of the choose variable?

Report · Nov 06, 2008

Yeah. There is a page called search.cfm. In here they will type in the student lastname they are looking for. It then goes to the page search1.cfm which has the query in it.

Is this what you wanted
SQLSTATE 07002
SQL SELECT cd.studentID, cd.caseworkerid, cd.collegeid, cd.studentlastname, cd.studentfirstname, cd.staddress, cw.casworkerfirstname, cw.casworkerlaststname, cw.caseworkerid, b.collegename, b.collegeid FROM students cd, caseworker cw, college b WHERE cd.collegeid = b.collegeid AND cw.caseworkerid = cd.caseworkerid AND studentlastname like 'g%' ORDER BY cd.studentlastname
VENDORERRORCODE -3010
DATASOURCE Ciasa

Report · Nov 06, 2008

You are on the right track. The first thing to try is to copy and paste the sql into access and see if it works there. If it works, there is some sort of driver problem.

If it doesn't, edit your cfquery tag so it looks like this:
SELECT cd.studentID,
cd.caseworkerid
,cd.collegeid
,cd.studentlastname
etc

FROM students cd, caseworker cw, college b
WHERE cw.caseworkerid = cd.caseworkerid
AND cd.collegeid = b.collegeid
AND studentlastname like '#choose#%'
ORDER BY cd.studentlastname

Then comment out as much as you can and run it. If it runs, uncomment the query line by line until it crashes. Then you'll know what line it is.

Report · Nov 14, 2008

This might be too simple an answer but is it because normally this '#choose#%' should read as '%#choose#%' .

Report · Nov 18, 2008

I'm presuming it doesn't matter but you do not qualify studentlastname in the like clause e.g. cd.studentlastname

I also notice you have cw.caseworkerid with case as the beginning and
cw.casworkerfirstname, cw.casworkerlaststname starting with only cas
and PROBABLY THE BUG

cw.casworkerlaststname with lastname spelled as laststname