Participating Frequently

Answered

cfqueryparam and cfif statement

Forum|Forum|15 years ago
December 23, 2010
2 replies
2126 views

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

This topic has been closed for replies.

Correct answer Owainnorth

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.

D

Dan_Bracuk

Inspiring

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.

J

JohnShellenAuthor

Participating Frequently

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.

>

ilssac

Inspiring

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.

OwainnorthCorrect answer

Inspiring

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.

J

JohnShellenAuthor

Participating Frequently

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.

>

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded