Developer : Tariq Chaudahry
Tools: SQL Server 2000,Macromedia Cold Fusion Studio 5,SQL,Javascript,IIS 5.0 and HTML
 Inserting data using Stored Procedure,Cold Fusion,SQL Server 2000,Windows 2000 and IIS 5.0. |
|
Navigating the Database records using Next and Previous Buttons
|
|
Displaying data from Database using Cold Fusion,SQL,SQL Server 2000
|
|
Banking application using Session Variables(viewing checking,saving accounts on basis of Id)
|
|
Inserting, data using Cold Fusion 5.0,SQL Server 2000,Windows 2000 and IIS 5.0. |
|
Deleting database Records using Cold Fusion,SQL,SQL Server 2000 and Windows 2000 and IIS 5.0. |
|
Searching database Records using Cold Fusion,SQL,SQL Server 2000 and Windows 2000 and IIS 5.0. |
|
Updating database Records using Cold Fusion,SQL,SQL Server 2000 and Windows 2000 and IIS 5.0. |
|
Creating different Access levels using Session Variables in Cold Fusion(Tools:Cold Fusion Studio 5.0,SQL Server 2000 and Windows 2000 and IIS 5.0) |
Inserting data using Stored Procedure,Cold Fusion,SQL Server 2000,Windows 2000 and IIS 5.0.
<!--validate() is javascript function for validating form enteries-->
<script language="JavaScript">
function validate()
{
if(document.myform.f_name.value== ""
||document.myform.l_name.value== ""
||document.myform.address.value== ""
||document.myform.id.value== "")
{
alert("Please complete all the entries ,try again?")
return false;
}
<!-- isNaN function is for checking non numberic id(s)-->
if(isNaN(document.myform.id.value)== true)
{
alert(" id must be numberic?")
return false;
}
document.myform.submit()
return true;
}
</script>
</HEAD>
<BODY>
<FORM action="Insertdata.cfm" method="POST" id=form1 name=form1>
<Center>
<TABLE WIDTH="35%" BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD>First Name<input type="text" Name="f_name" size="10"> </TD>
<TD>Last Name<input type="text" Name="l_name" size="10"> </TD>
</TR>
<TR>
<TD>Address<input type="text" Name="address" size="10"> </TD>
<TD>ID<input type="text" Name="id" size="5"> </TD>
</TR>
<TR>
<TD><input type="button" Name="submit" value="Add" size="10" onClick="validate();"> </TD>
</TR>
</TABLE>
</center>
</FORM>
</BODY>
</HTML> Top
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>INSEERT INFORMATION</TITLE>
</HEAD>
<!-- sp_insert this is stored procedure which I created in SQL Server 2000-->
<CFSTOREDPROC PROCEDURE="sp_insert" datasource="tariqch" username="administrator" password="admin" Returncode="YES">
<cfprocparam type="in" CFSQLTYPE="CF_SQL_CHAR" DBVARNAME="@F_NAME" VALUE="#form.F_NAME#">
<cfprocparam type="in" CFSQLTYPE="CF_SQL_CHAR" DBVARNAME="@L_NAME" VALUE="#form.L_NAME#">
<cfprocparam type="in" CFSQLTYPE="CF_SQL_CHAR" DBVARNAME="@ADDRESS" VALUE="#form.ADDRESS#">
<cfprocparam type="in" CFSQLTYPE="CF_SQL_INTEGER" DBVARNAME="@ID" VALUE="#form.ID#">
<CFPROCRESULT NAME="BASICINFO">
</CFSTOREDPROC>
<!--Syntax of stored procedurs which I created in SQL server 2000-->
Create procedure sp_insert
( @f_name char(10),
@l_name char(10),
@address char(10),
@id numeric(9)
)
As
Insert into tblbiodata
values(@f_name,@l_name,@address,@id)
<b> <font color="#800000">A new record with id</font><font color="blue">
<cfoutput>#Id#</cfoutput></font><font color="maroon"> has been added</font></b><br><br>
<table>
<tr>
<th bgcolor="#ffffff">First Name</th>
<th bgcolor="#00ffff">Last Name</th>
<th bgcolor="#00ffff">Address</th>
<th bgcolor="#00ffff">ID</th>
</tr>
<CFOUTPUT>
<tr> <td>#F_NAME#</td></tr>
<tr> <td>#L_NAME#</td></tr>
<tr> <td>#ADDRESS#</td></tr>
<tr> <td>#ID#</td></tr>
</table>
</CFOUTPUT>
</body>
</HTML> Top
Navigating Database Records using Next and Previous Buttons.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Displaying Records</title>
</head>
<body>
<cfquery datasource="tariqch" name="qdisplay">
Select * from tblbiodata
</cfquery>
<cfset MaxRows="5">
<cfparam Name="start" default="1">
<center>
<table width="50%">
<tr bgcolor="lightyellow">
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Id</th>
</tr>
<cfoutput query="qdisplay" startrow="#start#" MAXROWS="#MaxRows#">
<tr bgcolor="yellow">
<td align="Center">#f_name#</td>
<td align="center">#l_name#</td>
<td align="center">#address#</td>
<td align="center">#id#</td>
</tr>
</cfoutput>
<tr>
<cfset PrevRec =Start - MaxRows>
<cfset NextRec =Start + MaxRows>
<td>
<cfif PrevRec GTE 1>
<cfoutput>
<form action="cfrecsdisplay.cfm" method="post">
<Input type="hidden" Name="start" Value="#PrevRec#">
<Input type="hidden" Name="F_Name" Value="#qdisplay.F_Name#">
<Input type="hidden" Name="L_Name" Value="#qdisplay.L_Name#">
<Input type="hidden" Name="Address" Value="#qdisplay.Address#">
<Input type="hidden" Name="id" Value="#qdisplay.id#">
<input type="submit" Name="psubmit" value="Previous #MaxRows#">
</form>
</cfoutput>
</cfif>
</td>
<td>
<cfif NextRec LTE qdisplay.recordcount>
<cfoutput>
<form action="cfrecsdisplay.cfm" method="post">
<Input type="hidden" Name="start" Value="#NextRec#">
<Input type="hidden" Name="F_name" Value="#qdisplay.F_Name#">
<Input type="hidden" Name="L_Name" Value="#qdisplay.L_Name#">
<Input type="hidden" Name="Address" Value="#qdisplay.Address#">
<Input type="hidden" Name="id" Value="#qdisplay.id#">
<Input type="submit" Name="Nsubmit" value="Next #MaxRows#">
</form>
</cfoutput>
</cfif>
</td>
</tr>
</table>
</center>
</body>
</html> Top
Displaying Database Records
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Show records</title>
</head>
<body>
<form>
<!--</form> action="update.cfm" method="post">-->
<cfquery name="qshowall" datasource="info">
Select * from tblinfo
</cfquery>
<table width="55%" cellspacing="2" cellpadding="2" align="center" border="2">
<tr bgcolor="#00ffff">
<font color="maroon"> <th>First Name</th>
<th>Last Name</th>
<th>City</th>
<th>State</th>
<th>Id</th></font>
<th colspan="2">Action</th>
</tr>
<cfoutput query="qshowall">
<tr>
<td>#Fname#</td>
<td>#lname#</td>
<td>#City#</td>
<td>#State#</td>
<td>#Id#</td>
<Input type="hidden" Name="id" value="#id#">
<td><a href= "delete.cfm?id=#id#">Delete</a></td>
<td><a href="update.cfm?id=#id#">Update</a></td>
</tr>
</cfoutput>
</table>
</form>
</body>
</html> Top
Database Driven Banking Application using Session Variables
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
Date: April 24,2003
<html>
<head>
<title>Login</title>
</head>
<body bgcolor="">
<form action="request.cfm" method="post" name="frm1">
<div align="center"><strong><font size="+4" color="#800000"><strong>Log In</strong></font></strong></div>
<div align="center">
<table width="35%" border="1" cellpadding="2" cellspacing="4" bgcolor="#ffff00">
<tr>
<td>
Customer Name<input type="Text" name="custname" size="10">
Password
<input type="password" name="custpwd" size="10">
</td>
</tr>
<tr>
<td>
<div align="center">
<input type="submit" name="btnsubmit" value=" Submit" size="10" >
</div>
</td>
</tr>
</table></div>
</form>
</body>
</html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>approval page</title>
</head>
<body>
<!--- Check to see if the session variable exists, if not set it. --->
<CFPARAM name="session.custpwd" default="N">
<CFPARAM name="session.custname" default="N">
<!--- Check to see if the session variable exists, if not redirect them to the login page --->
<CFIF IsDefined("session.custpwd") EQUAL "No"> <CFLOCATION URL="/login.cfm"></CFIF>
<!--this will display the error message if nothing was entered-->
<cfif form.custpwd is "" or form.custname is "" >
<script language="javascript">
alert("Error: You left something blank,so try again??")
history.go(-1);
</script>
</cfif>
<cfquery name="checkuser" datasource="tariqch">
Select * from custlogin
where custpwd=#custpwd# and custname='#custname#'
</cfquery>
<cfif form.custpwd is NOT checkuser.custpwd and form.custname is NOT checkuser.custname>
<script language="javascript">
alert("Error: You do not exist in the database,check your name and password??")
history.go(-1);
</script>
<cfelse>
<cfset Session.custname="#checkuser.custname#">
<cfset Session.custpwd="#checkuser.custpwd#">
</cfif>
<cfoutput query="checkuser">
<font size="10" color="darkblue">Hi you just logged in and your name is #session.custname#you will be able to see some useful stuff</font>
<div align="center">
<a href="checking.cfm?custpwd=#session.custpwd#">Checking Account</a><br>
<a href="Saving.cfm?custpwd=#session.custpwd#">Saving Account</a>
</div>
</cfoutput>
</body>
</html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled</title>
</head>
<body>
<h1>This is your Saving Account information</h1>
<cfquery name="qsaving" datasource="tariqch">
Select custlogin.custname,tblsaving.amount,tblsaving.date
from tblsaving ,custlogin
where tblsaving.id = #session.custpwd#
and custlogin.custname = '#session.custname#'
</cfquery>
<div align="center">
<table cellpadding="4" cellspacing="4">
<Tr bgcolor="#ffff00">
<th>Name</th><th>Amount</th><th>Date</th>
</tr>
<tr>
<cfoutput query="qsaving">
<td>#custName#</td>
<td> #amount# </td>
<td>#date#</td>
</tr>
<tr><td><a href="checking.cfm?custpwd=#custpwd#">Checking Account</a></td></tr>
</cfoutput>
</table></div>
</body>
</html>
Top
Inserting Data using Cold Fusion,SQL Server 2000,IIS 5.0 and SQL and etc.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<script language="javascript">
function Chkblank()
{
if
( document.frm.fname.value == "" )
{
alert(" You left first name blank?")
return false
}
if( document.frm.lname.value == "" )
{
alert(" You left last name blank?")
return false
}
if(document.frm.city.value == "" )
{
alert(" You left city name blank?")
return false
}
if( document.frm.state.value == "" )
{
alert(" You left state name blank?")
return false
}
if(document.frm.id.value == "")
{
alert(" You left id number blank?")
return false
}
else
document.frm.submit()
return true
}
</script>
<title>Inserting Records</title>
</head>
<body> Top
<form action="Insert.cfm" Method="post" name="frm">
<table bgcolor="lightyellow" width="40%" height="25%" align="center" border="0">
<tr>
<td>First Name<input type="text" Name="fname" size="8">
Last Name <input type="text" Name="lname" size="8"></td>
</tr>
<tr>
<td>City<input type="text" Name="city" size="8" width="14">
State<input type="text" Name="state" size="8"></td>
</tr>
<tr>
<td>Id<input type="text" Name="id" size="8"></td>
</tr>
<tr>
<td>
<input type="button" value="Insert" onclick="Chkblank()">
<input type="reset" Value="Clear"></td>
</tr>
</table>
</form>
</body>
</html> Top
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Insert data</title>
</head>
<body>
<cfquery name="qinsert" datasource="info">
Insert into tblinfo (fname,lname,city,state,id)
values('#fname#','#lname#','#city#','#state#',#id#)
</cfquery>
<strong><font size="+1" color="#800000">Inserted record</font>:</strong>
<table align="center" bgcolor="lightyellow" cellspacing="2" cellpadding="2" width="41%" border="2">
<tr>
<th>First name</th>
<th>Last Name</th>
<th>City</th>
<th>State</th>
<th>ID</th></font>
</tr>
<cfoutput>
<tr>
<td>#fname#</td>
<td>#lname#</td>
<td>#city#</td>
<td>#state#</td>
<td>#id#</td>
</tr>
</cfoutput>
</table>
<A href="showRecords.cfm">Show me all records</A>
</body>
</html> Top
Deleting Database Records
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Delete Records</title>
</head>
<body>
<cfquery name="qfordelete" datasource="info">
Select * from tblinfo
where id = #id#
</cfquery>
<cfoutput query="qfordelete">
<form>
<table border="1">
<tr>
<td><input type="text" Name="fname" Value="#fname#"></td>
<td><input type="text" Name="lname" Value="#lname#"></td>
</tr>
<tr>
<td><input type="text" Name="city" Value="#city#"></td>
<td><input type="text" Name="state" Value="#state#"></td>
</tr>
<tr>
<td><input type="text" Name="id" Value="#id#"></td>
</tr>
<tr>
<td><input type="submit" Name="btnsubmit" Value="Delete"></td></tr>
</table>
</form>
</cfoutput>
<cfquery datasource="info" name="qdelete">
Delete from tblinfo
where id =#id#
</cfquery>
</body>
</html>
Top
Searching Database Records
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<script language="javascript">
function blank()
{
if
(
document.frmsrch.fname.value == "" &&
document.frmsrch.lname.value == "" )
{
alert(" Enter some thing to search?")
return false
}
else
document.frmsrch.submit()
return true
}
</script>
<title>Search Form</title>
</head>
<body>
<div align="center"><strong><font size="+4" Color="#0000ff">Search</font></strong></div>
<form action="search.cfm" method="POST" name="frmsrch">
<table width="45%" border="1" cellspacing="1" cellpadding="1" align="center">
<tr>
<td> First Name <input type="Text" name="fname" size="10">
Last Name <input type="Text" name="lname" size="10">
</td>
</tr>
<tr>
<td><input type="button" Name="btnsumbit" Value="Search" onClick="blank()">
</td>
</tr>
</table>
</form>
</body>
</html>
Top
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Search Results</title>
</head>
<body>
<cfquery name="qsearchdata" datasource="info">
Select fname,lname,city,state,id from tblinfo
Where lname LIKE '#lname#%'
Order by fname, Lname
</cfquery>
<table width="55%" cellspacing="2" cellpadding="2" align="center" border="2">
<tr bgcolor="#00ffff">
<font color="maroon"> <th>First Name</th>
<th>Last Name</th>
<th>City</th>
<th>State</th>
<th>Id</th></font>
</tr>
<cfoutput query="qsearchdata">
<tr>
<td>#Fname#</td>
<td>#lname#</td>
<td>#City#</td>
<td>#State#</td>
<td>#Id#</td>
</tr>
</cfoutput>
</table>
</body>
</html>
Updating Database Records
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Show records</title>
</head>
<body topmargin="74%">
<form>
<cfquery name="qshowall" datasource="info">
Select * from tblinfo
</cfquery>
<table width="55%" cellspacing="2" cellpadding="4" align="center" bordercolor="800000">
<tr bgcolor="00ffff">
<font color="maroon">
<th>First Name</th>
<th>Last Name</th>
<th>City</th>
<th>State</th>
<th>Id</th></font>
<th colspan="2">Action</th>
</tr>
<cfoutput query="qshowall">
<TR bgcolor="#IIf(CurrentRow Mod 2, DE('ffffff'), DE('ffffcf'))#">
<td>#fname#</td>
<td>#lname#</td>
<td>#city#</td>
<td>#state#</td>
<td>#id#</td>
<Input type="hidden" Name="id" value="#id#">
<td><a href= "delete.cfm?id=#id#">Delete</a></td>
<td><a href="update.cfm?id=#id#">Update</a></td>
</tr>
</cfoutput>
</table>
</form>
</body>
</html>
Top
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Update</title>
</head>
<body topmargin="75%">
<cfquery name="qforupdate" datasource="info">
Select fname,lname,city,state,id from tblinfo
where id = #id#
</cfquery>
<cfoutput query="qforupdate">
<form action="doUpdate.cfm" method="post">
<center><table border="1" bgcolor="800080">
<tr>
<td><font color="ffffff">First Name</font>
<input type="text" Name="fname" Value="#fname#" size="12">
<font color="ffffff">Last Name</font>
<input type="text" Name="lname" Value="#lname#" size="12">
</td>
</tr>
<tr>
<td><font color="ffffff">City </font>
<input type="text" Name="city" Value="#city#" size="12">
<font color="ffffff">State</font>
<input type="text" Name="state" Value="#state#" size="12">
</td>
</tr>
<tr>
<td> <font color="ffffff">Id</font>
<Input type="text" Name="id" Value="#id#" size="4">
<input type="submit" Name="btnsubmit" Value="Update">
</td>
</tr>
</table>
</center>
</form>
</cfoutput>
</body>
</html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Perform update</title>
</head>
<body>
<cfquery datasource="info" name="qup">
UPDATE tblinfo
Set fname= '#fname#',
lname= '#lname#',
city = '#city#',
state = '#state#'
WHERE id = #id#
</cfquery>
</body>
</html> Top
Creating Different Access levels using Session Variables
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
Friday, January 16, 2004<br>
<HEAD>
<strong><font color="#800000">login form</font></strong>
</HEAD>
<BODY>
<FORM action="login_sys.cfm" method="POST" name="myform">
<Center>
<TABLE WIDTH="39%" height="20%" BORDER=0 CELLSPACING=2 CELLPADDING=2 bgcolor="lightyellow">
<TR>
<TD>User Id<input type="password" Name="userid" size="10"> </TD>
<TD>User<input type="text" Name="fname" size="10"> </TD>
</TR>
<TR>
<TD align="right"><input type="submit" Name="btnSubmit" value="Login">
<input type="reset" value="Clear">
</TD>
</TR>
</TABLE>
</center>
</FORM>
</BODY>
</HTML>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
Friday, January 16, 2004
<html>
<head>
<title>Welcome to the system</title>
</head>
<body>
<!--- if any field is blank then warn the user--->
<cfif form.userid is "" or form.fname is "" >
<script language="javascript">
alert("Error: You left something blank,so try again??")
history.go(-1);
</script>
</cfif>
<cfquery name="qryusers" datasource="tariqch">
Select * from tblNusers
where userid=#userid# and fname='#fname#'
</cfquery>
<!--- if there is not record send the user back to login screen--->
<cfif #qryusers.recordcount# is 0>
<script>
alert(" no record found")
history.go(-1)
</script>
<cfelse>
<CFSET session.userid="#qryusers.userid#">
<CFSET session.fname="#qryusers.fname#">
<CFSET session.title="#qryusers.title#">
<CFSET session.admin="#qryusers.admin#">
</cfif>
<cfoutput>
<cfif session.admin equal 1>
<font size="10" color="darkblue">You logged in as a #session.fname#,and your title is: #session.title# <br>
and you are an administrator and you can view the followings<br>
<a href="www.yahoo.com">Yahoo</a></font>
<!--- if user is not admin then send the user back to user menu--->
<cfelseif session.admin eq 0>
<script>
alert(" sorry,you r not an admin,so u can not access this menu,u will be send to user menu? ")
document.location="userMenu.cfm"
</script>
</cfif>
</cfoutput>
Top
</body>
</html>