Solved

Three errors in CF application; probably easy (for an expert) to solve

Posted on 2008-06-16
16
479 Views
Last Modified: 2010-04-21
Three errors in CF application; probably easy to solve

ColdFusion 8
MS SQL Server 2005

Hi. I'm getting three errors in a simple registration form for a conference. Users will populate the FORM with data and the data goes into a MS SQL server table (over HTTPS).

The three errors occur when I try to populate these three fields in the table:

CheckEnclosed (datatype bit, not null)
AuthorizeCharge (datatype bit, not null)
DateCreated (datatype datetime, nulls allowed)

The fields CheckEnclosed and AuthorizeCharge are populated by FORM checkboxes.

Obviously, if the FORM checkboxes are not checked, the CF gives me variable undefined errors. I understand why that is happening -- b/c the datatype will not accept a NULL value.

I could just allow NULLs, right? But, what's a better way to do this? How should I handle checkboxes? The user does not necessarily HAVE to check them.

Finally, I am trying to populate DateCreated automatically using <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">. ColdFusion objects to this. It gives me this error:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting the varchar value 'on' to data type bit.
 
The error occurred in D:\websites\ascassociation.org\conference_data_insert.cfm: line 90

88 :                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardZIPCode#">,
89 :                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#CVV#">,
90 :                         <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
91 :         )
92 : </cfquery>

SQLSTATE         22018
SQL          INSERT INTO RegistrationEntries (Name, Degree, Title, FacilityCompany, Address1, Address2, City, State, Zip, Phone, Fax, Email, WebSite, MainConference1stAttendeeAmountBefore8Sep625, MainConference2ndAttendeeAmountBefore8Sep575, MainConference3rdAttendeeAmountBefore8Sep525, MainConference4thAttendeeAmountBefore8Sep500, MainConference1stAttendeeAmountAfter8Sep725, MainConference2ndAttendeeAmountAfter8Sep675, MainConference3rdAttendeeAmountAfter8Sep625, MainConference4thAttendeeAmountAfter8Sep600, MainandPreConference1stAttendeeAmountBefore8Sep825, MainandPreConference2ndAttendeeAmountBefore8Sep775, MainandPreConference3rdAttendeeAmountBefore8Sep725, MainandPreConference4thAttendeeAmountBefore8Sep700, MainandPreConference1stAttendeeAmountAfter8Sep975, MainandPreConference2ndAttendeeAmountAfter8Sep875, MainandPreConference3rdAttendeeAmountAfter8Sep825, MainandPreConference4thAttendeeAmountAfter8Sep800, BeckersASCReviewSpecialDiscount, ASCReviewSubscriberDiscountFifty, TotalEnclosed, CheckEnclosed, CheckNumber, AuthorizeCharge, CreditCard, CreditCardNumber, ExpirationDate, PrintedCardHolderName, CreditCardZIPCode, CVV, DateCreated) VALUES( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) , (param 10) , (param 11) , (param 12) , (param 13) , (param 14) , (param 15) , (param 16) , (param 17) , (param 18) , (param 19) , (param 20) , (param 21) , (param 22) , (param 23) , (param 24) , (param 25) , (param 26) , (param 27) , (param 28) , (param 29) , (param 30) , (param 31) , (param 32) , (param 33) , (param 34) , (param 35) , (param 36) , (param 37) , (param 38) , (param 39) , (param 40) , (param 41) , (param 42) )
VENDORERRORCODE         245
DATASOURCE         ebwebwork

Any suggestions to resolve my three errors?

As always, thanks very much.

Eric

I attach two CFM files: chicagoOct2008.cfm and conference_data_insert.cfm
chicagoOct2008.cfm:
 

<cfinclude template="/SiteHeader.cfm">
 

<h1> Ambulatory Surgery Centers</h1>

<h2>Improving Profitability &#8212; Critical Business, Strategic, Clinical and Legal Issues 2008</h2>

<h3>OCTOBER 23 &#8211; 25, 2008</h3>
 
 
 
 

<!--- If the user is filling out and submitting the conference registration form ... --->

<cfif isDefined("FORM.subject")>
 

 

 <!--- Display "success" message to user --->
 
 

 <h2>Thank you. Your conference registration information has been submitted securely to ASC Association.</h2>

  <p>If you have questions or concerns about your registration, please contact the ASC Association at <a href="mailto:ASC@ascassociation.org">ASC@ascassociation.org</a>.</p>
 

<p>For information on exhibiting and sponsorships, call (800) 417-2035.</p>
 

  <p>Here is the information you sent:</p>

  
 

<cfoutput>

<p>

Name: #FORM.Name#<br />

Degree: #FORM.Degree#<br />

Title: #FORM.Title#<br />

Facility/Company: #FORM.FacilityCompany#<br />

Address1: #FORM.Address1#<br />

Address 2: #FORM.Address2#<br />

City: #FORM.City#<br />

State: #FORM.State#<br />

ZIP: #FORM.Zip#<br />

Phone: #FORM.Phone#<br />

Fax: #FORM.Fax#<br />

Email: #FORM.Email#<br />

Web Site: #FORM.WebSite#</p>
 

<p><strong>Main Conference Only:</strong></p>
 

<p>1st Attendee (paid before 8 Sep 2008): #FORM.MainConference1stAttendeeAmountBefore8Sep625#<br />

2nd Attendee (paid before 8 Sep 2008): #FORM.MainConference2ndAttendeeAmountBefore8Sep575#<br />

3rd Attendee (paid before 8 Sep 2008): #FORM.MainConference3rdAttendeeAmountBefore8Sep525#<br />

4th Attendee (paid before 8 Sep 2008): #FORM.MainConference4thAttendeeAmountBefore8Sep500#</p>
 

<p>1st Attendee (paid after 8 Sep 2008): #FORM.MainConference1stAttendeeAmountAfter8Sep725#<br />

2nd Attendee (paid after 8 Sep 2008): #FORM.MainConference2ndAttendeeAmountAfter8Sep675#<br />

3rd Attendee (paid after 8 Sep 2008): #FORM.MainConference3rdAttendeeAmountAfter8Sep625#<br />

4th Attendee (paid after 8 Sep 2008): #FORM.MainConference4thAttendeeAmountAfter8Sep600#</p>
 

<p><strong>Main Conference + Pre-Conference</strong></p>
 

<p>1st Attendee (paid before 8 Sep 2008): #FORM.MainandPreConference1stAttendeeAmountBefore8Sep825#<br />

2nd Attendee (paid before 8 Sep 2008): #FORM.MainandPreConference2ndAttendeeAmountBefore8Sep775#<br />

3rd Attendee (paid before 8 Sep 2008): #FORM.MainandPreConference3rdAttendeeAmountBefore8Sep725#<br />

4th Attendee (paid before 8 Sep 2008): #FORM.MainandPreConference4thAttendeeAmountBefore8Sep700#</p>
 

<p>1st Attendee (paid after 8 Sep 2008): #FORM.MainandPreConference1stAttendeeAmountAfter8Sep975#<br />

2nd Attendee (paid after 8 Sep 2008): #FORM.MainandPreConference2ndAttendeeAmountAfter8Sep875#<br />

3rd Attendee (paid after 8 Sep 2008): #FORM.MainandPreConference3rdAttendeeAmountAfter8Sep825#<br />

4th Attendee (paid after 8 Sep 2008): #FORM.MainandPreConference4thAttendeeAmountAfter8Sep800#</p>
 
 

<p>Becker's ASC Review Special Discount: #FORM.BeckersASCReviewSpecialDiscount#</p>
 

<p>ASC Review Subscriber or ASC Association Member Discount $50: #FORM.ASCReviewSubscriberDiscountFifty#</p>
 

<p>Total Enclosed: #FORM.TotalEnclosed#</p>
 

<p>Form Printed, Check Enclosed: #FORM.CheckEnclosed#</p>

<p>Check Number: #FORM.CheckNumber#</p>

<p>Authorize Credit Card Charge: #FORM.AuthorizeCharge#</p>
 
 

<p><strong>Credit Card information:</strong> <em>Obscured for security.</em></p>

</cfoutput>

  
 

<!--- Otherwise, display the form to user... ---> 

<cfelse>
 

 <!--- Set up form to populate conference registration information --->
 
 

<!--- Add/update Page form --->

<cfform action="/conference_data_insert.cfm" enctype="multipart/form-data">

  
 
 

<h1>REGISTRATION INFORMATION</h1>
 

	<table class="uvatable">

	<tr><td><strong>Name:</strong></td>		<td><cfinput TYPE="text" SIZE="30" NAME="Name" /></td></tr>

		<tr><td><strong>Degree: As you wish it to appear on your badge:</strong></td>		<td><cfinput TYPE="text" SIZE="30" NAME="Degree" /></td></tr>

		<tr><td><strong>Title:</strong></td>		<td><cfinput TYPE="text" SIZE="30" NAME="Title" /></td></tr>

	<tr><td><strong>Facility / Company:</strong></td> 	<td><cfinput TYPE="text" SIZE="30" NAME="FacilityCompany" /></td></tr>

	<tr><td><strong>Address Line 1:</strong></td> 		<td><cfinput TYPE="text" SIZE="30" NAME="Address1" /></td></tr>

		<tr><td><strong>Address Line 2:</strong></td> 		<td><cfinput TYPE="text" SIZE="30" NAME="Address2" /></td></tr>

	<tr><td><strong>City:</strong></td> 		<td><cfinput TYPE="text" SIZE="30" NAME="City" /></td></tr>

	<tr><td><strong>State:</strong></td> 		<td><cfinput TYPE="text" SIZE="5" NAME="State" /></td></tr>	

	<tr><td><strong>ZIP:</strong></td> 		<td><cfinput TYPE="text" SIZE="5" NAME="Zip" /></td></tr>

	<tr><td><strong>Phone:</strong></td>		<td><cfinput TYPE="text" SIZE="20" NAME="Phone" /></td></tr>

	<tr><td><strong>Fax:</strong></td>		<td><cfinput TYPE="text" SIZE="20" NAME="Fax" /></td></tr>

	<tr><td><strong>Email:</strong></td>		<td><cfinput TYPE="text" SIZE="30" NAME="Email" /></td></tr>

		<tr><td><strong>Web Site:</strong></td>		<td><cfinput TYPE="text" SIZE="30" NAME="WebSite" /></td></tr>

	</table>

	

<h1>REGISTRATION FEES</h1>
 

	
 

<h2>ANNUAL CONFERENCE &#38; EXHIBITS</h2>

<p>Receive multiple registrant discount(s). The more people you send the greater discount you receive. The prices listed

below are per person. Your registration includes all conference sessions, materials, and the meal functions.</p>
 
 

<h2>MAIN CONFERENCE ONLY</h2>
 

	<table class="uvatable">

<tr>

	<td></td>

	<td><strong>FEES<br />(Before 9/10/08)</strong></td>

	<td><strong>ENTER AMOUNT</strong></td>

	<td><strong>FEES<br />(After 9/10/08)</strong></td>

	<td><strong>ENTER AMOUNT</strong></td>
 

</tr>

<tr>

	<td>1st Attendee</td>

	<td>$625</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainConference1stAttendeeAmountBefore8Sep625" /></td>

	<td>$725</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainConference1stAttendeeAmountAfter8Sep725" /></td>
 

</tr>

<tr>

	<td>2nd Attendee</td>

	<td>$575</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainConference2ndAttendeeAmountBefore8Sep575" /></td>

	<td>$675</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainConference2ndAttendeeAmountAfter8Sep675" /></td>

</tr>

<tr>

	<td>3rd Attendee</td>

	<td>$525</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainConference3rdAttendeeAmountBefore8Sep525" /></td>

	<td>$625</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainConference3rdAttendeeAmountAfter8Sep625" /></td>
 

</tr>

<tr>

	<td>4th Attendee or more</td>

	<td>$500</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainConference4thAttendeeAmountBefore8Sep500" /></td>

	<td>$600</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainConference4thAttendeeAmountAfter8Sep600" /></td>
 

</tr>

</table>
 

<p><em>Ask about larger group discounts.</em></p>
 
 

<h2>MAIN CONFERENCE + PRE-CONFERENCE</h2>
 

	<table class="uvatable">

<tr>

	<td></td>

	<td><strong>FEES<br />(Before 9/10/08)</strong></td>

	<td><strong>ENTER AMOUNT</strong></td>

	<td><strong>FEES<br />(After 9/10/08)</strong></td>

	<td><strong>ENTER AMOUNT</strong></td>
 

</tr>

<tr>

	<td>1st Attendee</td>

	<td>$825</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainandPreConference1stAttendeeAmountBefore8Sep825" /></td>

	<td>$975</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainandPreConference1stAttendeeAmountAfter8Sep975" /></td>
 

</tr>

<tr>

	<td>2nd Attendee</td>

	<td>$775</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainandPreConference2ndAttendeeAmountBefore8Sep775" /></td>

	<td>$875</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainandPreConference2ndAttendeeAmountAfter8Sep875" /></td>

</tr>

<tr>

	<td>3rd Attendee</td>

	<td>$725</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainandPreConference3rdAttendeeAmountBefore8Sep725" /></td>

	<td>$825</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainandPreConference3rdAttendeeAmountAfter8Sep825" /></td>
 

</tr>

<tr>

	<td>4th Attendee or more</td>

	<td>$700</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainandPreConference4thAttendeeAmountBefore8Sep700" /></td>

	<td>$800</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="MainandPreConference4thAttendeeAmountAfter8Sep800" /></td>
 

</tr>

</table>
 
 
 

<h2>OPTIONAL</h2>
 

<table class="uvatable">

<tr>

	<td>Becker's ASC Review Special Discount Offer</td>

	<td>$100 (per year) </td>

	<td><cfinput TYPE="text" SIZE="30" NAME="BeckersASCReviewSpecialDiscount" /></td>

</tr>

<tr>

	<td>50 Discount per attendee if paid ASC Association Member

or Becker's ASC Review Subscriber </td>

	<td>(- 50) </td>

	<td><cfinput TYPE="text" SIZE="30" NAME="ASCReviewSubscriberDiscountFifty" /></td>

</tr>

<tr>

	<td></td>

	<td><strong>TOTAL ENCLOSED</strong></td>

	<td><cfinput TYPE="text" SIZE="30" NAME="TotalEnclosed" /></td>

</tr>

</table>
 
 
 
 

<h1>PAYMENT INFORMATION</h1>
 

<p>To remit payment, you may print this form and mail it to ASC Association with a check (see information below). Or, submit your credit card information, below. Credit card information is submitted through a secure connection, certified by SecureTrust.</p>
 

<p class="float-left"><cfinput type="Checkbox" name="CheckEnclosed" /> Enclosed is a check, payable to ASC Association Fall Conference</p> 
 

<p class="float-right">Check # <cfinput TYPE="text" SIZE="30" NAME="CheckNumber" /></p>
 

<div class="clear-both"></div>
 

<p class="float-left"><cfinput type="Checkbox" name="AuthorizeCharge" /> I authorize ASC Association to charge my:</p>
 

<p class="float-right">

<select name="CreditCard">

<option value="SelectCard">Select a Credit Card</option>

<option value="Visa">Visa</option>

<option value="MasterCard">MasterCard</option>

<option value="AmericanExpress">American Express</option></select></p>
 
 

<div class="clear-both"></div>
 
 

<table class="uvatable">

<tr>

	<td>Credit Card Number:</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="CreditCardNumber" /></td>

	<td>Expiration Date:</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="ExpirationDate" /></td>

</tr>

<tr>

	<td>Printed Cardholder Name:</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="PrintedCardHolderName" /></td>

	<td>Zip Code:</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="CreditCardZIPCode" /></td>

</tr>

<tr>

	<td>Signature (if printing and mailing):</td>

	<td></td>

	<td>CVV#/3-digit #:</td>

	<td><cfinput TYPE="text" SIZE="30" NAME="CVV" /></td>

</tr>

</table>
 
 

<h1>TO REGISTER</h1>
 
 

<h2>COMPLETE REGISTRATION FORM AND MAIL OR FAX AS FOLLOWS:</h2>
 

<p><strong>Mail:</strong> Make checks payable to ASC Association Fall Conference and mail to:

ASC Association, 1012 Cameron St., Alexandria, VA 22314</p>
 

<p><strong>Fax:</strong> Fax registration form with credit card information to (703) 836-2090</p>
 

<p><strong>Call:</strong> Call (703) 836-5904 to register by phone</p>
 

<p><strong>Web site:</strong> <a href="http://www.BeckersASC.com">www.BeckersASC.com</a></p>
 

<p><strong>Cancellation policy:</strong> Written cancellation requests must be received by September 18, 2008. Refunds are subject to a 100 processing fee.

Refunds will not be made after this date.</p>
 

<p>Fax registration form with credit card information to (703) 836-2090.</p>

	

	

<cfinput type="submit" name="submit" value="Send to ASC Association" />
 
 

</cfform>
 

</cfif> 
 

<p>If you have trouble sending the form, please print and fax to 703-549-0976.</p>
 

	

<p>This information will only be used in conjunction with ASC Association business. We will not share your information with any other organizations.</p>
 
 
 

<cfinclude template="/SiteFooter.cfm">
 
 
 
 

conference_data_insert.cfm:
 

  <!--- Insert values into database columns --->

	

	<cfquery datasource="ebwebwork" dbname="ascassociation" name="createRegistration">

		  

INSERT INTO RegistrationEntries (Name,

Degree,

Title,

FacilityCompany,

Address1,

Address2,

City,

State,

Zip,

Phone,

Fax,

Email,

WebSite,

MainConference1stAttendeeAmountBefore8Sep625,

MainConference2ndAttendeeAmountBefore8Sep575,

MainConference3rdAttendeeAmountBefore8Sep525,

MainConference4thAttendeeAmountBefore8Sep500,

MainConference1stAttendeeAmountAfter8Sep725,

MainConference2ndAttendeeAmountAfter8Sep675,

MainConference3rdAttendeeAmountAfter8Sep625,

MainConference4thAttendeeAmountAfter8Sep600,

MainandPreConference1stAttendeeAmountBefore8Sep825,

MainandPreConference2ndAttendeeAmountBefore8Sep775,

MainandPreConference3rdAttendeeAmountBefore8Sep725,

MainandPreConference4thAttendeeAmountBefore8Sep700,

MainandPreConference1stAttendeeAmountAfter8Sep975,

MainandPreConference2ndAttendeeAmountAfter8Sep875,

MainandPreConference3rdAttendeeAmountAfter8Sep825,

MainandPreConference4thAttendeeAmountAfter8Sep800,

BeckersASCReviewSpecialDiscount,

ASCReviewSubscriberDiscountFifty,

TotalEnclosed,

CheckEnclosed,

CheckNumber,

AuthorizeCharge,

CreditCard,

CreditCardNumber,

ExpirationDate,

PrintedCardHolderName,

CreditCardZIPCode,

CVV,

DateCreated)

 VALUES(

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Name#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Degree#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Title#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#FacilityCompany#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Address1#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Address2#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#City#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#State#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Zip#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Phone#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Fax#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Email#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#WebSite#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference1stAttendeeAmountBefore8Sep625#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference2ndAttendeeAmountBefore8Sep575#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference3rdAttendeeAmountBefore8Sep525#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference4thAttendeeAmountBefore8Sep500#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference1stAttendeeAmountAfter8Sep725#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference2ndAttendeeAmountAfter8Sep675#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference3rdAttendeeAmountAfter8Sep625#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference4thAttendeeAmountAfter8Sep600#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference1stAttendeeAmountBefore8Sep825#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference2ndAttendeeAmountBefore8Sep775#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference3rdAttendeeAmountBefore8Sep725#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference4thAttendeeAmountBefore8Sep700#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference1stAttendeeAmountAfter8Sep975#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference2ndAttendeeAmountAfter8Sep875#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference3rdAttendeeAmountAfter8Sep825#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference4thAttendeeAmountAfter8Sep800#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#BeckersASCReviewSpecialDiscount#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#ASCReviewSubscriberDiscountFifty#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#TotalEnclosed#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CheckEnclosed#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CheckNumber#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#AuthorizeCharge#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCard#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardNumber#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#ExpirationDate#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PrintedCardHolderName#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardZIPCode#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CVV#">,

				<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

        )

</cfquery>

Open in new window

0
Comment
Question by:Eric Bourland
  • 12
  • 4
16 Comments
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
YOu have all of your cfsqltypes set to varchar

this will cause issues as cf is trying to match datatypes with the db and failing (which is what the error is telling you)

use the type appropriate to the field you are inserting

<cfqueryparam cfsqltype="cf_sql_bit" value="#form.CheckEnclosed#">


0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Aha. =)

Of course. I'll work on this and get back to you. Thank you.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Hi, Sid,

OK, in conference_data_insert.cfm, I changed my cf_sql_param values as you suggested:

 <cfqueryparam cfsqltype="cf_sql_bit" value="#CheckEnclosed#">,

 <cfqueryparam cfsqltype="cf_sql_bit" value="#AuthorizeCharge#">,

<cfqueryparam cfsqltype="cf_sql_datetime" value="#now()#">

* I'm still getting a Variable Undefined error if I process the form WITHOUT checking the CheckEnclosed and AuthorizeCharge checkboxes; I think I just need to allow NULLs for those database fields? Or is a better way to process form checkbox data?

*When I do check the checkboxes I get this error:

 Invalid data on for CFSQLTYPE CF_SQL_BIT.
 
The error occurred in D:\websites\ascassociation.org\conference_data_insert.cfm: line 90

88 :                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardZIPCode#">,
89 :                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#CVV#">,
90 :                         <cfqueryparam cfsqltype="cf_sql_datetime" value="#now()#">
91 :         )
92 : </cfquery>

So something is still wrong. It seems to be <cfqueryparam cfsqltype="cf_sql_datetime" value="#now()#">

So I changed that back to: <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

But that produces the same error. ColdFusion does not like how I am trying to process these variables: my checkboxes, and the datestamp I am trying to enter.

Any ideas? Thanks very much, again.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Hi, Sid,

Did you get any time to think further on this problem? I've been working on it for a few hours, trying different datatypes, but I still cannot get this form to process and this application to populate my table.

Sorry to bug you. I've got an impatient client I am trying please. =)

I really appreciate your time.

Peace,

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
>>>Conversion failed when converting the varchar value 'on' to data type bit.

Also, I think there's something more to this than just setting <cfqueryparam cfsqltype="cf_sql_bit" value="#CheckEnclosed#">.

I still get this error: Invalid data on for CFSQLTYPE CF_SQL_BIT

So I believe that means my Checkbox in my form is trying to send the data "on" to a datatype set to "bit" -- will that work?

I'm still not sure how to handle checkboxes in a form. This, I think, is the crux of the matter.

What do you think?

Thanks again.

Eric
0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
been busy... just checked back

it's really a 2 part problem...the cfsqltype and you're right about the on

you can handle that by simply adding this before your query


<cfid form.checkenclosed eq "on">
<cfset vCheckEnclosed =1>
<cfelse>
<cfset  vCheckEnclosed = 0>
</cfif>

then just use the variable vCheckEnclosed in your cfquerparam...

(and repeat for the other form checkbox vars)

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Cool. Thank you! Sorry to interrupt your work day. I've been pretty swamped myself. =)

I'll try this out and get back to you soon.

Have a great evening.

eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Hi, Sid,

I am making progress on this. I did as you suggested, and I am still getting this error:


 Invalid token . found on line 1 at column 11.

The CFML compiler was processing:

    * The tag attribute form, on line 1, column 7.
    * A cfid tag beginning on line 1, column 2.

 
The error occurred in D:\websites\ascassociation.org\conference_data_insert.cfm: line 1

1 : <cfid form.checkenclosed eq "on">
2 : <cfset vCheckEnclosed =1>
3 : <cfelse>


I attach my current conference_data_insert.cfm file ... am I doing this correctly?

Why do we change CheckEnclosed  to vCheckEnclosed ?

Many thanks, again. Hope your day is going well.

Eric
conference_data_insert.cfm:
 

<cfid form.checkenclosed eq "on">

<cfset vCheckEnclosed =1>

<cfelse>

<cfset vCheckEnclosed = 0>

</cfif>
 

<cfid form.AuthorizeCharge eq "on">

<cfset vAuthorizeCharge =1>

<cfelse>

<cfset vAuthorizeCharge = 0>

</cfif>
 

  <!--- Insert values into database columns --->

	

	<cfquery datasource="ebwebwork" dbname="ascassociation" name="createRegistration">

		  

INSERT INTO RegistrationEntries (Name,

Degree,

Title,

FacilityCompany,

Address1,

Address2,

City,

State,

Zip,

Phone,

Fax,

Email,

WebSite,

MainConference1stAttendeeAmountBefore8Sep625,

MainConference2ndAttendeeAmountBefore8Sep575,

MainConference3rdAttendeeAmountBefore8Sep525,

MainConference4thAttendeeAmountBefore8Sep500,

MainConference1stAttendeeAmountAfter8Sep725,

MainConference2ndAttendeeAmountAfter8Sep675,

MainConference3rdAttendeeAmountAfter8Sep625,

MainConference4thAttendeeAmountAfter8Sep600,

MainandPreConference1stAttendeeAmountBefore8Sep825,

MainandPreConference2ndAttendeeAmountBefore8Sep775,

MainandPreConference3rdAttendeeAmountBefore8Sep725,

MainandPreConference4thAttendeeAmountBefore8Sep700,

MainandPreConference1stAttendeeAmountAfter8Sep975,

MainandPreConference2ndAttendeeAmountAfter8Sep875,

MainandPreConference3rdAttendeeAmountAfter8Sep825,

MainandPreConference4thAttendeeAmountAfter8Sep800,

BeckersASCReviewSpecialDiscount,

ASCReviewSubscriberDiscountFifty,

TotalEnclosed,

CheckEnclosed,

CheckNumber,

AuthorizeCharge,

CreditCard,

CreditCardNumber,

ExpirationDate,

PrintedCardHolderName,

CreditCardZIPCode,

CVV,

DateCreated)

 VALUES(

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Name#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Degree#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Title#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#FacilityCompany#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Address1#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Address2#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#City#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#State#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Zip#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Phone#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Fax#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Email#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#WebSite#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference1stAttendeeAmountBefore8Sep625#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference2ndAttendeeAmountBefore8Sep575#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference3rdAttendeeAmountBefore8Sep525#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference4thAttendeeAmountBefore8Sep500#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference1stAttendeeAmountAfter8Sep725#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference2ndAttendeeAmountAfter8Sep675#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference3rdAttendeeAmountAfter8Sep625#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference4thAttendeeAmountAfter8Sep600#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference1stAttendeeAmountBefore8Sep825#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference2ndAttendeeAmountBefore8Sep775#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference3rdAttendeeAmountBefore8Sep725#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference4thAttendeeAmountBefore8Sep700#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference1stAttendeeAmountAfter8Sep975#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference2ndAttendeeAmountAfter8Sep875#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference3rdAttendeeAmountAfter8Sep825#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference4thAttendeeAmountAfter8Sep800#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#BeckersASCReviewSpecialDiscount#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#ASCReviewSubscriberDiscountFifty#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#TotalEnclosed#">,

                <cfqueryparam cfsqltype="cf_sql_bit" value="#vCheckEnclosed#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CheckNumber#">,

                <cfqueryparam cfsqltype="cf_sql_bit" value="#vAuthorizeCharge#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCard#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardNumber#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#ExpirationDate#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PrintedCardHolderName#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardZIPCode#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CVV#">,

				<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

        )

</cfquery>

		  
 
 
 

<!--- When done go chicagoOct2008received.cfm--->

<cflocation url="/chicagoOct2008received.cfm">

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Wait, I see something. cfid should be cfif.

hang on....
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Hi Sid,

Making further progress.

I added:

<cfif form.checkenclosed eq "on">
<cfset vCheckEnclosed =1>
<cfelse>
<cfset  vCheckEnclosed = 0>
</cfif>

to conference_data_insert.cfm

Now I get this error when I process the form:

 Element CHECKENCLOSED is undefined in FORM.
 
The error occurred in D:\websites\ascassociation.org\conference_data_insert.cfm: line 1

1 : <cfif form.checkenclosed eq "on">
2 : <cfset vCheckEnclosed =1>
3 : <cfelse>

I'm still not clear why we changed CheckEnclosed to vCheckEnclosed?

Thanks again. I'm learning a ton here. I'm very grateful.

Eric
0
 
LVL 36

Accepted Solution

by:
SidFishes earned 500 total points
Comment Utility
sorry ... when checkboxes are in a form they are only passed if they are checked ("on")

you can view this behaviour in this code

<cfif isdefined('form.test')>
<cfdump var="#form#">
</cfif>
<form method="post" action="index.cfm">
<Input type="checkbox" name="CheckEnclosed ">
<input type="submit" name="test">
</form>


we need to modify the cfif to this

<cfif isDefined('form.checkenclosed')><!--- box has been checked and is present in post data --->
<cfset vCheckEnclosed =1>
<cfelse>
<cfset  vCheckEnclosed = 0><!--- not checked and not present --->
</cfif>

in fact you could add the whole cfif block in the cfqueryparam value attribute

<cfqueryparam... value="<cfif....</cif>">

but that makes for very hard to read code.
I prefer to do the logic outside the query block and set a simple local variable



In cf you can do a lot of things in naming variables

<cfif isDefined('form.checkenclosed')>
<cfset form.CheckEnclosed =1><!--- reset the value from on to 1  for your  bit field --->
<cfelse>
<cfset  form.CheckEnclosed = 0><!--- here we actually create the variable for the insert as it doesn't exist yet --->
</cfif>


you could also just

<cfif isDefined('form.checkenclosed')>
<cfset CheckEnclosed =1>

the reason we added a v is simply a habit of mine (I think it's a good one) It notifies me that it is a local variable.













0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Interesting.

OK I am trying this. I'll get back soon.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Sid,

I'm getting there. Your solution above took care of the Checkboxes and I see now how to deal with Checkboxes.

I like the idea of adding a v to indicate a local variable.

I'm getting another error:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'RegistrationID', table 'ascassociation.dbo.RegistrationEntries'; column does not allow nulls. INSERT fails.
 
The error occurred in D:\websites\ascassociation.org\conference_data_insert.cfm: line 102

100 :                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardZIPCode#">,
101 :                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#CVV#">,
102 :                         <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
103 :         )
104 : </cfquery>


I have set column 'RegistrationID' to be Primary Key. Its datatype is integer. It is set to notnull. I would like to to auto-increment each time a new record is added.

I thought I had set up my CF code to make that happen. Do you have any idea what I am doing wrong?

Also -- if this counts as a new question I will close this question, gratefully give you the points, and start a new question.

Thanks very much again. I really appreciate your help.

Eric
<cfif isDefined('form.checkenclosed')><!--- box has been checked and is present in post data --->

<cfset vCheckEnclosed =1>

<cfelse>

<cfset  vCheckEnclosed = 0><!--- not checked and not present --->

</cfif>
 
 

<cfif isDefined('form.checkenclosed')><!--- box has been checked and is present in post data --->

<cfset vAuthorizeCharge = 1>

<cfelse>

<cfset vAuthorizeCharge = 0><!--- not checked and not present --->

</cfif>
 

  <!--- Insert values into database columns --->

	

	<cfquery datasource="ebwebwork" dbname="ascassociation" name="createRegistration">

		  

INSERT INTO RegistrationEntries (Name,

Degree,

Title,

FacilityCompany,

Address1,

Address2,

City,

State,

Zip,

Phone,

Fax,

Email,

WebSite,

MainConference1stAttendeeAmountBefore8Sep625,

MainConference2ndAttendeeAmountBefore8Sep575,

MainConference3rdAttendeeAmountBefore8Sep525,

MainConference4thAttendeeAmountBefore8Sep500,

MainConference1stAttendeeAmountAfter8Sep725,

MainConference2ndAttendeeAmountAfter8Sep675,

MainConference3rdAttendeeAmountAfter8Sep625,

MainConference4thAttendeeAmountAfter8Sep600,

MainandPreConference1stAttendeeAmountBefore8Sep825,

MainandPreConference2ndAttendeeAmountBefore8Sep775,

MainandPreConference3rdAttendeeAmountBefore8Sep725,

MainandPreConference4thAttendeeAmountBefore8Sep700,

MainandPreConference1stAttendeeAmountAfter8Sep975,

MainandPreConference2ndAttendeeAmountAfter8Sep875,

MainandPreConference3rdAttendeeAmountAfter8Sep825,

MainandPreConference4thAttendeeAmountAfter8Sep800,

BeckersASCReviewSpecialDiscount,

ASCReviewSubscriberDiscountFifty,

TotalEnclosed,

CheckEnclosed,

CheckNumber,

AuthorizeCharge,

CreditCard,

CreditCardNumber,

ExpirationDate,

PrintedCardHolderName,

CreditCardZIPCode,

CVV,

DateCreated)

 VALUES(

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Name#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Degree#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Title#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#FacilityCompany#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Address1#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Address2#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#City#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#State#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Zip#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Phone#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Fax#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Email#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#WebSite#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference1stAttendeeAmountBefore8Sep625#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference2ndAttendeeAmountBefore8Sep575#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference3rdAttendeeAmountBefore8Sep525#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference4thAttendeeAmountBefore8Sep500#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference1stAttendeeAmountAfter8Sep725#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference2ndAttendeeAmountAfter8Sep675#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference3rdAttendeeAmountAfter8Sep625#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainConference4thAttendeeAmountAfter8Sep600#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference1stAttendeeAmountBefore8Sep825#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference2ndAttendeeAmountBefore8Sep775#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference3rdAttendeeAmountBefore8Sep725#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference4thAttendeeAmountBefore8Sep700#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference1stAttendeeAmountAfter8Sep975#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference2ndAttendeeAmountAfter8Sep875#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference3rdAttendeeAmountAfter8Sep825#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#MainandPreConference4thAttendeeAmountAfter8Sep800#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#BeckersASCReviewSpecialDiscount#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#ASCReviewSubscriberDiscountFifty#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#TotalEnclosed#">,

                <cfqueryparam cfsqltype="cf_sql_bit" value="#vCheckEnclosed#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CheckNumber#">,

                <cfqueryparam cfsqltype="cf_sql_bit" value="#vAuthorizeCharge#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCard#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardNumber#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#ExpirationDate#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PrintedCardHolderName#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CreditCardZIPCode#">,

                <cfqueryparam cfsqltype="cf_sql_varchar" value="#CVV#">,

				<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

        )

</cfquery>

		  
 
 
 

<!--- When done go chicagoOct2008received.cfm--->

<cflocation url="/chicagoOct2008received.cfm">

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Sid, never mind. I fixed it. I set Identity to true, and identity seed to 1. That solved the problem. I'm going to close this question. Thanks very much for your help.

Peace.

Eric
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
Thanks SidFishes!
0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
glad to help
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now