variable?error

[quiero mas]

I have this sql but something isnt quite right
could someone kindly point me in the right direction please

Search Page
SELECT Language_name
FROM tslanguage

Result page
select a.Hospital_Name, a.English, a.Russian
from tsHospital a, tsLanguage b, tshsptlang c
where a.Hosptial_ID = c.Hospital_ID
and b.Language_ID=c.Language_ID
and b.Language_Name=#cflanguage_variable#

I get an error about the cflanguage_variable

any ideas

cant seem to sort it out

Regards Mark

[Dan Bracuk]

I see two potential problems. The first is that you are referring to a
variable that doesn't exist. The second is that language_name looks like a
char field and you don't have quotes around the variable.

[MikerRoo]

Just to elaborate:

Your search form page must contain a text box or selector named
cflanguage_variable.

Then on the result page, this:
and b.Language_Name=#cflanguage_variable#

becomes this:
and b.Language_Name = <cfqueryparam value=#FORM.cflanguage_variable#
CFSQLType="CF_SQL_VARCHAR">



If cflanguage_variable is not from a form use an appropriate <CFPARAM>
statement at the top of your results file.
(Actually, use the <cfparam> even if cflanguage_variable IS a form variable.)

[quiero mas]

much appreciated - i want to make this into a teo select search.
one drop down enu populating the next.
example after choosing the language the user is able to then select the
location.

am i going about the process right? First get this one sorted te n move onto
the location one. then bring tem all together somehow. or should i be thinking
about this in another way?

thanks again for all the help
been away on business in Kyoto - was glad to see you both still here

[MikerRoo]

The best way to do that is to use Ajax to dynamically populate things.

Google CFAjax.

[MikerRoo]

Take a look at this example: [url]http://www.indiankey.com/cfajax/examples/list.htm[/url]

[quiero mas]

thanks miker
I gave it a try still a final tweak needed i think
I have heard about ajax - will start reading up on things

Search Page
<cfquery name="SearchLanguage" datasource="simple">
SELECT Language_name
FROM tslanguage</cfquery>
<form action="ResultLanguage.cfm" method="get">

<input type="submit" name="Submit" value="Submit">
<select name="cflanguage_variable">
<cfoutput query="SearchLanguage">
<option value="#SearchLanguage.Language_name#" <cfif
(isDefined("SearchLanguage.Language_name") AND SearchLanguage.Language_name EQ
SearchLanguage.Language_name)>selected</cfif>>#SearchLanguage.Language_name#</op
tion>
</cfoutput> </select>

</form>

Result page
<cfparam name="FORM.cflanguage_variable" default="1">
<cfquery name="gethospital" datasource="simple">
select a.Hospital_Name, a.English, a.Russian
from tsHospital a, tsLanguage b, tshsptlang c
where a.Hosptial_ID = c.Hospital_ID
and b.Language_ID=c.Language_ID
and b.Language_Name = <cfqueryparam value=#FORM.cflanguage_variable#
CFSQLType="CF_SQL_VARCHAR">
</cfquery>


I get an error telling me russina is not the default - tried a few combos - no
go

any ideas

cant seem to sort it out

Regards Mark

[quiero mas]

just had a look at the populating example - nice example very clear. thanks
after the drop downs have been populated - the next step is the sql on the
result page?
Is it hard to write the sql for something like what i want to achieve - i
guess once i ve done it once these these become much easier
thank you again miker
great tips
Mark

[MikerRoo]

Huh?

Please paste the EXACT error and indicate what page it is on.
Be sure to include the generated SQL that is in the error message.

[MikerRoo]

I have to run off but just noticed...

This code: <cfif (isDefined("SearchLanguage.Language_name") AND
SearchLanguage.Language_name EQ SearchLanguage.Language_name)>selected</cfif>

Should have the effect of selecting every value! Is this what you want?

Also, if the select can have multiple values you need to change the result
query.

Change the relevant line to this:
and b.Language_Name IN <cfqueryparam list="yes"
value=#FORM.cflanguage_variable# CFSQLType="CF_SQL_VARCHAR">

(Syntax not checked)