Session variables and sql...

[P_Roberts]

hi guys, right made big progress since i was last here but now I'm getting a
"syntax error in INSERT INTO statement" message....when trying to insert the
Session variables into the database...i've checked and double checked and can't
see anything wrong...used cfdump and all the variables are there ready to
go....any clues?

here's the error...

Error Executing Database Query.
Syntax error in INSERT INTO statement.

The error occurred in C:\CFusionMX7\wwwroot\ukdoorstaff\activateprocess. cfm:
line 40

38 : #Session.regPhoneNumber#,
39 : '#Session.regHearAboutUs#',
40 : '#Session.regSuggestions#'
41 : )
42 : </cfquery>



--------------------------------------------------------------------------------

SQL INSERT INTO dRegDetails (dUserName, dPassword, dFirstName, dSurName,
dEmail, dDOB, dGender, dSIA, dAddress1, dAddress2, dTownCity, dCounty,
dPostCode, dPhoneNumber, dHearAboutUs, dSuggestions) VALUES ( 'tf', 'tf', 'tf',
'tf', 'tf@tf.com', '10/03/1982', 'Male', 'No', '', '', 'jh', 'kjh', '', ,
'...please select...', '' )
DATASOURCE cfukdoorstaff
VENDORERRORCODE 3092
SQLSTATE &nbsp;

and the code....

<cflock scope="Session" timeout="60" throwOnTimeout="yes" type="readOnly">
<cfquery datasource="cfukdoorstaff" name="insertRegDetails">
INSERT INTO dRegDetails (dUserName, dPassword, dFirstName, dSurName,
dEmail, dDOB, dGender, dSIA, dAddress1, dAddress2, dTownCity, dCounty,
dPostCode, dPhoneNumber, dHearAboutUs, dSuggestions)
VALUES (
'#Session.regUserName#',
'#Session.regPassword#',
'#Session.regFirstName#',
'#Session.regSurName#',
'#Session.regEmail#',
'#Session.regDOB#',
'#Session.regGender#',
'#Session.regSIA#',
'#Session.regAddress1#',
'#Session.regAddress2#',
'#Session.regTownCity#',
'#Session.regCounty#',
'#Session.regPostCode#',
#Session.regPhoneNumber#,
'#Session.regHearAboutUs#',
'#Session.regSuggestions#'
)
</cfquery>
<cfquery datasource="cfukdoorstaff" name="insertSubDetails">
INSERT INTO dSubscriptionDetails (dID, dEmail, dInitialDateReg,
dCurrentSubStart, dCurrentSubExpiry, dAccountActive)
VALUES (
SELECT dID, dEmail, '#Session.regCurrentDate#',
'#Session.regCurrentDate#', '#Session.regSubExpiryDate#', 'Yes'
FROM dRegDetails
WHERE dEmail = '#Session.regEmail#' )
</cfquery>
</cflock>

any ideas?

[BKBK]

>... any ideas?
Yes, three.

1) With no single quotes around #Session.regPhoneNumber# , Coldfusion
expected you to insert, for example, a numeric value or date. You inserted
nothing.

2) The value inserted for dHearAboutUs, "...please select..." , suggests to me
that
a no option was selected in a form's select-field. I would make sure that one
option is
always selected by default, e.g. by using the Selected attribute.

3) It is recommended coding practice to keep queries out of Coldfusion locks
and locks
out of queries. As database servers usually enforce their own locks, the
combination
might cause a bottleneck. However, locking session code as you have done is
also
good coding practice. A way around the apparent dilemma is to temporarily
transfer
the session variables to the request scope.





<cflock scope="Request" timeout="60" throwOnTimeout="yes" type="readOnly">
<cfset Request.regUserName="#Session.regUserName#">
<cfset Request.regPassword="#Session.regPassword#">
<cfset Request.regFirstName="#Session.regFirstName#">
<cfset Request.regSurName="#Session.regSurName#">
<cfset Request.regEmail="#Session.regEmail#">
<cfset Request.regDOB="#Session.regDOB#">
<cfset Request.regGender="#Session.regGender#">
<cfset Request.regSIA="#Session.regSIA#">
<cfset Request.regAddress1="#Session.regAddress1#">
<cfset Request.regAddress2="#Session.regAddress2#">
<cfset Request.regTownCity="#Session.regTownCity#">
<cfset Request.regCounty="#Session.regCounty#">
<cfset Request.regPostCode="#Session.regPostCode#">
<cfset Request.regPhoneNumber="#Session.regPhoneNumber#">
<cfset Request.regHearAboutUs="#Session.regHearAboutUs#">
<cfset Request.regSuggestions="#Session.regSuggestions#">
<cfset Request.regCurrentDate="#Session.regCurrentDate#">
<cfset Request.regSubExpiryDate="#Session.regSubExpiryDat e#">
<cfquery datasource="cfukdoorstaff" name="insertRegDetails">
</cflock>
<cfquery datasource="cfukdoorstaff" name="insertRegDetails">
INSERT INTO dRegDetails (dUserName, dPassword, dFirstName, dSurName, dEmail,
dDOB, dGender, dSIA, dAddress1, dAddress2, dTownCity, dCounty, dPostCode,
dPhoneNumber, dHearAboutUs, dSuggestions)
VALUES (
'#Request.regUserName#',
'#Request.regPassword#',
'#Request.regFirstName#',
'#Request.regSurName#',
'#Request.regEmail#',
'#Request.regDOB#',
'#Request.regGender#',
'#Request.regSIA#',
'#Request.regAddress1#',
'#Request.regAddress2#',
'#Request.regTownCity#',
'#Request.regCounty#',
'#Request.regPostCode#',
#Request.regPhoneNumber#,
'#Request.regHearAboutUs#',
'#Request.regSuggestions#')
</cfquery>
<cfquery datasource="cfukdoorstaff" name="insertSubDetails">
INSERT INTO dSubscriptionDetails (dID, dEmail, dInitialDateReg,
dCurrentSubStart, dCurrentSubExpiry, dAccountActive)
VALUES
SELECT dID, dEmail, '#Session.regCurrentDate#', '#Request.regCurrentDate#',
'#Request.regSubExpiryDate#', 'Yes'
FROM dRegDetails
WHERE dEmail = '#Request.regEmail#'
</cfquery>

[P_Roberts]

excellent thankyou...idea no.1 worked in correcting that problem...idea no. 2
the "...please select..." is the first value in the list so thats cool, and
number 3, well I used your code and thats sorted that issue.

however coming up with another error now, probably something i'm overlooking
again, but can't figure it out...

Error Executing Database Query.
Syntax error in INSERT INTO statement.

The error occurred in C:\CFusionMX7\wwwroot\ukdoorstaff\activateprocess. cfm:
line 66

64 : SELECT dID, dEmail, #Request.regCurrentDate#, #Request.regCurrentDate#,
#Request.regSubExpiryDate#, 'Yes'
65 : FROM dRegDetails
66 : WHERE dEmail = '#Request.regEmail#'
67 : </cfquery>
68 : <cfoutput>



--------------------------------------------------------------------------------

SQL INSERT INTO dSubscriptionDetails (dID, dEmail, dInitialDateReg,
dCurrentSubStart, dCurrentSubExpiry, dAccountActive) VALUES SELECT dID, dEmail,
05/12/2005, 05/12/2005, 05/12/2006, 'Yes' FROM dRegDetails WHERE dEmail =
'asd@fd.co.uk'
DATASOURCE cfukdoorstaff
VENDORERRORCODE 3092
SQLSTATE &nbsp;



<cfquery datasource="cfukdoorstaff" name="insertSubDetails">
INSERT INTO dSubscriptionDetails (dID, dEmail, dInitialDateReg,
dCurrentSubStart, dCurrentSubExpiry, dAccountActive)
VALUES
SELECT dID, dEmail, #Request.regCurrentDate#, #Request.regCurrentDate#,
#Request.regSubExpiryDate#, 'Yes'
FROM dRegDetails
WHERE dEmail = '#Request.regEmail#'
</cfquery>

[paross1]

Drop the VALUES from your insert, since you are doing a SELECT

Phil


<cfquery datasource="cfukdoorstaff" name="insertSubDetails">
INSERT INTO dSubscriptionDetails (dID, dEmail, dInitialDateReg,
dCurrentSubStart, dCurrentSubExpiry, dAccountActive)
SELECT dID, dEmail, #Request.regCurrentDate#, #Request.regCurrentDate#,
#Request.regSubExpiryDate#, 'Yes'
FROM dRegDetails
WHERE dEmail = '#Request.regEmail#'
</cfquery

[P_Roberts]

excellent thanks - knew t would be something simple!