Coldfusion search: Wrong number of parameters error. 2

[thunderain]

1/Coldfusion Studio 4.5a, Microsoft Access 2000 database
2/ I am trying two input two numbers from a form on previous page, get all files in a table that match either number.
3/ I get this error:
Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)


[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (9:1) to (9:59).


My Code: Tried it two ways:
First Way:
<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>
SELECT Filtran_Part_Number, Description, On_Web_Site
FROM filtran
WHERE (0=0
<cfloop list=&quot;#form.Filtran_Part_Number#&quot; index=&quot;ThisNumber&quot; delimiters=&quot;,&quot;>
AND (AMD_Part_Number LIKE &quot;%#ThisNumber#%&quot
</cfloop>)

Second Way:

ListGetAt(form.Filtran_Part_Number, 1,&quot;,&quot
ListGetAt(form.Filtran_Part_Number, 2)

<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>

SELECT Filtran_Part_Number, Description, On_Web_Site
FROM filtran
WHERE (

0=0
<cfloop list=&quot;#form.Filtran_Part_Number#&quot; index=&quot;ThisNumber&quot;>
and (AMD_Part_Number LIKE &quot;%#ThisNumber#%&quot
</cfloop>)

=======

As the error indicates, my field name in the database table is doesn't match my code. This is incorrect as they do match. Something else is causing it.

Any ideas?
Thank you

 

[tlhawkins]

Im getting a headache looking at that.

What I would try is to create a string of your query and output it to your page so you can see it. You will probably find the problem easily that way.

Chances are , you aren't getting your commas in the right places. I can't see where you are putting the commas in at all (but I might be missing something)

if you could show us the actual Query that is going to the DB we could also help find the error.

hope this helps.

 

[thunderain]

I tried what you said:
The two numbers input on previous screen:AM79C30A, AM79C978

with this output check:

<cfoutput>
#form.Filtran_Part_Number#<BR>
#ListGetAt(form.Filtran_Part_Number, 1,&quot;,&quot#<BR>
#ListGetAt(form.Filtran_Part_Number, 2)#<BR>
</cfoutput>

I get:
AM79C30A, AM79C978
AM79C30A
AM79C978

As you can see, it is splitting them up quite nicely but
with this query:

<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>

SELECT Filtran_Part_Number, Description, On_Web_Site
FROM filtran
WHERE (

0=0
<cfloop list=&quot;#form.Filtran_Part_Number#&quot; index=&quot;ThisNumber&quot;>
and (AMD_Part_Number LIKE &quot;%#ThisNumber#%&quot
</cfloop>)
</cfquery>

I still get the &quot;Wrong number of parameters&quot; error previously mentioned

(I'm a new programmer)

Thank you

 

[tlhawkins]

I'm not really following your Query,

Here is how I do that:

<cfquery name=&quot;Getlist&quot; datasource=&quot;#dsn#&quot;>
Select *
From tblname
Where fieldname IN (#Form.ListofChoices#)
</cfquery>

I don't think all of that listing and looping are necessary. Try to simplify that Query down like mine and see what you get.

If the looping is necessary, I'll rewrite it to include the aggregate &quot;LIKE&quot; and see how that works for you, but it looks like you're overworking here.

 

[Kenvase]

Substitute &quot; with ' in the LIKE part ex
(AMD_Part_Number LIKE &quot;%#ThisNumber#%&quot
(AMD_Part_Number LIKE '%#ThisNumber#%')

Kent

 

[thunderain]

I tried replacing &quot; with ' but that didn't make a difference.


I don't really understand how the string AM79C30A, AM79C978 (which are the two part numbers entered into a box in the previous page) can be broken with (#Form.ListofChoices#). I tried what you had (didb't work), modified it a bit to try and break it and got the same error message again. Maybe I don't quite understand what your doing with (#Form.ListofChoices#). How does the string get broken?

<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>
Select Filtran_Part_Number, Description, On_Web_Site, Approved
From filtran
Where AMD_Part_Number IN (# ?? #)
</cfquery>

It seems I can break the string in two ok.

From what i read, i need to loop through the database column first with the first number, then secondly with the second number. But of course I get that dreaded error.

It seems I would have to loop, but then i'm new at this.

Thank you

 

[Kenvase]

I made a DB and table and got this working
Replaced and with or between the LIKE's
<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>

SELECT Filtran_Part_Number, Description, On_Web_Site
FROM filtran
WHERE ( 0=0 and (AMD_Part_Number LIKE '%AM79C30A%') or (AMD_Part_Number LIKE '%AM79C978%'))
</cfquery>

<cfoutput query=&quot;filtran&quot;>#filtran_part_number#, #Description#, #On_Web_Site#<br></cfoutput>

 

[Kenvase]

Maybe like this ??

<cfquery name=&quot;filtran&quot; datasource=&quot;teamtec&quot; dbtype=&quot;ODBC&quot;>

SELECT Filtran_Part_Number, Description, On_Web_Site
FROM filtran
WHERE (0=0 and (AMD_Part_Number LIKE '%#ListGetAt(form.Filtran_Part_Number, 1,&quot;,&quot#%')) or
(0=0 and(AMD_Part_Number LIKE '%#ListGetAt(form.Filtran_Part_Number, 2)#%'))
</cfquery>

By the way is 0=0 ??
I would like it like this

<cfquery name=&quot;filtran&quot; datasource=&quot;teamtec&quot; dbtype=&quot;ODBC&quot;>

SELECT Filtran_Part_Number, Description, On_Web_Site
FROM filtran
WHERE (AMD_Part_Number LIKE '%#ListGetAt(form.Filtran_Part_Number, 1,&quot;,&quot#%') or
(AMD_Part_Number LIKE '%#ListGetAt(form.Filtran_Part_Number, 2)#%')
</cfquery>

 

[Kenvase]

Sorry,
Change datasource from teamtec to filtran.
I used a test db I have

 

[thunderain]

Kenvase,

I put the code you had in to test it. It gives me exactly the output I am looking for. The only thing is, the numbers are hardcoded in. I need to have the two numbers (ex: AM79C30A, AM79C978) come from the previous page, coming from one text box separated by a comma and then get the same output.

This is where i'm getting the error.

Thank you

 

[thunderain]

This is working somewhat. It depends on what order you put them in. I put two numbers in
(AM79C30A, AM79C978) it gives you what you want, 26 records for the first number and 2 records for the second. Great!!! BUT...if I switch the numbers, (AM79C978, AM79C30A) I only pick up the two records for the first number and not the 26 records for the second. This is strange. What would cause this?

(If i only put one number, it crashes, but I assume I need an if statement to handle that, either one number or two)

Thank you

 

[thunderain]

Thanks tlhawkins, kenvase
your responses helped me in this. I ended up going back to a loop. This does exactly as I was looking for, works great.

<Cfparam name=&quot;form.Filtran_Part_Number&quot; default=&quot;&quot;>

<cfquery name=&quot;filtran&quot; datasource=&quot;filtran&quot; dbtype=&quot;ODBC&quot;>
SELECT Filtran_Part_Number, Description, On_Web_Site, Approved
FROM filtran
WHERE
<Cfif listlen(form.Filtran_Part_Number)>
<Cfloop list=&quot;#form.Filtran_Part_Number#&quot; index=&quot;i_Part&quot;>
(Pulse_Part_Number LIKE '%#Trim(i_Part)#%')

<cfif not (i_Part eq listlast(form.Filtran_Part_Number))>
OR

</cfif>

</cfloop>
</cfif>