Ask a Question related to Coldfusion Database Access, Design and Development.
-
Jabez Clegg #1
Displaying the correct Data from Access To CF
Hi There!!!
I am just coming to end of a university project which i am working on, in
which buyers of used vehicle are able access an MS Access database, which
contains information about wheather a vehicle has been reported as stolen or
not. The system is similar the AA car data checker.
I am having Two problems with this.
1)The problem i am having is that when a vehicle registration number is
entered it is bringing up all the data
relating to all the vehicles in my tables. i only want it to bring up data
from the record which matches the registration
field. I have tried the Where statement is the query but i keep getting an
error message.
2) How would i'd go about filitering the records so if the Registration
entered in the
Text field dosnt match the registration in the database no data is bought up
at all.
Below is a list of the code i am using.
Please if anybody can help it would be most appreciated. Please Help if you
can.
Thank You In Advance.
Main Page
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<!-- DW6 -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Project Website</title>
<link rel="stylesheet" href="3col_leftNav.css" type="text/css">
<style type="text/css">
<!--
.style1 {font-size: x-large}
-->
</style>
</head>
<body>
<div id="masthead">
<h1 align="center" class="style1" id="siteName">Car Data Checker - MMU
Project<img src="fd2767e6.jpg" width="155" height="118" align="right"></h1>
<p align="right"> </p>
<div id="globalNav">
<p><a href="#">Home</a> | <a href="Data%20Checker.htm">Car Data Check
</a>| <a href="Review.htm">Vehicle Reviews</a>| |<a href="Safety And
Maintainence.htm"> Saftey & Maintaintence</a>| About Us| Site Map </p>
</div>
</div>
<div id="navBar">
<div id="sectionLinks">
<h3 align="center">Importing / Exporting Vehicles </h3>
<ul>
<li>
<div align="center"><a href="Importing a Vehicle.htm">Importing a
Vehicle </a>
<ul>
<li><a href="Commercial%20Imports.htm">Commercial Importers of
New Vehicles</a></li>
<li>
<a href="Permanent%20&%20Temporary%20Imports.htm">Per manent /
Tempory Export</a></li>
</ul>
</div>
</li>
</ul>
</div>
<div class="relatedLinks">
<h3>Consumer Advice </h3>
<ul>
<li><a href="#">Buying a Vehicle </a></li>
<li><a href="#">Seatbelt Exemption</a></li>
<li><a href="#">Release of Information </a></li>
<li><a href="#">Volentry Code of Practise for Private Car Parking
Enforcment </a></li>
<li><a href="#">Your Legal Obligations as a Driver and / or Keeper of a
Vehicle </a></li>
<li><a href="#">Data Protection </a></li>
</ul>
</div>
<div class="relatedLinks">
<ul>
<li><a href="#">Site Map </a></li>
<li><a href="#">Contact Us </a></li>
<li><a href="#">Privacy Policy </a></li>
</ul>
</div>
</div>
<!--end navBar div -->
<!--end headlines -->
<div id="content">
<div class="feature">
<DIV style="FONT-WEIGHT: bold; FONT-SIZE: 17px; TEXT-ALIGN: center"
width:"100%">
<form name="form2" method="post" action="">
Data Checker
</form>
</DIV>
<p>Please Enter Your Vehicle Details Below</p>
<form name="form1" method="post" action="Example2.cfm">
<table align="center" bgcolor="#003399">
<TR>
<TD Align ="Right">
Vehicle Registration:
</TD>
<td>
<input type="text" name="Vehicle_Registration" maxlength="10">
</td>
</TR>
<td colspan="2" align="center">
<input type="submit" value="Login">
</td>
</tr>
</table>
</form>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p>
<!--Make a Link to Terms and Conditions
-->
</p>
<p> </p>
<p><BR>
</p>
</div>
<div class="story"> </div>
</div>
<!--end content -->
<div id="siteInfo">
<div align="center"><a href="#">About Us</a> | <a href="#">Site
Map</a> | <a href="#">Privacy Policy</a> | <a href="#">Contact Us</a> |
©2003
Company Name </div>
</div>
<br>
</body>
</html>
This Is Displaying My Results
<cfquery name="Searching" datasource="project">
SELECT Car_Registration, Make, Model, Colour, Engine
FROM Vehicle
</cfquery>
<cfquery name="Status" datasource="project">
SELECT *
FROM Status
</cfquery>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Searching The Database</title>
</head>
<body>
<table>
<TR>
</TR>
<cfoutput query="Searching">
<tr>
<td>
<font size="+1"><b>#CurrentRow# : #Car_Registration#</b></font></BR>
<font size="+1"><i>#Make# #Model# #Colour# #Engine#</i></font>
</td>
<!--- <td>Released: #DateFormat(DateInTheaters)# </td> --->
</tr>
</cfoutput>
<table>
<TR>
</TR>
<cfoutput query="Status">
<tr>
<td>
<font size="+1"><b>#CurrentRow# : #Car_Registration#</b></font></BR>
<font size="+1"><i>#Stolen# #Insurance_Write_Off# #HPI# #No_Reports#
</i></font>
</td>
<!--- <td>Released: #DateFormat(DateInTheaters)# </td> --->
</tr>
</cfoutput>
</table>
</body>
</html>
This is the Catch method to make sure a The Registration field is not left
blank
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Try N Catch Query</title>
</head>
<body>
<CFIF Len (Trim(Vehicle_Registration)) IS 0>
<H1> Error! Registration Cannot Be Left Blank</H1>
<cfabort>
</CFIF>
<center>
<H1>Please Select The Link Below To Continue</H1>
<a href="Results.cfm">Continue</a><p></p>
<h1>Please Select This Option To Go Back To Previous Page</h1>
<a href="Data checker 3.cfm">Back To Previous Step</a>
</center>
</body>
</html>
Jabez Clegg Guest
-
php data correct on html ouput but output to flash isincorrect
Not sure whats going on. Information sent back from database is correct so it must be my for statement. var nextY = 0; var nextY2 = 18; for... -
#25177 [Bgs]: Sha1 doesnt work correct with data greater then 2kB
ID: 25177 User updated by: a dot lunkeit at signcubes dot com Reported By: a dot lunkeit at signcubes dot com Status: ... -
#25177 [Opn->Bgs]: Sha1 doesnt work correct with data greater then 2kB
ID: 25177 Updated by: iliaa@php.net Reported By: a dot lunkeit at signcubes dot com -Status: Open +Status: ... -
#25177 [Opn]: Sha1 doesnt work correct with data greater then 2kB
ID: 25177 User updated by: a dot lunkeit at signcubes dot com Reported By: a dot lunkeit at signcubes dot com Status: ... -
#25177 [Opn->Fbk]: Sha1 doesnt work correct with data greater then 2kB
ID: 25177 Updated by: derick@php.net Reported By: a dot lunkeit at signcubes dot com -Status: Open +Status: ... -
philh #2 Re: Displaying the correct Data from Access To CF
As I explained in your cross-post, you have to pass the form variable value to
a WHERE clause in the SELECT statement on the action page. What you have right
now, <cfquery name='Searching' datasource='project'> SELECT Car_Registration,
Make, Model, Colour, Engine FROM Vehicle </cfquery> will ALWAYS return all of
the records. You're not narrowing the results. That's where the WHERE clause
comes in. Look up SQL syntax for the use of the WHERE clause. As I said, it
will have to narrow the results to records in which the registration value
equals the passed value from the form. Good luck.
philh Guest
-
Tulsa #3 Re: Displaying the correct Data from Access To CF
Using the SQL Where statement should solve both problems if I am understanding
you.
<cfquery name="Searching" datasource="project">
SELECT Car_Registration, Make, Model, Colour, Engine
FROM Vehicle
WHERE Car_registration = #form.Vehicle_Registration# <!---If the datatype is
text, for car_registration, you will need single quotes around the
#form.vehicle_registration#--->
</cfquery>
Also, cf has built in validation. To get rid of that catch code at the end,
change all <input> and <form> tags to <cfinput ....> and <cfform....>this
allows you use the attributes "required" and "message". If the field is
required and the user leaves it blank, upon submitting a popup will appear
displaying the message. Heres an example:
<cfform name="form2" method="post" action="somepage.cfm">
<cfinput type = "text" name = "vehicle_registration" maxlength = "10" required
= "yes" message = "Please Enter Registration">
Tulsa Guest
-
micb #4 Re: Displaying the correct Data from Access To CF
I am having a similar problem...
I have a LogIn page and want to use session variables to remember the LogIn
Name.. I have turned session management on in the application.cfm
--- code snippet---
<cfapplication name="secureTest" sessionmanagement="yes"
sessiontimeout="#CreateTimespan(0,0,5,0)#">
This is the code for my Login
<cfform action="#MM_loginAction#" method="post">
<table height="133">
<tr>
<td><h4><font color="#000000" face="Geneva, Arial, Helvetica,
sans-serif">StaffID</font></h4></td>
<td><cfinput type="text" name="StaffID" required="yes" message="Please enter
your Staff ID">
<input type="hidden" name="StaffID_required" value="Please enter your Staff
ID."></td>
</tr>
<tr>
<td><h4><font face="Geneva, Arial, Helvetica,
sans-serif">Password</font></h4></td>
<td><cfinput type="password" name="Password" required="yes" message="Please
enter your password.">
<input type="hidden" name="Password_required" value="Please enter a
password."></td>
</tr>
<tr align="center">
<td colspan="2"> <input type="submit" value="Login"></td>
</tr>
</table>
</cfform>
The Login is working correctly and it takes you onto a page that shows a list
of events (which is working properly) there is then a link in this page that
shows the events a person is booked for. Its currently showing all the events
that everyone is booked on for...
This is the query I have in this page
<cfquery name="Shifts" datasource="BookingSystemDSN">
SELECT Job.Event, Job."Date", Location.LocationName
FROM Job, Location, Employee,
EmpShifts WHERE Location.LocationID = Job.LocationID
AND EmpShifts.EmpID = Employee.EmpID
AND EmpShifts.JobID = Job.JobID
and employee.employeeID = '#session.empID#'
</cfquery>
Its not recognising that a user has logged in...
Any suggestions where I am goin wrong?
Thanks
Michelle
micb Guest
-
Tulsa #5 Re: Displaying the correct Data from Access To CF
Micb,
I know that is not all of your code, but make sure that the page your login
form calls has the code
<cfset session.empID = TheEmployee'sID> (replace TheEmployee'sID with whatever
variable is storing the ID, on your form it looks like form.StaffID)
Also your session variables are set to timeout after 5 minutes (e.g.
sessiontimeout="#CreateTimespan(0,0,5,0)) so session.empID will only be valid
for five minutes.
Tulsa Guest
-
micb #6 Re: Displaying the correct Data from Access To CF
Thanks Tulsa,
Its getting really fustrating. I tried what you said but it still comes up
with this error.
"Element STAFFID is undefined in FORM"
---------------- My Code for LogIn Page -------------
<cfif IsDefined("URL.MM_logout") AND URL.MM_logout EQ "1">
<cflock scope="Session" type="Exclusive" timeout="30" throwontimeout="no">
<cfset Session.MM_Username="">
<cfset Session.MM_UserAuthorization="">
</cflock>
<cfset MM_logoutRedirectPage="LogOut.cfm">
<cfif MM_logoutRedirectPage EQ "">
<cfset MM_logoutRedirectPage=CGI.SCRIPT_NAME>
</cfif>
<cfset
MM_logoutQuery=ListDeleteAt(CGI.QUERY_STRING,ListC ontainsNoCase(CGI.QUERY_STRING
,"MM_logout=","&"),"&")>
<cfif MM_logoutQuery NEQ "">
<cfif Find("?",MM_logoutRedirectPage) EQ 0>
<cfset MM_logoutRedirectPage=MM_logoutRedirectPage & "?" &
MM_logoutQuery>
<cfelse>
<cfset MM_logoutRedirectPage=MM_logoutRedirectPage & "&" &
MM_logoutQuery>
</cfif>
</cfif>
<cflocation url="#MM_logoutRedirectPage#" addtoken="no">
</cfif>
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.StaffID")>
<cfset MM_redirectLoginSuccess="BookingIn.cfm">
<cfset MM_redirectLoginFailed="Main_FailedLogIn.cfm">
<cfset session.empID = form.staffID />
<cfquery name="MM_rsUser" datasource="BookingSystemDSN">
SELECT EmpID,Password FROM Employee WHERE EmpID='#FORM.StaffID#' AND
Password='#FORM.Password#'
</cfquery>
<cfif MM_rsUser.RecordCount NEQ 0>
<cftry>
<cflock scope="Session" timeout="30" type="Exclusive">
<cfset Session.MM_Username=FORM.StaffID>
<cfset Session.MM_UserAuthorization="">
</cflock>
<cfif IsDefined("URL.accessdenied") AND false>
<cfset MM_redirectLoginSuccess=URL.accessdenied>
</cfif>
<cflocation url="#MM_redirectLoginSuccess#" addtoken="no">
<cfcatch type="Lock">
<!--- code for handling timeout of cflock --->
</cfcatch>
</cftry>
</cfif>
<cflocation url="#MM_redirectLoginFailed#" addtoken="no">
<cfelse>
<cfset MM_LoginAction=CGI.SCRIPT_NAME>
<cfif CGI.QUERY_STRING NEQ "">
<cfset MM_LoginAction=MM_LoginAction & "?" & CGI.QUERY_STRING>
</cfif>
</cfif>
---------------and this is the page the Login takes you to
---------------------------
<cfset session.empID = form.StaffID>
<cfif IsDefined("URL.MM_logout") AND URL.MM_logout EQ "1">
<cflock scope="Session" type="Exclusive" timeout="30" throwontimeout="no">
<cfset Session.MM_Username="">
<cfset Session.MM_UserAuthorization="">
</cflock>
<cfset MM_logoutRedirectPage="LogOut.cfm">
<cfif MM_logoutRedirectPage EQ "">
<cfset MM_logoutRedirectPage=CGI.SCRIPT_NAME>
</cfif>
<cfset
MM_logoutQuery=ListDeleteAt(CGI.QUERY_STRING,ListC ontainsNoCase(CGI.QUERY_STRING
,"MM_logout=","&"),"&")>
<cfif MM_logoutQuery NEQ "">
<cfif Find("?",MM_logoutRedirectPage) EQ 0>
<cfset MM_logoutRedirectPage=MM_logoutRedirectPage & "?" &
MM_logoutQuery>
<cfelse>
<cfset MM_logoutRedirectPage=MM_logoutRedirectPage & "&" &
MM_logoutQuery>
</cfif>
</cfif>
<cflocation url="#MM_logoutRedirectPage#" addtoken="no">
</cfif>
<cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">
<cfset
MM_Username=Iif(IsDefined("Session.MM_Username")," Session.MM_Username",DE(""))>
<cfset
MM_UserAuthorization=Iif(IsDefined("Session.MM_Use rAuthorization"),"Session.MM_U
serAuthorization",DE(""))>
</cflock>
<cfif MM_Username EQ "">
<cfset MM_referer=CGI.SCRIPT_NAME>
<cfif CGI.QUERY_STRING NEQ "">
<cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>
</cfif>
<cfset MM_failureURL="LogIn.cfm?accessdenied=" &
URLEncodedFormat(MM_referer)>
<cflocation url="#MM_failureURL#" addtoken="no">
</cfif>
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
micb Guest
-
Tulsa #7 Re: Displaying the correct Data from Access To CF
I seen this login script before and it is very confusing. I see you have
<cfset session.empID = form.staffid> on both pages. Take out the <cfset
session.empid...>. in the page the login takes you to(i know I told you to do
the opposite earlier) and move it to just after the query in the login page,
i.e.
<cfquery name="MM_rsUser" datasource="BookingSystemDSN">
SELECT EmpID,Password FROM Employee WHERE EmpID='#FORM.StaffID#' AND
Password='#FORM.Password#'
</cfquery>
<cfif MM_rsUser.RecordCount NEQ 0>
<cfset session.empID = form.staffID>
Tulsa Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
