Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

radio button help

ColdFusion 9
MS SQL Server 2012

Hi.

I have this insert error (see below). ColdFusion does not like the datatype that I submit when I select a radio button. (?)

It seems like I have solved this before, but it has been a while, and a search in my question history did not yield a solution.  Thank you for your advice as always.

(I also need to figure a way to make sure only one radio button, among the seven radio buttons in the form, is selected. A script for that? I am researching.)

Eric

Error:

 Invalid data '' for CFSQLTYPE CF_SQL_BIT.
 
The error occurred in C:/websites/www.nnvawi.org/Register.cfm: line 90
Called from C:/websites/www.nnvawi.org/Register.cfm: line 88
Called from C:/websites/www.nnvawi.org/Register.cfm: line 67
Called from C:/websites/www.nnvawi.org/Register.cfm: line 1

88 : <cftransaction>
89 : <!--- query to insert new user record into content table --->
90 :                   <cfquery name="InsertPage" datasource="#application.datasource#" result="newRegistrant">
91 :                          INSERT INTO #REQUEST.RegisterTable#
92 :      


My register form:

<!-----
Name:        Register.cfm
Author:      EB
Description: add, update conference registrants
Created:     July 2014
Revised: July 2014
ColdFusion Version 9
MS SQL Server 2012
----->

<!--- set a default value "" for RegisterID in scope URL --->
<cfparam name="url.RegisterID" default="">

<!--- define the RegisterID in scope FORM, then set form.RegisterID equal to the RegisterID passed in the URL --->
<cfparam name="form.RegisterID" default="#url.RegisterID#">

<!--- set default values for other user-editable fields --->
<cfparam name="form.Title" default="">
<cfparam name="form.FirstName" default="">
<cfparam name="form.MiddleInitial" default="">
<cfparam name="form.LastName" default="">
<cfparam name="form.Credentials" default="">
<cfparam name="form.Organization" default="">
<cfparam name="form.Address" default="">
<cfparam name="form.City" default="">
<cfparam name="form.State" default="">
<cfparam name="form.ZIP" default="">
<cfparam name="form.Country" default="">
<cfparam name="form.TelephoneHome" default="">
<cfparam name="form.TelephoneWork" default="">
<cfparam name="form.TelephoneMobile" default="">
<cfparam name="form.FAX" default="">
<cfparam name="form.UserEmail" default="">
<cfparam name="form.AltUserEmail" default="">
<cfparam name="form.SpecialRequirements" default="">
<cfparam name="form.DateCreated" default="">
<cfparam name="form.DateModified" default="">

  <!--- set default values for radio buttons --->
<cfparam name="form.NNVAWIMembersConferenceFee" default="0">
<cfparam name="form.JoinRenewConferenceFee" default="0">
<cfparam name="form.NonMemberConferenceFee" default="0">
<cfparam name="form.StudentConferenceFee" default="0">
<cfparam name="form.ThursdayConferenceFee" default="0">
<cfparam name="form.FridayConferenceFee" default="0">
<cfparam name="form.SaturdayConferenceFee" default="0">


<!--- in user-editable fields, set up protection against XSS  --->
    <cfloop collection="#FORM#" item="field">
      <cfset FORM[ field ] = ReReplaceNoCase (FORM[ field ], "<script.*?>.*?</script>", "", "all")>
    </cfloop>



		   
<!---- begin CFTRY; catch errors ---->
<cftry>  
 
<!---- populate cftry with error message ---->
<cfset variables.error = ""> 
 
<!--- BEGIN: Save action --->

<!--- begin form.doSave --->

<cfif IsDefined("FORM.doSave")>


 <!--- in this query select NOTHING from table #request.RegisterTable#, and simply check if UserEmail exists --->
 
 <cfquery datasource="#APPLICATION.dataSource#" name="CheckUserEmail">
  SELECT 'Nothing' FROM #request.RegisterTable#
  WHERE UserEmail = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.UserEmail#">
  AND  RegisterID <> <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.RegisterID)#">

  </cfquery>

  
   <!--- if UserEmail exists, display error; refuse record insert --->
   
	<cfif CheckUserEmail.recordcount GT 0>
		   <cfthrow message="That email address is already in use. Did you already register for the NNVAWI conference? Please contact NNVAWI at info@nnvawi.org.">
           
	</cfif>


<cftransaction>
<!--- query to insert new user record into content table --->
			<cfquery name="InsertPage" datasource="#application.datasource#" result="newRegistrant">
				 INSERT INTO #REQUEST.RegisterTable#
     					(
Title
,FirstName
,MiddleInitial
,LastName
,Credentials
,Organization
,Address
,City
,State
,ZIP
,Country
,TelephoneHome
,TelephoneWork
,TelephoneMobile
,FAX
,UserEmail
,AltUserEmail
,SpecialRequirements
,NNVAWIMembersConferenceFee
,JoinRenewConferenceFee
,NonMemberConferenceFee
,StudentConferenceFee
,ThursdayConferenceFee
,FridayConferenceFee
,SaturdayConferenceFee
,DateCreated
                        )
                        
                        
			     VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Title,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.FirstName,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.MiddleInitial,5))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.LastName,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Credentials,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Organization,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Address,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.City,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.State,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.ZIP,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Country,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.TelephoneHome,25))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.TelephoneWork,25))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.TelephoneMobile,25))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.FAX,25))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.UserEmail,128))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.AltUserEmail,128))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.SpecialRequirements,255))#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.NNVAWIMembersConferenceFee)#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.JoinRenewConferenceFee)#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.NonMemberConferenceFee)#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.StudentConferenceFee)#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.ThursdayConferenceFee)#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.FridayConferenceFee)#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.SaturdayConferenceFee)#">,
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
)         
					</cfquery>
        


   <cfmail
      from="#form.UserEmail#"
      to="email address"
      subject="New #REQUEST.companyName# conference registration ready for your review as of #DateFormat(Now())#, #TimeFormat(Now())#"
      SpoolEnable="Yes">Greetings, #REQUEST.companyName#. There is a new #REQUEST.companyName# conference registration ready for your review. Please check the #REQUEST.companyName# registrantship control panel.</cfmail>

<!--- /did user unsubscribe from mailing list? --->
    </cftransaction>
        
                   
<!--- use the result attribute value (newRegistrant) to set form field value --->
      <cfset form.RegisterID = newRegistrant.IDENTITYCOL>
      
  
              
<!--- END queries to update or insert database records ---> 



       <!--- done? relocate --->

<cflocation url="/thankyouConference.cfm" addtoken="no">

             
<!--- END: Save action --->

<!--- END form.doSave --->
                    </cfif>
       
<!--- END queries to update or insert database records ---> 
        

<!--- this CFCATCH will trap errors -- the ones you threw or just regular database issues --->
            <cfcatch type="Any">
                 <cfset variables.error = cfcatch.message>
                 <cfrethrow>
            </cfcatch>

<!--- END CFTRY --->  
			</cftry>
       
       
       
<cfinclude template="/SiteHeader.cfm">


<!--- if there an error, display error in readable form --->

<cfif len(variables.error)> 
	  <cfoutput>
	    <div class="errorbox">#variables.error#</div>
	    </cfoutput>
   
   <br />

             <div class="center">
               <input type="button" value="Go Back" onclick="history.go(-1)" />
</div>
             
             <cfabort>
</cfif>




<!--- form begins here --->
<form method="post" enctype="multipart/form-data" name="ebwebworkForm" class="ebwebworkForm">
                
 

                
 
 <!--- Embed RegisterID (PK) to assign a value to it --->
 <cfoutput>
<input type="hidden" name="RegisterID" value="#form.RegisterID#" />
   </cfoutput>





  <ul>
        <li>
<legend><h2>Register</h2></legend>
      </li>
      


<p><span class="red"><i class="icon-asterisk"></i></span> <em>indicates a required field.</em></p>

<li>
  <label for="Title"><h3>Title (Ms., Mr., Dr. etc.):</h3></label>



<input type="text" name="Title" placeholder="Title" value="" maxlength="255" tabindex="1" size="70" autofocus="true" />
               
</li>
                              
<li>
  <label for="FirstName"><h3>First Name:</h3></label>


<input type="text" name="FirstName" placeholder="First Name" value="" maxlength="255" tabindex="2" size="70" required="yes" />
        <span class="form_hint">Enter First Name</span> 
               
</li>


<li>
  <label for="MiddleInitial"><h3>Middle Initial:</h3></label>


<input type="text" name="MiddleInitial" placeholder="MI" value="" maxlength="5" tabindex="3" size="1" />
</li>



<li>
  <label for="LastName"><h3>Last Name:</h3></label>


<input type="text" name="LastName" placeholder="Last Name" value="" maxlength="255" tabindex="4" size="70" required="yes" />
        <span class="form_hint">Enter Last Name</span> 
               
</li>

<li>
  <label for="Credentials"><h3>Credentials:</h3></label>


<input type="text" name="Credentials" placeholder="Credentials (e.g. RN, MSN, PhD, MD)" value="" maxlength="255" tabindex="5" size="70" required="yes" />
        <span class="form_hint">Credentials (e.g. RN, MSN, PhD, MD)</span> 
               
</li>


<li>
  <label for="Organization"><h3>Current Position and Organization:</h3></label>


<input type="text" name="Organization" placeholder="Current Position and Organization" value="" maxlength="255" tabindex="6" size="70" required="yes" />
        <span class="form_hint">Enter Current Position and Organization</span> 
               
</li>


    
 
 
  <li>
  <label for="Address"><h3>Address:</h3></label>


<input type="text" name="Address" placeholder="Please enter your Address" value="" maxlength="255" tabindex="7" size="70" required="yes" />
        <span class="form_hint">Please enter your mailing address</span> 
               
</li>
    


  <li>
  <label for="City"><h3>City:</h3></label>


<input type="text" name="City" placeholder="Please enter your City" value="" maxlength="255" tabindex="8" size="70" required="yes" />
        <span class="form_hint">Please enter your City</span> 
               
</li>

         
         
           <li>
  <label for="State"><h3>State or Province:</h3></label>


<input type="text" name="State" placeholder="Please enter your State or Province" value="" maxlength="255" tabindex="9" size="70" required="yes" />
        <span class="form_hint">Please enter your State or Province</span> 
               
</li>                     
                         

           <li>
  <label for="ZIP"><h3>ZIP or Postal Code:</h3></label>


<input type="text" name="ZIP" placeholder="Please enter your ZIP or Postal Code" value="" maxlength="255" tabindex="10" size="70" required="yes" />
        <span class="form_hint">Please enter your ZIP or Postal Code</span> 
               
</li>  

           <li>
  <label for="Country"><h3>Country:</h3></label>


<input type="text" name="Country" placeholder="Please enter your Country" value="" maxlength="255" tabindex="11" size="70" required="yes" />
        <span class="form_hint">Please enter your Country</span> 
               
</li>  

           <li>
  <label for="TelephoneHome"><h3>Home Telephone:</h3></label>


<input type="text" name="TelephoneHome" placeholder="Please enter your home telephone number" value="" maxlength="25" tabindex="12" size="70" />
        <span class="form_hint">Please enter your home telephone number.</span> 
               
</li>     



           <li>
  <label for="TelephoneWork"><h3>Work Telephone:</h3></label>


<input type="text" name="TelephoneWork" placeholder="Please enter your work telephone number" value="" maxlength="25" tabindex="13" size="70" />
        <span class="form_hint">Please enter your work telephone number.</span> 
               
</li>     


           <li>
  <label for="TelephoneMobile"><h3>Mobile Telephone:</h3></label>


<input type="text" name="TelephoneMobile" placeholder="Please enter your mobile telephone number" value="" maxlength="25" tabindex="14" size="70" />
        <span class="form_hint">Please enter your mobile telephone number.</span> 
               
</li>     




           <li>
  <label for="FAX"><h3>FAX:</h3></label>


<input type="text" name="FAX" placeholder="Please enter your FAX number" value="" maxlength="25" tabindex="15" size="70" />
        <span class="form_hint">Please enter your FAX number.</span> 
               
</li>     

<li>
  <label for="UserEmail"><h3>Email Address:</h3></label>


<input type="text" name="UserEmail" placeholder="Important: Please enter your valid email address." value="" maxlength="128" tabindex="16" size="70" required="yes" />
        <span class="form_hint">Enter Email Address</span> 
               
</li>


           <li>
  <label for="AltUserEmail"><h3>Alternate Email Address:</h3></label>


<input type="text" name="AltUserEmail" placeholder="Please enter an alternate Email Address" value="" maxlength="128" tabindex="17" size="70" />
              
</li>                   

      
      <li>
  <label for="SpecialRequirements"><h3>Special Requirements -- dietary, mobility, etc.:</h3></label>


<input type="text" name="SpecialRequirements" placeholder="Important: Please enter special requirements, if any." value="" maxlength="255" tabindex="18" size="70" />
               
</li>


<h2>Conference Fees</h2>

<p>There are two ways to take advantage of lower fees for NNVAWI members.</p>


<p><i class="icon-check green"></i> If you are a member in good standing, select the member rate.</p>

<p><i class="icon-check green"></i> To join NNVAWI (or renew your membership) when you register for the conference, select the 'Join/Renew and Register' rate.</p>



<h2>Payment Options</h2>

<p><em>All fees are payable in U.S. Funds.</em></p>


<table width="40%" summary="Early Bird Full Conference Fee for NNVAWI Conference, April 9 - 11, 2015">
  <caption>
    <h3>Early Bird Full Conference Fee</h3>
  </caption>
  <tr>
    <td>NNVAWI Member</td>
    <td>
    <input type="radio" name="NNVAWIMembersConferenceFee" value="" tabindex="19" class="border0" /> $300.00</td>
  </tr>
  <tr>
    <td>Join / Renew and Registration</td>
    <td><input type="radio" name="JoinRenewConferenceFee" value="" tabindex="20" class="border0" /> $400.00</td>
  </tr>
  <tr>
    <td>Non-Member</td>
    <td><input type="radio" name="NonMemberConferenceFee" value="" tabindex="21" class="border0" /> $415.00</td>
  </tr>
  <tr>
    <td>Full-time Student</td>
    <td><input type="radio" name="StudentConferenceFee" value="" tabindex="22" class="border0" /> $120.00</td>
  </tr>
</table>


<table width="40%" summary="One-Day Registration for NNVAWI Conference, April 9 - 11, 2015">
  <caption>
    <h3>One-Day Registration</h3>
  </caption>
  <tr>
    <td>Thursday, April 9</td>
    <td>
<input type="radio" name="ThursdayConferenceFee" value="" tabindex="23" class="border0" />    $200.00 </td>
  </tr>
  <tr>
    <td>Friday, April 10</td>
    <td><input type="radio" name="FridayConferenceFee" value="" tabindex="24" class="border0" />    $200.00 </td>
  </tr>
  <tr>
    <td>Saturday, April 11</td>
    <td><input type="radio" name="SaturdayConferenceFee" value="" tabindex="25" class="border0" />    $200.00 </td>
  </tr>
</table>



<p><strong>Full Registration Fee Includes:</strong><br /> 
Continental breakfast, snacks and lunch each day; entry to conference sessions, Thursday evening Opening Reception, Friday poster session and reception.</p>  

<p><strong>One-Day Fee Includes:</strong><br />
Continental breakfast, snacks and lunch on the day of registration, and entrance to conference sessions scheduled on that day.</p>

    <li>
<div class="submitButton">
   <cfoutput>  
   <button name="doSave" type="submit" class="green" tabindex="19">Register for #REQUEST.companyName# Conference</button>
   </cfoutput>
</div>  
</li>
    
    
    </ul>

</form>

<p class="center"><span class="red"><i class="icon-asterisk"></i></span> <em>An NNVAWI administrator will review and confirm your registration.</em></p>

        <cfinclude template="/SiteFooter.cfm">

Open in new window

0
Eric Bourland
Asked:
Eric Bourland
  • 7
  • 6
2 Solutions
 
_agx_Commented:
@Scott Fell - I was trying to have my cake and eat it too ;-) I avoided the code blocks so I could emphasize the key code changes in each section with bold text.  

It looks like the text got mixed in with the three code blocks? Could you fix it or if it's easier, just delete the whole thing and I'll post it anew?  Thanks.
0
 
_agx_Commented:
Hi Eric.  The error is because the value of those buttons is set "" which isn't a valid boolean value.  Also, to enforce the "select-only-one" rule, the radio buttons must all share the same name:

    
    <input type="radio" name="ConferenceFeeType" value="NNVAWIMembersConferenceFee" ...>    
    <input type="radio" name="ConferenceFeeType" value="JoinRenewConferenceFee" ...>
    <input type="radio" name="ConferenceFeeType" value="NonMemberConferenceFee" ...>
    <input type="radio" name="ConferenceFeeType" value="StudentConferenceFee" ...>
    <input type="radio" name="ConferenceFeeType" value="ThursdayConferenceFee" ...>
    <input type="radio" name="ConferenceFeeType" value="FridayConferenceFee" ...>
    <input type="radio" name="ConferenceFeeType" value="SaturdayConferenceFee" ...>

Open in new window


So you'll only need a single cfparam statement for the buttons:

     <!--- set default values for radio buttons --->
     <cfparam name="form.ConferenceFeeType" default="">

Open in new window



Since you're inserting the selections into 7 separate columns, you'll need tweak the data a bit.  So as not to change too much of your query code, you could do something like this.


<cfif IsDefined("FORM.doSave")>
         ...
	<!--- convert selected string into 7 boolean flags --->
        <!--- if user selected "NNVAWIMembersConferenceFee" button, this variable will be "YES" --->
	<cfset variables.NNVAWIMembersConferenceFee = trim(form.ConferenceFeeType) eq "NNVAWIMembersConferenceFee">
        <!--- if user selected "JoinRenewConferenceFee" button, this variable will be "YES" --->
	<cfset variables.JoinRenewConferenceFee = trim(form.ConferenceFeeType) eq "JoinRenewConferenceFee">
        <!--- lather, rinse, repeat for the rest of the options ....  --->
	<cfset variables.NonMemberConferenceFee = trim(form.ConferenceFeeType) eq "NonMemberConferenceFee">
	<cfset variables.StudentConferenceFee = trim(form.ConferenceFeeType) eq "StudentConferenceFee">
	<cfset variables.ThursdayConferenceFee = trim(form.ConferenceFeeType) eq "ThursdayConferenceFee">
	<cfset variables.FridayConferenceFee = trim(form.ConferenceFeeType) eq "FridayConferenceFee">
	<cfset variables.SaturdayConferenceFee = trim(form.ConferenceFeeType) eq "SaturdayConferenceFee">

Open in new window


Then change the cfqueryparam for those columns to use the #variables# scope instead of #form#

    ....
    <cfqueryparam cfsqltype="cf_sql_bit" value="#variables.NNVAWIMembersConferenceFee#">,
    <cfqueryparam cfsqltype="cf_sql_bit" value="#variables.JoinRenewConferenceFee#">,
    <cfqueryparam cfsqltype="cf_sql_bit" value="#variables.NonMemberConferenceFee#">,
    <cfqueryparam cfsqltype="cf_sql_bit" value="#variables.StudentConferenceFee#">,
    <cfqueryparam cfsqltype="cf_sql_bit" value="#variables.ThursdayConferenceFee#">,
    <cfqueryparam cfsqltype="cf_sql_bit" value="#variables.FridayConferenceFee#">,
    <cfqueryparam cfsqltype="cf_sql_bit" value="#variables.SaturdayConferenceFee#">,
    <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
     )
    ....

Open in new window


That should do it.
0
 
_agx_Commented:
@Scott Fell  - I figured it was easier for me to just repost it, so you can go ahead and delete the original? Thanks.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dave BaldwinFixer of ProblemsCommented:
If they are actually radio buttons in the HTML with the same 'name' attribute, you can't select more than one at a time.  If you did not give them the same name then they are not part of the same radio button group.  In addition, the default value for radio buttons and checkboxes is 'on' which is not a 'bit' value but a 'text' value.  The smallest value you can assign to a radio button is a single character which is still going to be 'text'.

Looks like I repeated most of what _agx_ said.
0
 
Eric BourlandAuthor Commented:
Friends, got it. Working on this now. It makes sense.

I'll come back in a few minutes. Thank you. =)

Eric
0
 
Eric BourlandAuthor Commented:
Making progress on this.

Question:

In my data table, do I need one column for ConferenceFeeType (datatype: bit)?

Or do I need seven columns, each with datatype bit?

,NNVAWIMembersConferenceFee
,JoinRenewConferenceFee
,NonMemberConferenceFee
,StudentConferenceFee
,ThursdayConferenceFee
,FridayConferenceFee
,SaturdayConferenceFee

My data table looks like this, as of this morning:

RegisterID      int      Unchecked
Title      nvarchar(25)      Checked
FirstName      nvarchar(255)      Checked
MiddleInitial      nvarchar(5)      Checked
LastName      nvarchar(255)      Checked
Credentials      nvarchar(255)      Checked
Organization      nvarchar(255)      Checked
Address      nvarchar(255)      Checked
City      nvarchar(255)      Checked
State      nvarchar(255)      Checked
ZIP      nvarchar(255)      Checked
Country      nvarchar(255)      Checked
TelephoneHome      nvarchar(25)      Checked
TelephoneWork      nvarchar(25)      Checked
TelephoneMobile      nvarchar(25)      Checked
FAX      nvarchar(25)      Checked
UserEmail      nvarchar(128)      Checked
AltUserEmail      nvarchar(128)      Checked
SpecialRequirements      nvarchar(255)      Checked
ConferenceFeeType      bit      Checked
DateCreated      datetime      Checked
DateModified      datetime      Checked
0
 
_agx_Commented:
In my data table, do I need one column for ConferenceFeeType (datatype: bit)? Or do I need seven columns, each with datatype bit?

Well.. it depends on whether you can change the db structure.  My previous example works with the original (ie 7 columns) But it would be simpler to store all of the unique fee types in a separate table and add a single integer column to your data table:

ConferenceFeeType
-----------------
ID |  Title
1  | NNVAW IMembers
2  | Join Renew
.....

Open in new window


You could generate the radio buttons with a simple query and loop

<cfquery name="getConferenceTypes"> 
    SELECT ID, Title FROM ConferenceFeeType  
    ....
</cfquery>

<cfloop query="getConferenceTypes">
     <input type="radio" name="ConferenceFeeTypeID" value="#getConferenceTypes.ID#" ...> #getConferenceTypes.Title# <br>   
</cfloop>

Open in new window


... and the INSERT would just be:


     <cfqueryparam cfsqltype="cf_sql_integer" value="#val(FORM.ConferenceFeeTypeID)#">

Open in new window

0
 
Eric BourlandAuthor Commented:
Got it! I am going to try this now. It makes sense.

Thank you. I'll come back here with my results. =)

Hope your weekend is going well.
0
 
Eric BourlandAuthor Commented:
Good morning, _agx_,

Hope your weekend was good.

You solution above is really neat. That is a great way to implement the registration radio buttons -- just use a query. =) Makes sense.

But I think I have made a mistake somewhere, and still need your advice.

Here is the register form: http://nnvawi.org/register.cfm

The form does not throw an error -- but no matter which registration option I choose, the column "ConferenceFeeType" in table NNVAWIRegister is populated with value "0".

It seems like the insert statement should populate column "ConferenceFeeTypeID" with the correct value from table ConferenceFeeType. Instead, only value "0" is populated. What do you think I am doing wrong?

Thank you again for your help.

Eric

Table ConferenceFeeType:

ConferenceFeeTypeID    	ConferenceFeeTitle    Fee
1 	Current NNVAWI Member	300.00
2	 Join / Renew and Registration	400.00
3	 Non-Member	415.00
4	 Full-time Student	120.00
5	 Thursday, April 9	200.00
6	Friday, April 10	200.00
7	Saturday, April 11	200.00

Open in new window


query to insert data into table NNVAWIRegister:

<!--- query to insert new record into registration table --->
			<cfquery name="InsertPage" datasource="#application.datasource#" result="newRegistrant">
				 INSERT INTO #REQUEST.RegisterTable#
     					(
Title
,FirstName
,MiddleInitial
,LastName
,Credentials
,Organization
,Address
,City
,State
,ZIP
,Country
,TelephoneHome
,TelephoneWork
,TelephoneMobile
,FAX
,UserEmail
,AltUserEmail
,SpecialRequirements
,ConferenceFeeType
,DateCreated
                        )
                        
                        
			     VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Title,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.FirstName,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.MiddleInitial,5))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.LastName,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Credentials,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Organization,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Address,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.City,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.State,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.ZIP,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Country,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.TelephoneHome,25))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.TelephoneWork,25))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.TelephoneMobile,25))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.FAX,25))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.UserEmail,128))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.AltUserEmail,128))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.SpecialRequirements,255))#">,
<cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ConferenceFeeTypeID)#">,
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
)         
					</cfquery>

Open in new window


The HTML in the form:

<h2>Payment Options</h2>

<p><em>All fees are payable in U.S. Funds.</em></p>

<cfquery name="getConferenceTypes" datasource="#application.datasource#"> 
    SELECT ConferenceFeeTypeID, ConferenceFeeTitle, Fee
    FROM ConferenceFeeType
</cfquery>

<table width="40%" summary="Early Bird Full Conference Fee for NNVAWI Conference, April 9 - 11, 2015">
  <caption>
    <h3>Early Bird Full Conference Fee</h3>
  </caption>

<cfloop query="getConferenceTypes">

      <tr>
    <td><cfoutput>#getConferenceTypes.ConferenceFeeTitle#</cfoutput></td>
    <td><cfoutput>#getConferenceTypes.Fee#</cfoutput></td>
	<td><input type="radio" name="ConferenceFeeTypeID" value="#getConferenceTypes.ConferenceFeeTypeID#" class="border0"></td>
     </tr>

</cfloop>
                                      


</table>

Open in new window

0
 
_agx_Commented:
EDIT:  

Morning!  I think it is just a matter of a missing output tag.  Looking at the source, the button values are literally:

        value="#getConferenceTypes.ConferenceFeeTypeID#"

(Instead of the ID value in that column ie 1,2,3,...)  The reason it's inserting 0, instead of throwing an error is because the cfqueryparam code uses VAL(), which automatically converts that non-numeric string to 0.  


Either move the cfoutput tags so they include the <input> tags, or use <cfoutput query> instead. That should fix it.

<!--- change CFLOOP to CFOUTPUT Quer --->
<cfoutput query="getConferenceTypes">
    <tr><td>#getConferenceTypes.ConferenceFeeTitle#</cfoutput></td>
          <td>#getConferenceTypes.Fee#</td>
          <td><input type="radio" name="ConferenceFeeTypeID" 
                      value="#getConferenceTypes.ConferenceFeeTypeID#" class="border0">
          </td>
    </tr>
</cfoutput>

Open in new window



Just dump the #FORM# scope on submit, and check that field, to verify you're getting the correct values after the change.
0
 
Eric BourlandAuthor Commented:
That is working. The Register form populates the database with the correct values now. The CFOUTPUT was what I needed.

This worked on the Register page:

<h2>Conference Fees</h2>

<cfquery name="getConferenceTypes" datasource="#application.datasource#"> 
    SELECT ConferenceFeeTypeID, ConferenceFeeTitle, Fee
    FROM ConferenceFeeType
</cfquery>

<table width="40%" summary="Conference Fee Schedule for NNVAWI Conference, April 9 - 11, 2015">
  <caption>
    <h3>Conference Fees</h3>
  </caption>

<cfloop query="getConferenceTypes">
<cfoutput>
      <tr>
    <td>#getConferenceTypes.ConferenceFeeTitle#</td>
    <td>#getConferenceTypes.Fee#</td>
	<td><input type="radio" name="ConferenceFeeTypeID" value="#getConferenceTypes.ConferenceFeeTypeID#" class="border0"></td>
     </tr>
</cfoutput>
</cfloop>                
</table>

Open in new window


I am working on the Edit page now. On the Edit page I am not sure how to display the conference fee option that the user selected with the Register Form. It seems like the Edit form will need to request the integer value ( 1 - 7 ) from table NNVAWIRegister, and also the other conference fee information from table ConferenceFeeType.

Maybe something like this:

<table width="40%" summary="Conference Fee Schedule for NNVAWI Conference, April 9 - 11, 2015">
  <caption>
    <h3>Conference Fees</h3>
  </caption>

<cfloop query="getConferenceTypes">
<cfoutput>
      <tr>
    <td>#getConferenceTypes.ConferenceFeeTitle#</td>
    <td>#getConferenceTypes.Fee#</td>
	<td><input type="radio" name="ConferenceFeeTypeID" value="#getConferenceTypes.ConferenceFeeTypeID#" class="border0"  <CFIF val(editRegister.ConferenceFeeType) IS NOT '0'>checked</CFIF> ></td>
     </tr>
</cfoutput>
</cfloop>                
</table>
      

Open in new window


Variable editRegister.ConferenceFeeType will be a value 1 - 7 depending on which ConferenceFeeType was selected in the register form. I am not sure how to tell the Edit form to find which value was selected.

I hope I am making sense. Thank you again for your help.

Eric
0
 
_agx_Commented:
(Edit) Inside the loop, use the cfif to compare it to the query value. Something along these lines...


<CFIF val(editRegister.ConferenceFeeType) EQ getConferenceTypes.ConferenceFeeTypeID>checked</CFIF> 

Open in new window

0
 
Eric BourlandAuthor Commented:
That's it!

I needed the EQ.

Working like a charm.
0
 
Eric BourlandAuthor Commented:
This makes a lot of sense. And it's a very useful way to populate a form with radio buttons.

Thanks as always to _agx_.

The next part of this task is figuring out how to pass payment variables from this form into the authorize.net payment API. I've been studying that -- supposedly it is "simple". =) I might ask a new question soon.

Thank you, _agx_. Stay cool. =) Hope your day is going great.

Eric
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now