lists being passed into queries

[brokerandy25]

Any help would be so appreciated, I have beat my head against the wall on this
one and have scoured the web trying to find an answer. I am trying to build an
email program for a client that will allow them to issue press releases. The
trick is that they need to be able not only to select multiple counties in the
state, but then they need to select what type of media they send it to (also
multiple media oultets). NOTE my state has 95 counties, and there are 3 press
outlets, web, print and radio/ tv. I think that the query should look something
like this (unless anyone has a better answer) I need to be able to retrive all
email address from a table, that are say both radio and print oriented for 4
counties. <cfquery name'PressRelease' datasource='name'> SELECT Name,
EmailAddress From PressReleaseEmail WHERE County = XXXXXXXXXXXX AND MediaType =
<cfif isDefined ('Form.Print')> '#Form.Print#' <else> NULL </cfif> <cfif
isDefined ('Form.WEB')> OR '#FORM.WEB#' <esle> NULL </cfif> <cfif isDefined
('Form.Radio')> OR '#Form.Radio#' <else> NULL </cfif> I know that the query
can be dynamically built from the radio buttons, I know it can but I cannot get
it to work, without either getting <else> in the query statement and also the
Nulls. and how do I pass the list from FORM.County into the query?
AAARRRRRGGGGHHH!!!!!! All help will be wonderful. Andy

[shawnwindler]

First suggestion. Use <CFELSE>, not <else> Second. Don't say else = NULL.
Cause then you are only looking for (for example) PressReleases where MediaType
= NULL. Perhaps something like <CFIF isDefined('Form.Print')>AND MediaType =
'#Form.Print#'</CFIF> would work? Third. I believe WHERE County IN
#Form.County# will check a list to see if County is in that list. You might
need single quotes around it though, I'm not quite sure. Hope that helps. If
not, post back. - Shawn

[brokerandy25]

I feel dumb with the cfelse vs else. doh!! I am having problems with the where
clause. Here is what I have: <cfquery name='PressReleaseQuery'
datasource='KidneyIndiana'> SELECT * From PressRelease WHERE County IN
(#Form.County#) AND MediaType IN (#Form.Print#)</cfquery> Both form.county and
form.print are dynamically built on a previous page, the page that this query
is on is an 'action' page. I was told to maybe use cfloop and loop through the
county list, but I have to loop through not only the county list, but also loop
through the media type list and all possible iterations approxmitaly in other
words (95!) * 3 possiblities.

been sent.</font></div>
</div>
<div id="Layer4" style="position:absolute; left:30px; top:143px; width:705px;
height:121px; z-index:6">
<cfquery name="PressReleaseQuery" datasource="KidneyIndiana">
SELECT * From PressRelease WHERE County IN (#Form.County#) AND MediaType IN
(#Form.Print#)</cfquery>
<!--- <cfif isDefined ('Form.Print')> --->
<!--- ''
<cfelse>
NULL
</cfif>
<cfif isDefined ('Form.WEB')>
OR '#FORM.WEB#'
<cfesle>
NULL
</cfif>
<cfif isDefined ('Form.Radio')>
OR '#Form.Radio#'
<cfelse>
NULL
</cfif> --->
<cfoutput query="PressRelease">#PressRelease.Name#</cfoutput></div>

</body>
</html>

[The ScareCrow]

I think you will find the problem to be the lists. I assume the values in the
lists are text ?

If so you will need to enclose each element of the lists in single quotes.
This is done by using ListQualify(list, qualifier [, delimiters ] [, elements
])

Thus
SELECT * From PressRelease WHERE County IN (#ListQualify(Form.County, "'",
"All")#) AND MediaType IN (#ListQualify(Form.Print, "'", "All")#)

There is no need for all those nulls.

You don't indicate how these lists are formed. I assume you may have a select
list (drop down) for the counties and maybe a group of checkboxes for the media
type. If you have checkboxes, give them the same name, just with the relevant
value. Then the above should work.


Ken

[brokerandy25]

Well I don't know if we are any closer but Now I am getting Too few parameters.
Expected 1. after tweaking the code to : SELECT * From PressRelease WHERE
County IN (#ListQualify(Form.County, ''', ',')#) AND MediaType IN
(#ListQualify(Form.Print, ''', ',')#)</cfquery> The lists appear as such:
Marion,Hamilton,Hancock cold fusion error shows SQL SELECT * From
PressRelease WHERE County IN ('Marion','Hamilton') AND MediaType IN
('Radio','Print') DATASOURCE KidneyIndiana VENDORERRORCODE -3010
SQLSTATE 07002 And the sql error equaates to 07002 The call parameter list
or control block is invalid.

[brokerandy25]

The lists are built from select boxes in html forms that are set to allow
multiple selects, my client needs to choose from one to all of the counties,
and one to all of the media outlets PLEASE SOME ONE HELP, I have spent 6 hours
on this and have almost thrown my laptop off the balcony. The Table constists
of : AutoNumber - PK Name EmailAddress County Source - this is the media type
PhoneNumber

[The ScareCrow]

If I was doing this I would also have a "counties" table and a "mediatype" table

Then instead of the actual text in the columns of your pressrelease table I
would have the id
This would involve changing the select lists to pass the id's in the value of
the select lists as well.
Then you would not need to put single quotes around each element in the lists
for the query.

But, to resolve your problem

Copy the cf query text which is
SELECT * From PressRelease WHERE County IN ('Marion','Hamilton') AND MediaType
IN ('Radio','Print')

Select the query tab in ms access to create a new query and paste this into
it. See what the error is.

To me the sql statement looks fine. It might also be giving an error on the
column with the name of "Name" as this may be a reserved word. Try listing the
column names and enclosing in square brackets "[" "]" to see if this gets rid
of the error

Ken

[brokerandy25]

It appears that MS access is reading County and MediaType as variables, does
this problem lead to a QoQ with a Query ontop, example select all from the
table where MediaType is this or this, do another query where these counties
are chosen, then all that have matching ID numbers are selected? BTW the
Counties and MediaType are in their own tables to begin with the select boxes
are dynamic. I did this because I don't want to put in 95 counties, and there
are not media outles in all counties. I really appreciate your help. Thanks
Andy

[Kronin555]

BTW the Counties and MediaType are in their own tables to begin with the select
boxes are dynamic
OK, so what exactly is the County and MediaType column in the
PressReleaseEmail table? Are they varchars that hold the name? or are they int
fields that hold the ID linked to the County table?

[Kronin555]

In one of your previous posts you say:
The Table constists of :

AutoNumber - PK
Name
EmailAddress
County
Source - this is the media type
PhoneNumber

And in your query you're comparing County to a list of strings (so County
should be a varchar or similar type, holding the county name) and you're
comparing MediaType to a list of strings.

Note: You have no MediaType column in your table! Above you say it's called
Source.

[The ScareCrow]

I think Kronin555 has hit on the questions I would have asked.

But also, I'm just wondering if "Source" is a reserved word and this could be
causing some trouble as well.

As you have stated that you already have the information in other tables, this
is how I would have the "PressReleaseEmail" table

AutoNumber - PK
ClientName
EmailAddress
CountyId - integer
MediaId - integer
PhoneNumber

Ken

[brokerandy25]

so use the same query structure, just look for numbers instead of strings?

[The ScareCrow]

Yes, that's correct

eg

Form page
<cfquery name="get_media" datasource="dsn">
Select media_id, mediatype from mediatypes
</cfquery
<cfquery name="get_counties" datasource="dsn">
Select county_id, county from counties
</cfquery>

<select name="media">
<cfoutput query="get_media">
<option value="#media_id#">#mediatype#</option>
</select>

<select name="county">
<cfoutput query="get_counties">
<option value="#county_id#">#county#</option>
</select>

Action page

<cfquery name="get_emails" datasource="dsn">
SELECT *
From PressRelease
WHERE County IN (#form.county#)
AND MediaType IN (#form.media#)
</cfquery>

Ken

[brokerandy25]

almost seems too simple . . . I am off to my "real Job" will give it a try later.


Thanks,

Andy

[brokerandy25]

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Too few parameters. Expected 1.

The error occurred in C:\CFusionMX\wwwroot\New
NKF\EmployeeDBAccess\EmployeePages\PressRelease\em ail_corfirm.cfm: line 2


1 : <cfquery name="get_emails" datasource="KidneyIndiana">
2 : SELECT * FROM PressRelease WHERE County IN (#form.County#) AND MediaType
IN (#form.media#)
3 : </cfquery>
4 :
SQL SELECT * FROM PressRelease WHERE County IN (1,2) AND MediaType IN (2,3)

DATASOURCE KidneyIndiana
VENDORERRORCODE -3010
SQLSTATE 07002
Please try the following:

Is this a problem with the DB setting? I am quite the newbie to DBs and
strings and all that jazz.

[brokerandy25]

Did a little tweaking and now it works, Thank you for all of your help.