asked on

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

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

SidFishes

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#">

Eric Bourland

ASKER

Aha. =)

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

Eric

Eric Bourland

ASKER

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

Eric Bourland

ASKER

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

Eric Bourland

ASKER

>>>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

SidFishes

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)

Eric Bourland

ASKER

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

Eric Bourland

ASKER

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

Eric Bourland

ASKER

Wait, I see something. cfid should be cfif.

hang on....

Eric Bourland

ASKER

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

ASKER CERTIFIED SOLUTION

SidFishes

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Eric Bourland

ASKER

Interesting.

OK I am trying this. I'll get back soon.

Eric Bourland

ASKER

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

Eric Bourland

ASKER

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

Eric Bourland

ASKER

Thanks SidFishes!

SidFishes

glad to help