Skip to main content
A
Anonymous
May 12, 2012
Answered

Query only insterting if three fields entered

  • May 12, 2012
  • 2 replies
  • 1963 views

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>

    This topic has been closed for replies.
    Correct answer BKBK

    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>


    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>

    2 replies

    A
    Adam Cameron.
    Inspiring
    May 12, 2012

    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

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 12, 2012

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

    A
    Anonymous
    May 12, 2012

    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

    D
    Dan_Bracuk
    Inspiring
    May 12, 2012

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices