home Links Articles Books Past Meetings Photos SiteMap
The MDCFUG is sponsored by TeraTech. Visit us at www.TeraTech.com

Please send
comments/questions to

michael@
teratech.com

 

PAGE A:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

 

<html>

<head>

                <title>Untitled</title>

</head>

 

<body>

<cfquery datasource="data" name="getdata">

SELECT * FROM Members

</cfquery>

 

<cfoutput query="getdata">

<a href="member_edit.cfm?member_id=#id#">#id#</a>,

#firstname#, #lastname#, #age#, #address1#, #address2#, #dob#

<a href="member_delete_submit.cfm?member_id=#id#"> Delete </a><br>

</cfoutput>

<br>

<a href="member_add.cfm">ADD Record</a><br>

</body>


</html>

 


Notes: The HTML above produces the figure above.  The numbers on the left side of the query data are used to link the user to another page where updates are performed.  The tag <a href="member_edit.cfm?member_id=#id#">#id#</a>,  is used to accomplish the link to the member_edit.cfm page,  passing a variable (member_id) to that page.  The number are values from the field named ID in the Access Data.

The tag <a href="member_delete_submit.cfm?member_id=#id#"> Delete </a> work simular to the before mention tag.  The difference is the link to the page called member_delete_submit.cfm.  Notice that an member_id is being past.  This is used to locate the record you wish to delete.

 

The tag <a href="member_add.cfm">ADD Record</a> create a submit button, labeled “Add Record” to allow the user to add records to the database.  No member_id needs to be past since we are creating a new record.


 

PAGE B:

 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

 

<html>

<head>

                <title>Add (SQL)</title>

</head>

<h2>Add Record</h2>

<body>

<form action="member_edit_submit.cfm" method="post">  

Enter Field Values You Wish To Add:<br>

<br>

First Name: <input type="Text" name="firstname" size=30 value><br>

Last Name: <input type="text" name="lastname" size=30><br>

Age: <input type="text" name="age" size=3><br>

Address: <input type="text" name="address1" size=50><br>

Address: <input type="text" name="address2" size=50><br>

State: <input type="text" name="state" size=2><br>

Date Of Birth: <input type="text" name="dob" size=12><br>

<!--<input type="hidden" name="dob_date">-->

<br>

<br>

<input type="Submit" value="Add Record">

</form>

 

</body>

</html>


 


Notes:

The above HTML creates this web page.  Please make sure the </form> is at the bottom of the page, after your final submit button,  not at the end of the line with  tage <form> on it.  This mistake took me hours to figure out.  And I did not get it until Micheal helped me out.

When the Add Record button is pressed the tage <form action="member_add_submit.cfm" method="post"> calls the member_add_submit.cfm page.  All variables created on the calling  page can be used on the page called.


PAGE: C

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

 

<html>

<head>

                <title>Untitled</title>

</head>

 

<body>

<CFQUERY Datasource = "data" Name="AddMember">

INSERT INTO Members (FirstName, LastName, Age, Address1, Address2, State, DOB)

VALUEs ('#firstname#', '#lastname#',#age#,'#address1#','#address2#','#state#','#dob#')

</CFQUERY>

<cfoutput>


Record Added

</cfoutput>

 

</body>

</html>

 

 

Notes:

This is where we make our money.  This is the page that submits your SQL to the datasoure (“data”).  The <CFQUERY Datasource = "data" Name="AddMember"> defines the datasource and labels the query with the name “AddMember”.  The INSERT statement is the standard insert statement that  you can read about the Access (copy of statement provide below).  The <CFOUTPUT> outputs to the screen “Records Add”, so that the user will know that some kind of action has taken place.


PAGE D:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

 

<html>

<head>

                <title>Edit (SQL)</title>

</head>

<h2>Edit Record</h2>

<body>

<form action="member_edit_submit.cfm" method="post">  

<cfquery datasource="data" name="editrecord">

SELECT * FROM Members WHERE ID = #member_id#

</cfquery>

 

<cfoutput query="editrecord">

<input type="hidden" name="member_id" value=#member_id#>

First Name: <input type="Text" name="firstname" size=30 value=#firstname#><br>

Last Name: <input type="text" name="lastname" size=30 value=#lastname#><br>

Age: <input type="text" name="age" size=3 value=#age#><br>

Address: <input type="text" name="address1" size=50 value=#address1#><br>

Address: <input type="text" name="address2" size=50 value=#address2#><br>

State: <input type="text" name="state" size=2 value=#state#><br>

Date Of Birth: <input type="text" name="dob" size=12 value=#dob#><br>

</cfoutput>

<br>

<br>

<input type="Submit" value="Update Record">

</form>

 

</body>

</html>

 


Notes:

The Edit Record screen works like the Add Record screen.  The thing  to notice here is that when the number is selected the Edit Record screen is populated with the values of the record selected.  This is accomplished by the executing a query before the page is shown using the following code:

<cfquery datasource="data" name="editrecord">

SELECT * FROM Members WHERE ID = #member_id#

</cfquery>

 

Remember from the first screen the member_ id was past to this screen like so:

                <a href="member_edit.cfm?member_id=#member_id#">#id#</a>


PAGE E:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

 

<html>

<head>

                <title>Update (SQL)</title>

</head>

<h2>Update Record</h2>

<body>

<CFQUERY Datasource = "data" Name="updatemember">

UPDATE Members SET

                FirstName = '#firstname#',

                LastName = '#lastname#',

                Age = #age#,

                Address1 = '#address1#',

                Address2 = '#address2#',

                State = '#state#',

                DOB = #dob#

WHERE ID = #member_id#

</CFQUERY>

<cfoutput>

Record Updated

</cfoutput>

</body>

</html>


 


Notes:

Same as the Add page but instead of an INSERT an UPDATE is being perforned.

 


PAGE F:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

 

<html>

<head>

                <title>Delete Recod</title>

</head>

 

<body>

<CFQUERY Datasource = "data" Name="deleterecord">

DELETE * FROM Members WHERE ID = #member_id#

</CFQUERY>

<cfoutput>

Record Deleted

</cfoutput>

 

</body>

</html>


 


Notes:

If  you have gotten this far and have understood whats been going on then you will see that this is simular to the rest.  <CFQUERY Datasource = “data”  Name=”deleterecord”> is naming  and labeling your data source and the delete statement the same DELETE  (copy provided below) statement in Access.  Remember the member_id is being past in to the query form the other form


ADDITIONAL NOTES:

 

INSERT

Adds a record or multiple records to a table. This is referred to as an append query.

 

Syntax

 

Multiple-record append query:

 

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]

SELECT [source.]field1[, field2[, ...]

FROM tableexpression

 

Single-record append query:

 

INSERT INTO target [(field1[, field2[, ...]])]

VALUES (value1[, value2[, ...])

 

The INSERT INTO statement has these parts:

 

Part         Description

target      The name of the table or query to append records to.

externaldatabase  The path to an external database. For a description of the path, see the IN clause.

source    The name of the table or query to copy records from.

field1, field2           Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.

tableexpression    The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query.

value1, value2       The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').

 

UPDATE

 

Creates an update query that changes values in fields in a specified table based on specified criteria.

 

Syntax

 

UPDATE table

SET newvalue

WHERE criteria;

 

The UPDATE statement has these parts:

 

Part         Description

table       The name of the table containing the data you want to modify.

newvalue               An expression that determines the value to be inserted into a particular field in the updated records.

criteria    An expression that determines which records will be updated. Only records that satisfy the expression are updated.

 

DELETE

 

Creates a delete query that removes records from one or more of the tables listed in the FROM clause that satisfy the WHERE clause.

 

Syntax

 

DELETE [table.*]

FROM table

WHERE criteria

 

The DELETE statement has these parts:

 

Part         Description

table       The optional name of the table from which records are deleted.

table       The name of the table from which records are deleted.

criteria    An expression that determines which records to delete.


Home | Links | Articles | Past Meetings | Meeting Photos | Site Map
About MDCFUG | Join | Mailing List |Forums | Directions |Suggestions | Quotes | Newbie Tips
TOP

Copyright © 1997-2017, Maryland Cold Fusion User Group. All rights reserved.
< >