Skip navigation
goodychurro1
goodychurro1
Currently Being Moderated

Query only insterting if three fields entered

May 12, 2012 9:44 AM

Hi all

For some reason when executing this query there have to be values in the sectorid, ranking and department fields otherwise cf throws an error. As these fields are optional (and the db fields are allowed to be null) how can I make the query work if they are not filled out? Thanks

 

<cfelse>

<!--- ADD COMPANY QUERY --->
<CFQUERY name="addcompany" datasource="sales">
INSERT INTO COMPANY (COMPANY, SECTORID, RANKING, DEPARTMENT, FIRSTNAME, POS, PHONE, EMAIL, MOBILE, ADDRESS, city, dept, COMMENTS, DTLEAVE, MEETINGTIME, state, UPDATED)


VALUES ('#Trim(FORM.COMPANY)#', #Trim(FORM.SECTORID)#, #Trim(FORM.RANKING)#, 

#Trim(FORM.DEPARTMENT)#, '#Trim(FORM.FIRSTNAME)#', '#Trim(FORM.POS)#', '#Trim

(FORM.PHONE)#', '#Trim(FORM.EMAIL)#', '#Trim(FORM.MOBILE)#', '#Trim(FORM.ADDRESS)

#', '#Trim(FORM.city)#', '#Trim(FORM.dept)#', '#Trim(FORM.COMMENTS)#','#Trim

(FORM.DTLEAVE)#', '#Trim(FORM.MEETINGTIME)#', '#Trim(FORM.state)#', '#dateFormat

(now(), 'mmm dd, yyyy')#')
</CFQUERY>
<CFLOCATION URL="member_welcome.cfm">
</cfif>
</cfif>

1022 Views   11 Replies   Latest reply: goodychurro1, May 13, 2012 10:06 AM
 
Replies
  • BKBK
    Currently Being Moderated
    1. BKBK,
    May 12, 2012 10:07 AM   in reply to goodychurro1
    Report

    Could it be that the primary key is some combination of {company, sectorid, ranking}? What is the error message?

     
    |
    Mark as:
  • Adam Cameron.
    Currently Being Moderated
    2. Adam Cameron.,
    May 12, 2012 10:28 AM   in reply to goodychurro1
    Report

    What does the error message say?  That should be a big clue as to what's wrong.  Also in the error message, it'll be showing the SQL that is being passed to the DB.  Does it look valid?

     

    What you need to do is to add some conditional logic around your form fields (and the columns they correspond to) to only include them in the SQL if they've got values that you actually want to go in the DB (so not if they don't have values at all...).

     

    Also you should not hard-code your dynamic values in your SQL string, you should pass them as parameters using <cfqueryparam>.

     

    --

    Adam

     
    |
    Mark as:
  • goodychurro1
    Currently Being Moderated
    3. goodychurro1,
    May 12, 2012 10:30 AM   in reply to BKBK
    Report

    Thanks bkbk here is the error:

     

    Error Executing Database Query. 
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , '', '', '', '', '', '', 'None', 'None', '','', 'None', 'None', 'May 12, 20' at line 2 
     
    The error occurred in member_welcome.cfm: line 56
    Called from member_welcome.cfm: line 51
    Called from member_welcome.cfm: line 27
    Called from member_welcome.cfm: line 1
    Called from member_welcome.cfm: line 56
    Called from member_welcome.cfm: line 51
    Called from member_welcome.cfm: line 27
    Called from member_welcome.cfm: line 1

     

    54 : <CFQUERY name="addcompany" datasource="sales">
    55 : INSERT INTO COMPANY (COMPANY, SECTORID, RANKING, DEPARTMENT, FIRSTNAME, POS, PHONE, EMAIL, MOBILE, ADDRESS, city, dept, COMMENTS, DTLEAVE, MEETINGTIME, state, UPDATED) 
    56 : VALUES ('#Trim(FORM.COMPANY)#', #Trim(FORM.SECTORID)#, #Trim(FORM.RANKING)#,  #Trim(FORM.DEPARTMENT)#, '#Trim(FORM.FIRSTNAME)#', '#Trim(FORM.POS)#', '#Trim(FORM.PHONE)#', '#Trim(FORM.EMAIL)#', '#Trim(FORM.MOBILE)#', '#Trim(FORM.ADDRESS)#', '#Trim(FORM.city)#', '#Trim(FORM.dept)#', '#Trim(FORM.COMMENTS)#','#Trim(FORM.DTLEAVE)#', '#Trim(FORM.MEETINGTIME)#', '#Trim(FORM.state)#', '#dateFormat(now(), 'mmm dd, yyyy')#')
    57 : </CFQUERY>
    58 : <CFLOCATION URL="member_welcome.cfm">

     

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

     

    VENDORERRORCODE   1064
    SQLSTATE   42000
    SQL    INSERT INTO COMPANY (COMPANY, SECTORID, RANKING, DEPARTMENT, FIRSTNAME, POS, PHONE, EMAIL, MOBILE, ADDRESS, city, dept, COMMENTS, DTLEAVE, MEETINGTIME, state, UPDATED) VALUES ('newcompany10', , , , '', '', '', '', '', '', 'None', 'None', '','', 'None', 'None', 'May 12, 2012') 
    DATASOURCE   sales

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    4. Dan Bracuk,
    May 12, 2012 12:15 PM   in reply to goodychurro1
    Report

    Where you have consecutive comma's you need something in between.  If you want null values, you need the word null. 

     

    As an aside, storing dates as text is a bad idea.

     
    |
    Mark as:
  • goodychurro1
    Currently Being Moderated
    5. goodychurro1,
    May 12, 2012 6:15 PM   in reply to Dan Bracuk
    Report

    I get this error using cfqueryparam:

    Invalid CFML construct found on line 56 at column 61.

    ColdFusion was looking at the following text:

    #

     

    Where am I going wrong? Thanks

     

    <!--- ADD COMPANY QUERY --->
    <CFQUERY name="addcompany" datasource="sales">
    INSERT INTO COMPANY (COMPANY, SECTORID, RANKING, DEPARTMENT, FIRSTNAME, POS, PHONE,

    EMAIL, MOBILE, ADDRESS, city, dept, COMMENTS, DTLEAVE, MEETINGTIME, state)


    VALUES (<cfqueryparam cfsqltype=”cf_sql_longvarchar” value=”#trim(form.COMPANY)#”

    null=”#NOT len(trim(form.COMPANY))#” />, <cfqueryparam cfsqltype=”cf_sql_integer”

    value=”#trim(form.SECTORID)#” null=”#NOT len(trim(form.SECTORID))#” />,

    <cfqueryparam cfsqltype=”cf_sql_longvarchar” value=”#trim(form.RANKING)#” null=”

    #NOT len(trim(form.RANKING))#” />,<cfqueryparam cfsqltype=”cf_sql_longvarchar”

    value=”#trim(form.DEPARTMENT)#” null=”#NOT len(trim(form.DEPARTMENT))#” />, '#Trim

    (FORM.FIRSTNAME)#', '#Trim(FORM.POS)#', '#Trim(FORM.PHONE)#', '#Trim(FORM.EMAIL)#',

    '#Trim(FORM.MOBILE)#', '#Trim(FORM.ADDRESS)#', '#Trim(FORM.city)#', '#Trim

    (FORM.dept)#', '#Trim(FORM.COMMENTS)#','#Trim(FORM.DTLEAVE)#', '#Trim

    (FORM.MEETINGTIME)#', '#Trim(FORM.state)#')
    </CFQUERY>

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    6. BKBK,
    May 13, 2012 4:20 AM   in reply to goodychurro1
    Report

    The query is chock-a-block, you can easily overlook a missing character. I have taken the liberty of converting it into the following (hopefully) more easily debugged version:

     

    <cfset isCompanyNull= iif(len(trim(form.company)) EQ 0, true, false)>

    <cfset isSectorIdNull   = iif(len(trim(form.sectorid)) EQ 0, true, false)>

    <cfset isRankingNull= iif(len(trim(form.ranking)) EQ 0, true, false)>

    <cfset isDepartmentNull = iif(len(trim(form.department)) EQ 0, true, false)>

     

    <!--- ADD COMPANY QUERY --->

    <CFQUERY name="addcompany" datasource="sales">

    INSERT INTO COMPANY (

    COMPANY,

    SECTORID,

    RANKING,

    DEPARTMENT,

    FIRSTNAME,

    POS,

    PHONE,

    EMAIL,

    MOBILE,

    ADDRESS,

    city,

    dept,

    COMMENTS,

    DTLEAVE,

    MEETINGTIME,

    state

    )

     

    VALUES (

    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMPANY)#" null="#isCompanyNull#" />,

    <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.SECTORID)#" null="#isSectorIdNull#" />,

    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.RANKING)#" null="#isRankingNull#" />,

    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DEPARTMENT)#" null="#isDepartmentNull#" />,

    '#Trim(FORM.FIRSTNAME)#',

    '#Trim(FORM.POS)#',

    '#Trim(FORM.PHONE)#',

    '#Trim(FORM.EMAIL)#',

    '#Trim(FORM.MOBILE)#',

    '#Trim(FORM.ADDRESS)#',

    '#Trim(FORM.city)#',

    '#Trim(FORM.dept)#',

    '#Trim(FORM.COMMENTS)#',

    '#Trim(FORM.DTLEAVE)#',

    '#Trim(FORM.MEETINGTIME)#',

    '#Trim(FORM.state)#'

    )

    </CFQUERY>

     
    |
    Mark as:
  • Adam Cameron.
    Currently Being Moderated
    7. Adam Cameron.,
    May 13, 2012 4:50 AM   in reply to BKBK
    Report
    <cfset isCompanyNull= iif(len(trim(form.company)) EQ 0, true, false)>

     

     

    One doesn't need the iif().  This is fine:

     

    isCompanyNull= !len(trim(form.company));

     

    But, equally, there's nowt wrong with dispensing with the variable and just having the expression inline in the tag.

     

    But, yeah, your advice to use some whitespace and indentation in one's code is a good 'un.

     

    --

    Adam

     
    |
    Mark as:
  • Adam Cameron.
    Currently Being Moderated
    8. Adam Cameron.,
    May 13, 2012 4:57 AM   in reply to goodychurro1
    Report

    goodychurro1 wrote:

     

    I get this error using cfqueryparam:

    Invalid CFML construct found on line 56 at column 61.

    ColdFusion was looking at the following text:

    #

     

    Where am I going wrong? Thanks

     

     

    Well... what's at line 56 column 61?  CF's giving you a pretty good tip as to where to start looking.

     

    That's a compile error it's giving, and there's nothing syntactically wrong with the code you posted (it compiles fine on my machine), so that is either: a) not the code you're actually running; b) not the code that's erroring.

     

    Also, how come you put four of your variables into <cfqueryparam> tags, but left most of them hard-coded?

     

    --

    Adam

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    9. BKBK,
    May 13, 2012 6:46 AM   in reply to Adam Cameron.
    Report

    Adam Cameron. wrote:

     

    <cfset isCompanyNull= iif(len(trim(form.company)) EQ 0, true, false)>

     

     

    One doesn't need the iif().  This is fine:

     

    isCompanyNull= !len(trim(form.company));

     

    I know. It's more a question of style. To me, at least.

     

    I do that by choice. I won't write an integer where I intend a boolean to be.

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    10. BKBK,
    May 13, 2012 6:55 AM   in reply to goodychurro1
    Report

    goodychurro1 wrote:

     

    I get this error using cfqueryparam:

    Invalid CFML construct found on line 56 at column 61.

    ColdFusion was looking at the following text:

    #

     

    Where am I going wrong? Thanks

     

    <!--- ADD COMPANY QUERY --->
    <CFQUERY name="addcompany" datasource="sales">
    INSERT INTO COMPANY (COMPANY, SECTORID, RANKING, DEPARTMENT, FIRSTNAME, POS, PHONE,

    EMAIL, MOBILE, ADDRESS, city, dept, COMMENTS, DTLEAVE, MEETINGTIME, state)


    VALUES (<cfqueryparam cfsqltype=”cf_sql_longvarchar” value=”#trim(form.COMPANY)#”

    null=”#NOT len(trim(form.COMPANY))#” />, <cfqueryparam cfsqltype=”cf_sql_integer”

    value=”#trim(form.SECTORID)#” null=”#NOT len(trim(form.SECTORID))#” />,

    <cfqueryparam cfsqltype=”cf_sql_longvarchar” value=”#trim(form.RANKING)#” null=”

    #NOT len(trim(form.RANKING))#” />,<cfqueryparam cfsqltype=”cf_sql_longvarchar”

    value=”#trim(form.DEPARTMENT)#” null=”#NOT len(trim(form.DEPARTMENT))#” />, '#Trim

    (FORM.FIRSTNAME)#', '#Trim(FORM.POS)#', '#Trim(FORM.PHONE)#', '#Trim(FORM.EMAIL)#',

    '#Trim(FORM.MOBILE)#', '#Trim(FORM.ADDRESS)#', '#Trim(FORM.city)#', '#Trim

    (FORM.dept)#', '#Trim(FORM.COMMENTS)#','#Trim(FORM.DTLEAVE)#', '#Trim

    (FORM.MEETINGTIME)#', '#Trim(FORM.state)#')
    </CFQUERY>

     

    Have you noticed that the double-quotes within the VALUES brackets are non-ASCII characters?

     
    |
    Mark as:
  • goodychurro1
    Currently Being Moderated
    11. goodychurro1,
    May 13, 2012 10:06 AM   in reply to BKBK
    Report

    Thanks all it works great now!!

     

    <cfset isCOMPANYNull = iif(len(trim(form.COMPANY)) EQ 0, true, false)>
    <cfset isSECTORIDNull   = iif(len(trim(form.SECTORID)) EQ 0, true, false)>
    <cfset isRANKINGNull = iif(len(trim(form.RANKING)) EQ 0, true, false)>
    <cfset isDEPARTMENTNull = iif(len(trim(form.DEPARTMENT)) EQ 0, true, false)>
    <cfset isFIRSTNAMENull = iif(len(trim(form.FIRSTNAME)) EQ 0, true, false)>
    <cfset isPOSNull = iif(len(trim(form.POS)) EQ 0, true, false)>
    <cfset isPHONENull = iif(len(trim(form.PHONE)) EQ 0, true, false)>
    <cfset isEMAILNull = iif(len(trim(form.EMAIL)) EQ 0, true, false)>
    <cfset isMOBILENull = iif(len(trim(form.MOBILE)) EQ 0, true, false)>
    <cfset isADDRESSNull = iif(len(trim(form.ADDRESS)) EQ 0, true, false)>
    <cfset isCITYNull = iif(len(trim(form.CITY)) EQ 0, true, false)>
    <cfset isDEPTNull = iif(len(trim(form.DEPT)) EQ 0, true, false)>
    <cfset isCOMMENTSNull = iif(len(trim(form.COMMENTS)) EQ 0, true, false)>
    <cfset isDTLEAVENull = iif(len(trim(form.DTLEAVE)) EQ 0, true, false)>
    <cfset isMEETINGTIMENull = iif(len(trim(form.MEETINGTIME)) EQ 0, true, false)>
    <cfset isSTATENull = iif(len(trim(form.STATE)) EQ 0, true, false)>


    <!--- ADD COMPANY QUERY --->
    <CFQUERY name="addcompany" datasource="sales">
    INSERT INTO COMPANY(
    COMPANY,
    SECTORID,
    RANKING,
    DEPARTMENT,
    FIRSTNAME,
    POS,
    PHONE,
    EMAIL,
    MOBILE,
    ADDRESS,
    CITY,
    DEPT,
    COMMENTS,
    DTLEAVE,
    MEETINGTIME,
    STATE,
    UPDATED
    ) 
    VALUES (
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMPANY)#" null="#isCOMPANYNull#" />,
    <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.SECTORID)#" null="#isSECTORIDNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.RANKING)#" null="#isRANKINGNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DEPARTMENT)#" null="#isDEPARTMENTNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.FIRSTNAME)#" null="#isFIRSTNAMENull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.POS)#" null="#isPOSNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.PHONE)#" null="#isPHONENull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.EMAIL)#" null="#isEMAILNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.MOBILE)#" null="#isMOBILENull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.ADDRESS)#" null="#isADDRESSNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.CITY)#" null="#isCITYNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DEPT)#" null="#isDEPTNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMMENTS)#" null="#isCOMMENTSNull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DTLEAVE)#" null="#isDTLEAVENull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.MEETINGTIME)#" null="#isMEETINGTIMENull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.STATE)#" null="#isSTATENull#" />,
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.UPDATED)#" null="#isUPDATEDNull#" />
    )
    </CFQUERY>

    
     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points