updating a database with cfid and cftoken

[wwolff]

Hello,

I am trying to update a MS Access database using cfid and cftoken. What I'd
like to do is have the database update where the cfid is the same as the
'#session.cfid#'. I've tried and few things, but I have been getting error
messages.

Code:

<cfquery datasource="DATASOURCE">
INSERT INTO TABLENAME(uin,cftoken) VALUES('#session.cfid#','#session.cftoken#')
</cfquery>

<cfquery datasource="DATASOURCE">
UPDATE="TABLENAME"
SET variable='#form.variable#'
WHERE uin='#session.cfid#'
</cfquery>

I'd also like to be able to identify alert a user that they have already
completed the survey, so there are no duplicate entries.

Thanks for your help,

bw

[vkunirs]

Hi

i think you have forgotten to post the errors. its better to post your error
of what you are getting.

to check whether the user has already completed the survey then write a
query like the below:
select * from table where uin="#session.cfid#"

IF the record if found then just display the survey button to not allow the
user to do it again.

[OldCFer]

If you're using the following:

UPDATE="TABLENAME"
SET variable='#form.variable#'
WHERE uin='#session.cfid#'

the syntax is wrong.It should be something like:
UPDATE TABLENAME
SET Somefield ='#form.variable#'
WHERE uin='#session.cfid#'

[wwolff]

OldCFer,

Thanks for pointing out my syntax error, which has done the trick.

I'm now trying to set up the correct CFIF statement so that a person cannot
complete the survey twice, but for some reason my code is not working, and a
usr can enter a survey multiple times. My code:

<cfquery datasource="swc-survey-database" >
SELECT uin FROM swctbl1 WHERE uin='#session.cfid#'
</cfquery>

<cfif #session.cfid# is 'uin'>

<p>You have already completed the survey. Thank you for your
participation.</p>

<cfelse>

<cfquery datasource="swc-survey-database">
INSERT INTO swctbl1(uin,cftoken) VALUES('#session.cfid#','#session.cftoken#')
</cfquery>

<cfquery datasource="swc-survey-database">
UPDATE swctbl1
SET major='#form.major#'
WHERE uin='#session.cfid#'
</cfquery>

</cfif>

<cflocation url="swc-thanks.html">

From this newCFer,

Thanks!

[OldCFer]

You have to name your cfqueries when you do selects.
Then your code would be:

<cfquery name="GetUIN" datasource="swc-survey-database" >
SELECT uin FROM swctbl1 WHERE uin='#session.cfid#'
</cfquery>

<cfif GetUIN.RecordCount>
<p>You have already completed the survey. Thank you for your participation.</p>
<cfelse>
<cfquery datasource="swc-survey-database">
INSERT INTO swctbl1(uin,cftoken,major)
VALUES('#session.cfid#','#session.cftoken#','#form .major#')
</cfquery>
</cfif>

There's no reason to insert then update. Just insert it all at once.

[wwolff]

Thanks, Larry.

That did the trick.

BW