<cfquery> Undefined

[zoid777]

I need some help with <cfquery>, I want to authenticate an add form so the user doesn't enter the same value twice or more so I used recordcount like this

<cfquery name="cool.recordcount" datasource="#Request.proto#">
<cfif cool EQ 0>
INSERT INTO proton(
name,
moon
)
VALUES(
'#Trim(Form.name)#',
'#Trim(Form.moon)#',
)
<cfif cool EQ 0>
<cfelse>
THIS RECORD ALREADY EXIST
<cfinclude template="adduser.cfm">
</cfif>
</cfquery>

but it throws an error like Variable COOL is undefined. I am also using anplication.cfm Application.cfm also in my form, is their anything you can see I am doing wrong or do I have to define the query name cool, it is already defined in the cfquery?

[cmhampton]

First of all, please don't name your query cool.recordCount. RecordCount is a property of the query object. Name it something like qryCool.

Secondly, you're missing a </cfif> tag. You have two cfif statements, but only one closing.

Do you already have code that determines if this is a duplicate record? What is the 'cool' variable for? If you already check, try this code and let me know:

<cfif cool EQ 0>
  <cfquery name="qryCool" datasource="#Request.proto#">
    INSERT INTO proton(
      name,
      moon
    )
    VALUES(
      '#Trim(Form.name)#',
      '#Trim(Form.moon)#'
    )
  </cfquery>
<cfelse>
  This Record Already Exists
  <cfinclude template="adduser.cfm">
</cfif>

Change the adduser.cfm to include it's own <cfquery> tag.

Alternately, you can do it all in SQL if you haven't determined whether this is a duplicate record or not.

<cfquery name="qryCool" datasource="#Request.proto#">
    IF NOT EXISTS
      (
        SELECT
          name,
          moon
        FROM
          proton
        WHERE
          name =  '#Trim(Form.name)#'
          AND moon =  '#Trim(Form.moon)#'
      )
      BEGIN
        INSERT INTO proton(
          name,
          moon
        )
        VALUES(
          '#Trim(Form.name)#',
          '#Trim(Form.moon)#'
        )

      SELECT SCOPE_IDENTITY() AS RecordID

      END

    ELSE
      SELECT NULL AS RecordID
  </cfquery>

  <cfif qryCool.RecordID EQ "">
    This Record Already Exists
  <cfelse>
    <!--- Do something here --->
  </cfif>

[zoid777]

Thanks cmhampton the Direct SQL version worked, I just modified the cfif at the buttom and it works.