[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

How to add CFQUERYPARAM to these two simple queries?

Experts,

I am wanting to use the CFQUERYPARAM tag with the two queries below.
Can someone help?

select distinct emp,sum(hours) as total_hours
From C
where emp = '#form.employee#' and hours <> 0
AND datepart(week, getdate()) = datepart(week, curdate)
group by emp
   

select emp,inumber,icodenumber,rel_month,hours,spm_subline
From C
where emp = '#form.employee#' and hours <> 0
AND datepart(week, getdate()) = datepart(week, curdate)
order by spm_subline

Cheers
0
g118481
Asked:
g118481
1 Solution
 
73SpyderCommented:
select distinct emp,sum(hours) as total_hours
From C
where emp = <cfqueryparam value="#form.employee#" cfsqltype="cf_sql_varchar"> and hours <> 0
AND datepart(week, getdate()) = datepart(week, curdate)
group by emp



select emp,inumber,icodenumber,rel_month,hours,spm_subline
From C
where emp = <cfqueryparam value="#form.employee#" cfsqltype="cf_sql_varchar"> and hours <> 0
AND datepart(week, getdate()) = datepart(week, curdate)
order by spm_subline
0
 
Ike23Commented:
It's sometimes helpful just to read over the livedocs and make sure you understand the tag you are using.  Here's a link to a good page that will tell you all about it.  I've run into some weird behaviour with dates so make sure you choose the correct data type and you should be fine.  

Here is a few lines that I think are important for you to know:

The validation rules are as follows:

For these types, a data value can be converted to a numeric value: CF_SQL_SMALLINT, CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT, CF_SQL_DOUBLE, CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4, CF_SQL_DECIMAL, CF_SQL_NUMERIC, and CF_SQL_BIGINT

For these types, a data value can be converted to a date supported by the target data source: CF_SQL_DATE, CF_SQL_TIME, CF_SQL_TIMESTAMP
 
For all other types, if the maxLength attribute is used, a data value cannot exceed the maximum length specified.
ColdFusion debug output shows the bind variables as question marks and lists the values beneath the query, in order of usage.

Note: To insert an empty string into a Microsoft Access table using the SequelLink ODBC Socket or SequelLink Access driver, the CFSQLType attribute must specify CF_SQL_LONGVARCHAR.

Spyder's example will probably work but you might want to add a TRIM() to the form.employee in case someone types in a space before or after the name like this:

select distinct emp,sum(hours) as total_hours
From C
where emp = <cfqueryparam value="#TRIM(form.employee)#" cfsqltype="cf_sql_varchar"> and hours <> 0
AND datepart(week, getdate()) = datepart(week, curdate)
group by emp



select emp,inumber,icodenumber,rel_month,hours,spm_subline
From C
where emp = <cfqueryparam value="#TRIM(form.employee)#" cfsqltype="cf_sql_varchar"> and hours <> 0
AND datepart(week, getdate()) = datepart(week, curdate)
order by spm_subline

Cheers!

Ike
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now