• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

A DataSet just like VB.Net

This tutorial shows you how to create a "dataset" just like the one in VB.Net
In VB.Net you would create a dataset with "edit" button in an extra column.
Once you click "edit", you get an option to "update", "delete" or "cancel edit mode" 
This is just like a cfgrid tag. Although a cfgrid tag lets you bulk insert, bulk update or bulk delete, the dataset object does it one by one. But cfgrid is slower, and may give users Java errors, depending on their browser settings.


You will need two files for this project;
1. Dataset.cfm
2. UpdateDataset.cfm


Also I am using the "NorthWind" database in SQL Server

DATASET.CFM

<!---------------------------------------------------------------------------------------------------------------
This part is for the errors caught on page 2 "UdateDataSet.cfm"
If the query cannot be executed, <cfcatch> catches the exception, and <cflocation> returns you
to this page with the proper "errorcode" to display the proper message !
If insert/update query is executed, <cflocation> returns you back to this page with the proper
"ErrorCode = a"
--------------------------------------------------------------------------------------------------------------->
<cfif IsDefined('URL.ErrorCode')>
    <cfset a = "Updates Successful">
    <cfset b = "Datastore could not be updated">
    <cfset c = "Could not delete from datastore">
    <cfset d = "Error occured">
    <h2><cfoutput>#Evaluate(URL.ErrorCode)#</cfoutput></h2>
</cfif>
<!---------------------------------------------------------------------------------------------------------------
Below is a regular "select" query to the NorthWind database. For some reason "Select *" 
statements don't work.
--------------------------------------------------------------------------------------------------------------->
<cfquery name="DataSet" DataSource="NorthWind">
    Select EmployeeId,FirstName, LastName
    From Employees
</cfquery>
<!---------------------------------------------------------------------------------------------------------------
Build your HTML Tables. Use <cfform> tag inside <cfoutput>. This will generate as many 
<cfforms> as there are records.
--------------------------------------------------------------------------------------------------------------->
<table border="1">
    <cfoutput query="DataSet">
    <cfform action="UpdateDataSet.cfm">
    <tr>
<!---------------------------------------------------------------------------------------------------------------
Use a list loop to generate number of columns <td>s listed in your query !
--------------------------------------------------------------------------------------------------------------->
    <cfloop index="Columns" list="#DataSet.ColumnList#">
<!---------------------------------------------------------------------------------------------------------------
Define the primary key or table index. You define the table index, so you can run your update
statements and use this index in your where clauses
--------------------------------------------------------------------------------------------------------------->
    <cfset TableIndex = DataSet.EmployeeId>
        <td>
<!---------------------------------------------------------------------------------------------------------------
Now comes the fun part !
When you click "edit" button/hyperlink, it will pass the Table Index value to this conditional output !
Thus when you have an Index, you can generate "textboxes" as given below !
--------------------------------------------------------------------------------------------------------------->
        <cfif IsDefined('URL.Index') and URL.Index eq TableIndex>
            <cfif #Columns# eq "EmployeeId">
<!---------------------------------------------------------------------------------------------------------------
Since you need the TableIndex for update, if you should keep that field readonly. Thus, this can be
used effectively for update or delete query
--------------------------------------------------------------------------------------------------------------->
                <input type="text" name="#Columns#" value="#Evaluate(Columns)#" size="#Len(Evaluate(Columns))#" readonly>
            <cfelse>
<!---------------------------------------------------------------------------------------------------------------
Rest of the values can be in editable textboxes as given below !
--------------------------------------------------------------------------------------------------------------->
                <cfinput type="Text" name="#Columns#" value="#Evaluate(Columns)#" required="Yes" size="#Len(Evaluate(Columns))#">
            </cfif>
        <cfelse>
<!---------------------------------------------------------------------------------------------------------------
Other values that are not returned by TableIndex, should remain displayed on the webpage, 
as below;
--------------------------------------------------------------------------------------------------------------->
            #Evaluate(Columns)#
        </cfif>
        </td>
        </cfloop>
        <td>
<!---------------------------------------------------------------------------------------------------------------
the following block of code, changes the option buttons.
For example when you do not have a URL.Tableindex, the webpage should display the "edit" 
button. When a URL.TableIndex is passed, the buttons displayed should change to "update",
"delete" or "cancel" This is exactly what happens 
--------------------------------------------------------------------------------------------------------------->
        <cfif IsDefined('URL.Index')>
            <cfif IsDefined('URL.Index') and URL.Index eq TableIndex>
                <input type="Submit" name="Update" value="Update">
                <input type="Submit" name="Update" value="Delete">
<!---------------------------------------------------------------------------------------------------------------
The cancel button is to take you out of "edit mode" just like the VB.Net Dataset object's 
cancel button !
--------------------------------------------------------------------------------------------------------------->
                <input type="Button" name="Update" value="Cancel" onClick="window.location.href='#CGI.Script_Name#'" id="Button">
            <cfelse>
                <input type="Button" name="Update" value="Edit" onClick="window.location.href='?Index=#TableIndex#'" id="Button">
            </cfif>
        <cfelse>
            <input type="Button" name="Update" value="Edit" onClick="window.location.href='?Index=#TableIndex#'" id="Button">
        </cfif>
        </td>
    </tr>
</cfform>
</cfoutput>
</table>
<!---------------------------------------------------------------------------------------------------------------
Use a little bit of java script for consistency. You can use hyperlinks instead. 
But all buttons or all hyperlinks just looks better and uniform.
--------------------------------------------------------------------------------------------------------------->

UPDATEDATASET.CFM

<!---------------------------------------------------------------------------------------------------------------
I used a cfswitch statements to decide whether to "update" datastore or "delete" from datastore
--------------------------------------------------------------------------------------------------------------->
<cfswitch expression="#Form.Update#">
<!---------------------------------------------------------------------------------------------------------------
If the button on form said "Update" the following block executes to update the database
--------------------------------------------------------------------------------------------------------------->
    <cfcase value="Update">
<!---------------------------------------------------------------------------------------------------------------
try to update the dataset with the update sql statement
--------------------------------------------------------------------------------------------------------------->
        <cftry>
            <cflock timeout="30" throwontimeout="No" type="EXCLUSIVE">
            <cftransaction>
                <cfquery name="DeleteRow" datasource="NorthWind">
                    Update Employees
                    Set FirstName = '#Form.FirstName#',
                    LastName = '#Form.LastName#'
                    Where EmployeeId = #Form.EmployeeId#
                </cfquery>
            </cftransaction>
            </cflock> 
<!---------------------------------------------------------------------------------------------------------------
If updated properly, return to the previous page, with "errorcode = a" which "Success!"
--------------------------------------------------------------------------------------------------------------->
            <cflocation url="DataSet.cfm?ErrorCode=a" addtoken="No">
            <cfcatch type="Any">
<!---------------------------------------------------------------------------------------------------------------
If there were exceptions to updates, return to previous page with "errorcode = b" which was "Error updating the dataset" message
--------------------------------------------------------------------------------------------------------------->
                <cflocation url="DataSet.cfm?ErrorCode=b" addtoken="No">
            </cfcatch>
        </cftry>
    </cfcase>
<!---------------------------------------------------------------------------------------------------------------
same is true for deletes !
--------------------------------------------------------------------------------------------------------------->
    <cfcase value="Delete">
        <cftry>
            <cflock timeout="30" throwontimeout="No" type="EXCLUSIVE">
            <cftransaction>
                <cfquery name="DeleteRow" datasource="NorthWind">
                    Delete from Employees Where EmployeeId = #Form.EmployeeId#
                </cfquery>
            </cftransaction>
            </cflock> 
            <cflocation url="DataSet.cfm?ErrorCode=a" addtoken="No">
            <cfcatch type="Database">
                <cflocation url="DataSet.cfm?ErrorCode=c" addtoken="No">
            </cfcatch> 
        </cftry>
    </cfcase>
<!---------------------------------------------------------------------------------------------------------------
for any other types of goof-ups ..... always account for "other" contingency. 
--------------------------------------------------------------------------------------------------------------->
    <cfdefaultcase>
        <cflocation url="DataSet.cfm?ErrorCode=d" addtoken="No">
    </cfdefaultcase>
</cfswitch>

<!---------------------------------------------------------------------------------------------------------------
You are done !
--------------------------------------------------------------------------------------------------------------->

In any case, whether an update was successful or not, you will be thrown back to "DataSet.cfm" with the proper message.
If updated, the table will reflect changes, if not it will display error !!
<!---
If you try the delete statement on this particular database, be advised, your referential integrity will not allow deletes.
Take off the cftry catch statements to see the following error !

Error Executing Database Query. 
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Employees'. The conflict occurred in database 'Northwind', table 'Orders', column 'EmployeeID'. 
---> 
Hope you find this easy and useful !!