Need some quick help!

farscp1 · Oct 20, 2005

I am trying to finalize an online registration process. The form consists of checkboxes and text fields. Email is being used as the PK and a value of aap2@nyu.edu was entered into the form. The values are passed to an action form which contains the following queries:

Code:

<!--- Initialize a variable called Continue to control the transaction --->
<CFSET Continue = "Yes">

<!--- begin transaction --->
<CFTRANSACTION ACTION="BEGIN">
 
<!--- Insert the new record --->
<cftry>
	<cfquery name="addAttendees" datasource="seligins">
		INSERT INTO tblAttendee 
			(h_email, firstname, l......, etc.) 
		VALUES 
			('#Form.h_email#', '#Form.firstname#', '#Form.lastname#', ....., etc.)
	</cfquery>
	
	<CFCATCH TYPE="Database">
    <CFTRANSACTION ACTION="ROLLBACK"/>
    <CFSET ProblemQuery = "addAttendees">
    <CFSET Continue = "No">
</CFCATCH>
</cftry>

<!--- Insert registration & payment status--->
<CFIF Continue EQ "Yes">
<CFTRY>
	<CFTRANSACTION ACTION="COMMIT"/>
	<cfquery name="addRegs" datasource="seligins">
		INSERT INTO tblAttendee_registration 
			(h_email, payment_status) 
		VALUES 
			('#Form.h_email#', 'paid')
	</cfquery>
	
	<CFCATCH TYPE="Database">
		<CFTRANSACTION ACTION="ROLLBACK"/>
		<CFSET ProblemQuery = "addRegs">
		<CFSET Continue = "No">
	</cfcatch>

</cftry>
</cfif>

<!--- if the record was successfully added, commit the transaction --->
<CFIF Continue EQ "Yes">
    <CFTRANSACTION ACTION="COMMIT"/>
</CFIF>
</CFTRANSACTION> 

<CFIF Continue EQ "Yes">
	<!--- Insert course details --->
	<cfset thisList = "#Form.course_id#">
	<cfloop list = "#thisList#" index="nthisList">
		<cfquery name="insertCourse" datasource="seligins">
  INSERT INTO tblRegistration_details
   (h_email, course_id) 
  VALUES
   ('#Form.h_email#', '#nthisList#')
   </cfquery>
  </cfloop>
</cfif>
	
<CFIF Continue EQ "Yes">
 
	<cfquery name="getReg" datasource="seligins">
		SELECT b.firstname, b.lastname, a.course_date, a.course_name
		FROM tblCourse a, tblAttendee b, tblRegistration_details d
		WHERE a.course_id = d.course_id
		AND #Form.h_email# = b.h_email
	</cfquery>
	
		<html>
		<head>
			<link rel="stylesheet" type="text/css" href="seligtest.css">
			<title>Registration Submitted!</title>
			<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
		</head>
	
		<body>
		<p></p>
		<p></p>
		<table width="250" cellspacing="0" cellpadding="0">
	
	<cfoutput query="getReg">
		<h3>
			#firstname# #lastname#, here are your registration details:<br>
		</h3>
			  <tr>
				<th scope="col">#course_date#</th>
				<th scope="col">#course_name#</th>
			  </tr>
		</table>	
	</cfoutput>
	
<CFELSE>
	<CFOUTPUT>
	<H2>An Error has occurred.  All queries have been rolled back</H2>
	<B>The query that caused the error is: <I>#ProblemQuery#</I>.</B>
	</CFOUTPUT>
</CFIF>

But I keep getting the following error when I try to join the #Form.h_email# (PK) with the other tables to preview registration details.

Here is the error message:

[Macromedia][SQLServer JDBC Driver][SQLServer]The column prefix 'aap2@nyu' does not match with a table name or alias name used in the query.
The error occurred in \onlinereg_action.cfm: line 71
69 : FROM tblCourse a, tblAttendee b, tblRegistration_details d
70 : WHERE a.course_id = d.course_id
71 : AND #Form.h_email# = b.h_email
72 : </cfquery>
73 :

SQL SELECT b.firstname, b.lastname, a.course_date, a.course_name FROM tblCourse a, tblAttendee b, tblRegistration_details d WHERE a.course_id = d.course_id AND aap2@nyu.edu = b.h_email

farscp1 · Oct 20, 2005

I figured it out myself, DUH!, I forgot the '' for #Form.h_email#.

pigsie · Oct 21, 2005

One thing I should add is that by default if you have multiple queries inside a transaction block and there is an error, the transaction (i.e. all queries) are rolled back therefore you can get rid of the try and catch unless you want to do something else when an error occurs such as log it or send an email.

HTH

ecobb · Oct 21, 2005

Another thing is that you have your AND clause backwards:

Code:

WHERE a.course_id = d.course_id
AND '#Form.h_email#' = b.h_email

Should Be

WHERE a.course_id = d.course_id
AND b.h_email = '#Form.h_email#'

You should always list your database columns first, then your variables second. I've never seen any rules or documents saying that you have to do it this way, but it's pretty much a coding standard and you will rarely see anyone do otherwise.

Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

TruthInSatire · Oct 21, 2005

Mainly because that's the way you set a variable, "name" then "Dadum" order.

the proper syntax
<cfset identifier = "value">
var identifier = "value"
dim identifier = "value"

<cfset "value" = identifier>
would give you unexpected results(assuming identifier has already been defined) if not an error, the other 2 would give you an error.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

r937 · Oct 21, 2005

yeah, but really, an assignment is hardly the same operator as equality

which is why some languages use == or := instead of = for assignment

equality is reflexive ([/i]it had better be!!![/i] ) and therefore this --

AND '#Form.h_email#' = b.h_email

is perfectly valid and i will defend it

r937.com | rudy.ca

farscp1 · Oct 21, 2005

Thanks to all of you for your feedback, they're very helpful. Thanks,

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need some quick help!

farscp1

Programmer

farscp1

Programmer

pigsie

Programmer

ecobb

Programmer

TruthInSatire

Programmer

r937

Technical User

farscp1

Programmer

Similar threads

Part and Inventory Search

Sponsor