Skip to main content
May 27, 2012
Answered

Insert into two tables cfif

  • May 27, 2012
  • 2 replies
  • 1345 views

I have a form with two fields that are added to one table each. One or both can be filled out and when the form is sent I am using the action code below. Am I do it correctly? Thanks:

<!--- If form is sent do the following --->

<cfif isDefined("form.updatebtn")>
<!--- If the sectors1 field is filled out do this--->

<cfif FORM.SECTORS1 GTE 1>
<CFQUERY name="addsector" datasource="salesdb">
INSERT INTO INDUSTRIES(
SECTORSNOM

VALUES (
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.SECTORS1)#" null="#isSECTORS1Null#" /> )
</CFQUERY>
<CFLOCATION URL="member_welcome.cfm">

<!--- If the department1 field is filled out do this--->

<cfelseif FORM.DEPARTMENT1 GTE 1>
<CFQUERY name="adddivision" datasource="salesdb">
INSERT INTO DIVISION(
DEPARTMENT1

VALUES (
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DEPARTMENT1)#" null="#isDEPARTMENT1Null#" />
)
</CFQUERY>
<CFLOCATION URL="member_welcome.cfm">

<!--- If the both fields are filled out do this--->

<cfelse>

<CFQUERY name="addsector" datasource="salesdb">
INSERT INTO INDUSTRIES(
SECTORSNOM

VALUES (
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.SECTORS1)#" null="#isSECTORS1Null#" /> )
</CFQUERY>

<CFQUERY name="adddivision" datasource="salesdb">
INSERT INTO DIVISION(
DEPARTMENT1

VALUES (
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DEPARTMENT1)#" null="#isDEPARTMENT1Null#" />
)
</CFQUERY>

</cfif>

    This topic has been closed for replies.
    Correct answer BKBK

    The else-block seems like duplication to me. The code is more or less equivalent to this shorter version:

    <cfif isDefined("form.updatebtn")>

        <!--- If the sectors1 field is filled out do this--->

        <cfif FORM.SECTORS1 GTE 1>

            <CFQUERY name="addsector" datasource="salesdb">

            INSERT INTO INDUSTRIES(SECTORSNOM)

            VALUES (<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.SECTORS1)#" null="#isSECTORS1Null#" /> )

            </CFQUERY>

            <CFLOCATION URL="member_welcome.cfm">

        </cfif>

        <!--- If the department1 field is filled out do this--->

        <cfif FORM.DEPARTMENT1 GTE 1>

            <CFQUERY name="adddivision" datasource="salesdb">

            INSERT INTO DIVISION(DEPARTMENT1)

            VALUES (<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DEPARTMENT1)#" null="#isDEPARTMENT1Null#" />)

            </CFQUERY>

            <CFLOCATION URL="member_welcome.cfm">

        </cfif>

    </cfif>

    2 replies

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    May 28, 2012

    The else-block seems like duplication to me. The code is more or less equivalent to this shorter version:

    <cfif isDefined("form.updatebtn")>

        <!--- If the sectors1 field is filled out do this--->

        <cfif FORM.SECTORS1 GTE 1>

            <CFQUERY name="addsector" datasource="salesdb">

            INSERT INTO INDUSTRIES(SECTORSNOM)

            VALUES (<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.SECTORS1)#" null="#isSECTORS1Null#" /> )

            </CFQUERY>

            <CFLOCATION URL="member_welcome.cfm">

        </cfif>

        <!--- If the department1 field is filled out do this--->

        <cfif FORM.DEPARTMENT1 GTE 1>

            <CFQUERY name="adddivision" datasource="salesdb">

            INSERT INTO DIVISION(DEPARTMENT1)

            VALUES (<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DEPARTMENT1)#" null="#isDEPARTMENT1Null#" />)

            </CFQUERY>

            <CFLOCATION URL="member_welcome.cfm">

        </cfif>

    </cfif>

    May 28, 2012

    Thanks BKBK and Dan I worked it out

    <cfif isDefined("form.UPDATEADDBTN")>
    <cfif form.SECTORS1 GTE 1>
    <CFQUERY name="addsector" datasource="salesdb">
    INSERT INTO INDUSTRIES(
    SECTORSNOM

    VALUES (
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.SECTORS1)#" /> )
    </CFQUERY>

    <cfelseif form.DEPARTMENT1 GTE 1>
    <CFQUERY name="adddivision" datasource="salesdb">
    INSERT INTO DIVISION(
    DEPARTMENTNOM

    VALUES (
    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.DEPARTMENT1)#" />
    )
    </CFQUERY>

    <cfelse>
    <strong>Please enter a Sector or Department</strong>
    </cfif>
    </cfif>

    Inspiring
    May 29, 2012

    If someone submits both sectors1 and department1, the adddivision query won't run.  Look at BKBK's suggestion again.

    Inspiring
    May 28, 2012

    You are not doing it correctly.  Comment out your queries and output some text that tells you what block of code is executing.  Then submit your form a few times with various values and see if you get your expected results.