Skip to main content

Posts

Showing posts with the label query

How to get a delimited List from an executed query in ColdFusion

QuotedValueList() - delimited List from an executed query QuotedValueList.cfm <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>QuotedValueList Function example: how to get a delimited List from an executed query (each value enclosed in single quotation marks)</title> </head> <body> <h2 style="color:HotPink">QuotedValueList Function Example</h2> <cfquery name="qDepartments" datasource="cfdocexamples"> SELECT Dept_ID, Dept_Name,Location FROM DEPARTMT </cfquery> <cfdump var="#qDepartments#" label="Departments"> <br /><br /> <cfset DepartmentList=QuotedValueList(qDepartments.Dept_Name,";")> <cfoutput> <b> DepartmentList: #DepartmentList# </b> </cfoutput> </body> </html>...
Read more

How to set a cell to a value in a query object in ColdFusion

QuerySetCell() - set a cell to a value in a query object QuerySetCell.cfm <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>QuerySetCell function example: how to set a cell to a value in a query object</title> </head> <body> <h2 style="color:HotPink">QuerySetCell Function Example</h2> <cfset qColor=QueryNew("ColorID, ColorName, ColorValue","Integer, VarChar, VarChar")> <cfset Temp=QueryAddRow(qColor,2)> <cfset Temp=QuerySetCell(qColor,"ColorID",1,1)> <cfset Temp=QuerySetCell(qColor,"ColorName","Beige",1)> <cfset Temp=QuerySetCell(qColor,"ColorValue","##F5F5DC",1)> <cfset Temp=QuerySetCell(qColor,"ColorID",2,2)> <cfset Temp=QuerySetCell(qColor,"ColorName",...
Read more

How to create an empty query in ColdFusion

Introduction ColdFusion offers developers powerful and flexible tools to work with data. One of these is the ability to create and manipulate queries directly in the code without a database, which can be helpful in various scenarios such as testing or building dynamic data sets for an application. The QueryNew function allows developers to create an empty query (or query object) and then programmatically add rows and columns to it. This is particularly useful for scenarios where data needs to be handled on the server-side without relying on database operations. In this tutorial, we will explore how to use the QueryNew function to create an empty query, populate it with data, and view the result. This can serve as the foundation for dynamic data manipulation within your ColdFusion application. Creating an Empty Query Object The first step in the code is the creation of an empty query object using ColdFusion’s QueryNew function. The QueryNew function allows you to define the structur...
Read more

How to add a specified number of empty rows to a query in ColdFusion

QueryAddRow() - add a specified number of empty rows to a query QueryAddRow.cfm <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>QueryAddRow function example: how to add a specified number of empty rows to a query</title> </head> <body> <h2 style="color:HotPink">QueryAddRow Function Example</h2> <cfset qColor=QueryNew("ColorID, Name, Value","Integer, VarChar, VarChar")> <cfset Temp=QueryAddRow(qColor,3)> <cfset Temp=QuerySetCell(qColor,"ColorID",1,1)> <cfset Temp=QuerySetCell(qColor,"Name","Aqua",1)> <cfset Temp=QuerySetCell(qColor,"Value","##00FFFF",1)> <cfset Temp=QuerySetCell(qColor,"ColorID",2,2)> <cfset Temp=QuerySetCell(qColor,"Name","Aquamarine...
Read more

How to add a column to a query with its value in ColdFusion

QueryAddColumn() - add a column to a query with its value QueryAddColumn.cfm <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>QueryAddColumn function example: how to add a column to a query with its value</title> </head> <body> <h2 style="color:HotPink">QueryAddColumn Function Example</h2> <cfset qColor=QueryNew("ColorID, Name","Integer, VarChar")> <cfset Temp=QueryAddRow(qColor,2)> <cfset Temp=QuerySetCell(qColor,"ColorID",1,1)> <cfset Temp=QuerySetCell(qColor,"Name","AliceBlue",1)> <cfset Temp=QuerySetCell(qColor,"ColorID",2,2)> <cfset Temp=QuerySetCell(qColor,"Name","AntiqueWhite",2)> <cfdump var="#qColor#" label="qColor"> <cfset ColorV...
Read more

How to get a delimited List from an executed query in ColdFusion

Introduction In ColdFusion, managing data from databases is a common requirement, especially when you need to format it for further processing or display. One useful technique is transforming query results into a delimited list, which can simplify displaying or manipulating the data within a web application. ColdFusion’s ValueList function provides an efficient way to achieve this by extracting a single column from an executed query as a delimited list. This tutorial explores how to use the ValueList function to retrieve a column of data from a query and output it as a list. We'll examine each step in the code example to understand how ColdFusion handles the data extraction and presentation. Setting Up the Query In this example, a simple SQL query is used to retrieve department information from a database. The query selects two fields, Dept_ID and Dept_Name , from the DEPARTMENTS table, using a ColdFusion <cfquery> tag. This tag is essential for executing SQL commands in...
Read more