Hi Michael,
I'll just start at the top, ok?
What is distrib_state? I get the impression that it is the actual name
of the state. If so, I would suggest that you use state_id instead - I
know the names of the states are not going to change but it will save
you space and is good database design.
Now, modify your query and select to include the state_id...
<CFQUERY NAME="GetState" DATASOURCE="dbcms">
SELECT state_name, state_id
FROM tblstates
</CFQUERY>
<SELECT NAME="distrib_state">
<CFOUTPUT QUERY="GetState">
<OPTION value="#GetState.state_id#">#GetState.state_name#</OPTION>
</CFOUTPUT>
</SELECT>
This means that when you action the form you get the state_id as the
value of distrib_state rather then state_name.
In the update form where you want to display the state for the
distributor you need to add a bit more code to the select so that it
'selects' the state you want:
<SELECT NAME="distrib_state">
<CFOUTPUT QUERY="GetState">
<OPTION value="#GetState.state_id#" <cfif GetState.state_id eq
Distributor.state_id>SELECTED</cfif> >
#GetState.state_name#
</OPTION>
</CFOUTPUT>
</SELECT>
I'm not sure why you are using <CFOUTPUT QUERY="Distributor"> when you
know you are only going to get a single record. It does save typing
'distributor' in front of the variable names but I would suggest that
this is a false economy. Using full 'dot' notation (as recommended by
Bill) will make your maintenance task much easier in another four years
time and is (I believe) marginally faster for CF to lookup.
If you decide to continue to use <CFOUTPUT QUERY="Distributor"> then you
will need to change the <CFOUTPUT QUERY="GetState"> in the select to be
<CFLOOP QUERY="GetState">. OK? And don't forget the </cfoutput> to
</cfloop>...
I hope that answers your question...
Regards
Brett Payne-Rhodes
B)
Eaglehawk Computing
Perth, Western Australia
Michael Wilson wrote:
>
> Hi all and thanks for taking time to look this over. I will try my best to
> keep it as brief as possible, although I need to give you some background on
> my problem. Thanks in advance for any help you can provide.
>
> I have a client I did a site for about 4 years ago and it have never really
> been changed much. The site owner asked me to update the look and
> functionality of the site to make it easier to use. He also requested that I
> create a system that would allow him to update the Distributors section on
> his own, so I went with ColdFusion, because I want to learn more about it.
>
> I am working on the code for the Distributors first. The original page is
> http://www.mfay.com/Locations.htm. Don't laugh, it's really Old School...
>
> I stared with my Database in MS Access, dbcms.mdb. I have 2 tables,
> tbldistrib and tblstates.
>
> tbldistrib has the following columns: distrib_id (auto number),
> distrib_name, distrib_city, distrib_state and distrib_phone
>
> tblstates has the following columns: state_id (auto number), state_name and
> state_abb (incase he wants to use abbreviation later)
>
> Next I started on the Add New Distributor area of his CMS. I created the
> insert form using Text type inputs for name, city and phone. I used a select
> box for the sate and got the select options from tblstates/state_names in
> the Database.
>
> the code I used for the select box:
>
> Query:
>
> <CFQUERY NAME="GetState" DATASOURCE="dbcms">
> SELECT state_name
> FROM tblstates
> </CFQUERY>
>
> Output:
>
> <SELECT NAME="distrib_state">
> <CFOUTPUT QUERY="GetState">
> <OPTION>
> #GetState.state_name#
> </OPTION>
> </CFOUTPUT>
> </SELECT>
>
> All went according to plan and it works fine. I then created the display
> page and messed around with the ordering and formatting until I have it real
> close to the original style of listing the distributors. You can find it at:
> http://xiondev2.dynip.com/cms/list_distrib.cfm
>
> Then I started on the Update Distributor section... I can make this work by
> using the following
>
> Query:
>
> <CFQUERY DATASOURCE="dbcms" NAME="Distributor">
> SELECT distrib_name,
> distrib_city,
> distrib_state,
> distrib_phone
> FROM tbldistrib
> WHERE distrib_id = #distrib_id#
> </CFQUERY>
>
> Output:
>
> <CFOUTPUT QUERY="Distributor">
>
> <FORM ACTION="distrib_update.cfm" METHOD="post">
> <INPUT TYPE="hidden" NAME=distrib_id" Value="#distrib_id#">
>
> <p>
> Distributor Name:
> <INPUT TYPE="text" NAME=distrib_name" SIZE="35" MAXLENGTH="50"
> Value="#Trim(distrib_name)#">
>
> ..and so forth for each form field. What I would like to do is have the
> "State Name" portion of the form return as a select box as in the insert
> form, displaying the current distrib_state as the default option while also
> populating the rest of the select box (options) with the data (state names)
> from the tblsates/state_names in the database. This way my client can change
> the Distributor State if he needs to in the same manner as when he adds a
> new distributor.
>
> I tried running 2 Queries and 2 outputs calling a distrib_id in the url
> (...cfm?distrib_id=30)
>
> Query:
>
> <CFQUERY DATASOURCE="dbcms" NAME="GetState" >
> SELECT state_name
> FROM tblstates
> </CFQUERY>
>
> Output:
>
> <SELECT NAME="distrib_state">
> <CFOUTPUT QUERY="GetState">
> <OPTION>
> #GetState.state_name#
> </OPTION>
> <OPTION>
> #Trim(distrib_state)#
> <OPTION>
> </CFOUTPUT>
> </SELECT>
>
> and received errors:
>
> Invalid tag nesting configuration
>
> A query driven CFOUTPUT tag is nested inside a CFOUTPUT tag that also has a
> QUERY= attribute. This is not allowed. Nesting these tags implies that you
> want to use grouped processing. However, only the top-level tag can specify
> the query that drives the processing.
>
> I moved things around several times and got several different results and
> errors, lol but nothing like what I was thinking about.
>
> Any ideas on how I can accomplish what I am looking for? Should I go back to
> the insert form and have him manually enter the state information and forget
> about the select box and the tblstates idea? The actual goal was to learn
> _how_ to do this rather than it being a "must have" in the system, but I am
> out of ideas.
>
> Any help is much appreciated and I am sorry for being long on words, but I
> wanted to make sure my question was clear enough to elicit clear answers
> that I can understand.
>
> I have placed all the CF code in .txt files at:
>
> http://xiondev2.dynip.com/cms/cftext/insertform.txt
> http://xiondev2.dynip.com/cms/cftext/insertdata.txt
> http://xiondev2.dynip.com/cms/cftext/updateform.txt
> http://xiondev2.dynip.com/cms/cftext/list_distrib.txt
>
> Thanks,
>
> Mike
>
>
>------------------------------------------------------------------------------------------------
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
>with 'unsubscribe' in the body to [EMAIL PROTECTED]
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
