Querying SQL 2000 Server from ColdFusion - Modifying the query and improving the display
(Page 4 of 4 )
The query shown in the previous section returns 27 rows. In some cases this can be very large, and it is always a good practice to return only the required number of rows to improve performance and display. The next code improves upon the previous by limiting the rows to a maximum of 10, with the third row as the starting element. It also supplies the missing column for the row number as well as adding some style to the table. The correction and additions to the code are highlighted.
<!--select au_lname, au_fname, phone, city, zip from authors-->
<cfparam name="MaxRows" default="10">
<cfparam name="StartRow" default="3">
<cfquery name="GetAuthors" datasource="CFSQL">
select au_lname, au_fname, phone, city, zip from authors
order by au_lname
</cfquery>
<table border="1" bgcolor="lime">
<tr>
<!--Column headers-->
<!--add a column for row number-->
<td>Row Number</td>
<td>Last Name</td>
<td>First Name</td>
<td>Telephone</td>
<td>City</td>
<td>Zip Code</td>
</tr>
<!--output of query-->
<cfoutput query="GetAuthors" startrow="#StartRow#" maxrows="#MaxRows#">
<tr>
<td>#GetAuthors.CurrentRow#</td>
<td bgcolor="lightblue">#au_lname#</td>
<td bgcolor="lightblue">#au_fname#</td>
<td bgcolor="lightblue">#phone#</td>
<td bgcolor="lightblue">#city#</td>
<td bgcolor="lightblue">#zip#</td>
</tr>
</cfoutput>
When the above query is browsed you see the following displayed. The row starts with the third row and contains 10 rows as set by the <cfparam/> tags at the beginning of the code.
Summary
This tutorial is very basic and will help the beginners to start database programming with ColdFusion MX Server, IIS and backend MS SQL Server 2000. The ColdFusion MX server's administration panel makes it painless to connect to a variety of databases, without worrying about the varied syntax of the connection string or the JDBC/ODBC interface information. The administrative panel also provides advanced features to set other operating parameters, as well as a one click button to verify the connectivity without writing a line of code.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
