cfqueryparam and cfif statement

Report · Dec 23, 2010

I do not know how to accomplish what I want to do. Have SQL Insert statement with cfqueryparam.
Have form that users can add begin date | end date | type of hrs | number of hrs
The type of hrs is a list with 4 selections. If user selects "Rel Comp" then they are required to complete additional begin and end dates in RCbeginDate and RCendDate fields.
Database has following date fields: startdate | enddate | RCbeginDate | RCendDate. If "Rel Comp" not selected then db fields RCbeginDate and RCendDate are null.
If user has two or more entries - one not "Rel Comp" and the other is, then user is prompted to enter dates for RCbeginDate and RCendDate. Those dates post to the db for the row that is not "Rel Comp" and it should not.
It seems an if (cfif) statement should stop that but how?

Code:
<cfloop from="1" to="4" index="i" step="1">
<cfif IsDate(evaluate("Form.StartDate" & i))>
<cfquery name="qInsertRequest" datasource="RAWH">
Insert into RAWH
       (EmpPIN,
        Empname,
        dateRequested,
        startdate,
        stopdate,
        TypeHrs, (option list showing Comp Time|Credit Hrs|OT|Rel Comp)
        NoOfHours,
        projects,
        justification,
        RCbeginDate,
        RCendDate,
        RCLvSlip,
        reqDecision,
        denialReason)
VALUES (
     <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.EmpPIN#" null="#IIF(Len(Trim(Form.EmpPIN)), DE('no'), DE('yes'))#" />,

     <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.EmpName#" null="#IIF(Len(Trim(Form.EmpName)), DE('no'), DE('yes'))#" />,

       getdate(),

     <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Evaluate("Form.startdate" & i)#" />,

     <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Evaluate("Form.stopdate" & i)#" />,

     <cfqueryparam cfsqltype="cf_sql_varchar" value="#Evaluate("Form.TypeHrs" & i)#" />, (option list which should have if statement)
     <cfqueryparam cfsqltype="cf_sql_decimal" scale="2" value="#Evaluate("Form.NoOfHours" & i)#" />,

     <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.projects#" null="#IIF(Len(Trim(Form.projects)), DE('no'), DE('yes'))#" />,

     <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.justification#" null="#IIF(Len(Trim(Form.justification)), DE('no'), DE('yes'))#" />,

     <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Form.RCbeginDate#" null="#IIF(Len(Trim(Form.RCbeginDate)), DE('no'), DE('yes'))#" />,

     <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Form.RCendDate#" null="#IIF(Len(Trim(Form.RCendDate)), DE('no'), DE('yes'))#" />,

     <cfqueryparam cfsqltype="cf_sql_char" value="#Form.RCLvSlip#" null="#IIF(Len(Trim(Form.RCLvSlip)), DE('no'), DE('yes'))#" />,

        null,
        null)

      </cfquery>
</cfif>
</cfloop>

Would appreciate help with the if statement and where and how to place it.

Thank you,

John

Report · Dec 23, 2010

Okay, well for starters I think that you're doing is right - I use iif() for this exact purpose a lot.

(As an aside, evaluate() is pretty dirty way of doing things, personally I'd use:

form["startdate" & i]

but that's not that important)

I find it quite difficult to see what you're trying to do, so as a suggestion why don't you move the decision logic outside your insert query, for example:

...

</cfif>

<cfquery...>

</cfquery>

</cfloop>

Sometimes things that are obscured by lots of inline code become blindingly clear once you break it up a little.

O.

Report · Dec 23, 2010

Thank you very much for the advice. I will embrace it.

John

Date: Thu, 23 Dec 2010 11:20:22 -0700
From: forums@adobe.com
To: inliten@hotmail.com
Subject: cfqueryparam and cfif statement
Okay, well for starters I think that you're doing is right - I use iif() for this exact purpose a lot.

(As an aside, evaluate() is pretty dirty way of doing things, personally I'd use:

form["startdate" & i]

but that's not that important)

I find it quite difficult to see what you're trying to do, so as a suggestion why don't you move the decision logic outside your insert query, for example:

<cfloop>
<cfif form[startdate & i] eq ...>
<cfset bInsertStartDate = false />
<cfset bInsertEndDate = true />
...
</cfif>
<cfquery...>

</cfquery>
</cfloop>

Sometimes things that are obscured by lots of inline code become blindingly clear once you break it up a little.

O.

>

Report · Dec 23, 2010

Okay, well for starters I think that you're doing is right - I use iif() for this exact purpose a lot.

It's a lot of effort you don't need to go to.

Instead of this:

null="#IIF(Len(Trim(Form.EmpPIN)), DE('no'), DE('yes'))#"

One can just do this:

null="#NOT Len(Trim(Form.EmpPIN))#"

--

Adam

Report · Dec 23, 2010

Adam Cameron. wrote:
It's a lot of effort you don't need to go to

Woah there, I said I used the method - I never said I'd use that atrocity of an iif statement

Report · Dec 24, 2010

It's a lot of effort you don't need to go to
Woah there, I said I used the method - I never said I'd use that atrocity of an iif statement

Yeah, sorry. I misread it when you said "I use iif() for this exact purpose a lot."

😛

--

Adam

Report · Dec 24, 2010

Ha! Oh I see how it is.

Christmas Cheekiness all round! But pub soon, so I forgive you.

Report · Dec 24, 2010

Ha! Oh I see how it is.
Christmas Cheekiness all round! But pub soon, so I forgive you.

Yeah... I hate having to wait for opening time...

😉

--

Adam

Report · Dec 24, 2010

Only made more bearable by the substantial hamper we've just had turn up from a large online sweetshop customer of ours.

Oh yes.

Report · Dec 23, 2010

The sooner you do your data input validation, the better. That means the best place is on the client, when the user submits the form.

Most of the time it's also necessary to validate on the server. The sooner you do it, the better. The cfquery tag is too late.

Report · Dec 23, 2010

Thank you. I have several client-side validations using javascript. I suppose what you are suggesting is adding another javascript validation or does coldfusion's validation execute on the client-side?

Again, thanks.

John

Date: Thu, 23 Dec 2010 11:19:49 -0700
From: forums@adobe.com
To: inliten@hotmail.com
Subject: cfqueryparam and cfif statement
The sooner you do your data input validation, the better. That means the best place is on the client, when the user submits the form.

Most of the time it's also necessary to validate on the server. The sooner you do it, the better. The cfquery tag is too late.

>

Report · Dec 23, 2010

JohnShellen wrote:
Thank you. I have several client-side validations using javascript. I suppose what you are suggesting is adding another javascript validation or does coldfusion's validation execute on the client-side

Yes JavaScript is client side validation and is for the sanity and safety of the USER.

ColdFusion is server side validation and is for the sanity and safety of the SERVER.

Because you can never tell if the user didn't turn off JavaScript or in any number of ways bypass the client side validation.

And to take it to the next level, Database validation is for the sanity and safety of the DATABASE and DATA.

Because you can never be sure that the data will always and only come from the Application Server.

cfqueryparam and cfif statement

1 Correct answer