Solved

Problem displaying the right results from my form to the results table

Posted on 2004-10-07
9
200 Views
Last Modified: 2013-12-24
The History of this started from
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21155027.html

Thank you for your help.  I modified the query and it works fine but the result page does not seem to output the right results.

I want the users to search with ItemNo and any other data and have the result page show the search criteria. e.g if I search with ItemNo and Date, I want the reult page to show the itemno, date and any other data I want.

I have the following query for the result page:

<cfparam name="FORM.AgendaItemNo" default="">
<cfparam name="FORM.Comm_CouncilDate" default="">
<cfparam name="FORM.Comm_CouncilName" default="">
<cfquery name="spResults" datasource="SpeakerCard">
SELECT *
FROM SpeakerCard
WHERE AgendaItemNo IN ('#FORM.AgendaItemNo#', '#FORM.Comm_CouncilDate#', '#FORM.Comm_CouncilName#')
ORDER BY SpeakerCard.AgendaItemNo, Comm_CouncilDate, Comm_CouncilName</cfquery>

Then I have my output table.

How do I fix this
0
Comment
Question by:iolike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 12251318
Hi iolike,
 
what is borken? The question is not clear.
If you want to dispaly the form field in teh table you can do like from query it will be like that... if you want form variable then replace queryname.variable with form.youravariable
<cfoutput>
<table>
<tr>
<td>
#spresutls.yourvariable#
</td>
</tr>
<table>
</cfoutput>
Regards,
---Pinal
0
 
LVL 9

Expert Comment

by:CFDevHead
ID: 12251365
Yes, it is still unclear as to what you want to do.
0
 
LVL 9

Expert Comment

by:CFDevHead
ID: 12251426
>>" I want the reult page to show the itemno, date and any other data I want."

if the query works fine and you just want help outputing I would do something like this.

<cfoutput>
<table>
      <tr>
            <td>ItemNo</td>
            <td>Date</td>
            <td>Other Data</td>
      </tr>
      <cfloop query="spResults">
      <tr class="">#iif(current mod 2,'"altbg"','""')#
            <td>ItemNo</td>
            <td>Date</td>
            <td>Other Data</td>
      </tr>
      </cfloop>      
</table>
</cfoutput>
0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
LVL 35

Expert Comment

by:mrichmon
ID: 12251468
Perhaps showing an example of what output you are getting versus what output you want.....
0
 
LVL 6

Expert Comment

by:campbelc
ID: 12251642
Try this:


<cfparam name="form.ConNumber" default="%">
<cfparam name="form.ItemNo" default="">
<cfparam name="form.Name" default="">
<cfparam name="form.Date" default="">
<cfquery name="SearchForm" datasource="ChrisForTest">
  SELECT * FROM SpeakerCard WHERE 0=0
    <cfif form.ConNumber NEQ "">AND ConNumber LIKE '%#form.ConNumber#%'</cfif>
    <cfif form.ItemNo NEQ "">AND ItemNo = '#form.ItemNo#'</cfif>
    <cfif form.Name NEQ ""> AND Name= '#form.Name#'</cfif>
    <cfif form.Date NEQ ""> AND Date = '#form.Date#'</cfif>
</cfquery>

<title>Speaker Card Search Form</title>

<style type="text/css">
<!--
  .style1 {font-family: Arial, Helvetica, sans-serif;font-size: 14px;font-weight: bold;}
  .style2 {font-size: 16px}
  .style3 {font-family: Arial, Helvetica, sans-serif;font-size: 11px;font-weight: bold;color:#ffffff;}
-->
</style>
</head>

<body bgcolor="#CCFFFF">
<p align="center" class="style1 style2"><strong>Search Form</strong></p>
<table width="25%" align="center" >
  <tr>
    <td><div align="center" class="style14 style17"><strong><cfoutput>#DateFormat(Now(), "mmmm dd, yyyy")#</cfoutput> </strong></div></td>
    <td><cfoutput><span class="style14 style1 style17"><strong>#TimeFormat(Now(), "hh:mm:sstt")#</strong></span></cfoutput></td>
  </tr>
</table>
<div align="right">
  <input type="submit" name="Submit2" value="Back to Menu Page" style="font:Arial, Helvetica, sans-serif; font-size:12px; font-weight:bolder" onClick="window.open('SearchandReportMenu.cfm');" onMouseOver="this.style.color='red';" onMouseOut="this.style.color='black';" >
</div>
<hr size="5" color="#333333">
<form action="blah.cfm?a=results" method="post" name="SearchForm" id="SearchForm">
  <p align="center" class="style1">Con<br>
    Number:<br>
    <input name="ConNumber" type="text" id="ConNumber" size="20" maxlength="25">    
    <br>
    <br>
    Item Number/<br>
    <input name="ItemNo" type="text" id="ItemNo" size="30" maxlength="35">
  </p>
  <p align="center" class="style1">Name:<br>
    <select name="Name" size="1" id="Name">
      <option value=""></option>
      <cfoutput query="SearchForm">
      <option value="#SearchForm.Name#">#SearchForm.Name#</option>
    </cfoutput>    
    </select>
  </p>
<p align="center" class="style1">Date:<br>
    <select name="Date" size="1" id="Date">
      <option value=""></option>
      <cfoutput query="SearchForm">
      <option value="#SearchForm.Date#">#SearchForm.Date#</option>
    </cfoutput>    
    </select>
  </p>
    <p align="center" class="style1"><span class="style1">
    <input type="submit" name="Submit" value="Search">
  </span> </p>
</form>
<p>&nbsp;</p>

<cfif isdefined("url.a")>
  <cfquery name="SearchForm2" datasource="ChrisForTest">
    SELECT * FROM SpeakerCard WHERE 0=0
      <cfif form.ConNumber NEQ "">AND ConNumber LIKE '%#form.ConNumber#%'</cfif>
      <cfif form.ItemNo NEQ "">AND ItemNo = '#form.ItemNo#'</cfif>
      <cfif form.Name NEQ ""> AND Name= '#form.Name#'</cfif>
      <cfif form.Date NEQ ""> AND Date = '#form.Date#'</cfif>
  </cfquery>

  <table align="center" width="700" cellpadding="3" cellspacing="1" bgcolor="cccccc">
    <tr bgcolor="808080">
        <td class="style3">Con Number:</td>
        <td class="style3">Item Number:</td>
        <td class="style3">Name:</td>
        <td class="style3">Date:</td>
    </tr>
    <cfoutput query="searchform2">
    <tr bgcolor="ffffff">
        <td>#connumber#</td>
        <td>#itemno#</td>
        <td>#name#</td>
        <td>#date#</td>
    </tr>
    </cfoutput>
  </table>
</cfif>

</body>
</html>


0
 
LVL 6

Expert Comment

by:campbelc
ID: 12251757
Sorry, try this:

<cfquery name="SearchFormNames" datasource="ChrisForTest">
  SELECT * FROM SpeakerCard order by name
</cfquery>
<cfquery name="SearchFormDates" datasource="ChrisForTest">
  SELECT * FROM SpeakerCard order by date
</cfquery>

<title>Speaker Card Search Form</title>

<style type="text/css">
<!--
  .style1 {font-family: Arial, Helvetica, sans-serif;font-size: 14px;font-weight: bold;}
  .style2 {font-size: 16px}
  .style3 {font-family: Arial, Helvetica, sans-serif;font-size: 11px;font-weight: bold;color:#ffffff;}
-->
</style>
</head>

<body bgcolor="#CCFFFF">
<p align="center" class="style1 style2"><strong>Search Form</strong></p>
<table width="25%" align="center" >
  <tr>
    <td><div align="center" class="style14 style17"><strong><cfoutput>#DateFormat(Now(), "mmmm dd, yyyy")#</cfoutput> </strong></div></td>
    <td><cfoutput><span class="style14 style1 style17"><strong>#TimeFormat(Now(), "hh:mm:sstt")#</strong></span></cfoutput></td>
  </tr>
</table>
<div align="right">
  <input type="submit" name="Submit2" value="Back to Menu Page" style="font:Arial, Helvetica, sans-serif; font-size:12px; font-weight:bolder" onClick="window.open('SearchandReportMenu.cfm');" onMouseOver="this.style.color='red';" onMouseOut="this.style.color='black';" >
</div>
<hr size="5" color="#333333">
<form action="blah.cfm?a=results" method="post" name="SearchForm" id="SearchForm">
  <p align="center" class="style1">Con<br>
    Number:<br>
    <input name="ConNumber" type="text" id="ConNumber" size="20" maxlength="25">    
    <br>
    <br>
    Item Number/<br>
    <input name="ItemNo" type="text" id="ItemNo" size="30" maxlength="35">
  </p>
  <p align="center" class="style1">Name:<br>
    <select name="Name" size="1" id="Name">
      <option value=""></option>
      <cfoutput query="SearchFormNames">
      <option value="#SearchFormNames.Name#">#SearchFormNames.Name#</option>
    </cfoutput>    
    </select>
  </p>
<p align="center" class="style1">Date:<br>
    <select name="Date" size="1" id="Date">
      <option value=""></option>
      <cfoutput query="SearchFormDates">
      <option value="#SearchFormDates.Date#">#SearchFormDates.Date#</option>
    </cfoutput>    
    </select>
  </p>
    <p align="center" class="style1"><span class="style1">
    <input type="submit" name="Submit" value="Search">
  </span> </p>
</form>
<p>&nbsp;</p>

<cfif isdefined("url.a")>
  <cfquery name="SearchForm2" datasource="ChrisForTest">
    SELECT * FROM SpeakerCard WHERE 0=0
      <cfif form.ConNumber NEQ "">AND ConNumber LIKE '%#form.ConNumber#%'</cfif>
      <cfif form.ItemNo NEQ "">AND ItemNo = '#form.ItemNo#'</cfif>
      <cfif form.Name NEQ ""> AND Name= '#form.Name#'</cfif>
      <cfif form.Date NEQ ""> AND Date = '#form.Date#'</cfif>
  </cfquery>

  <table align="center" width="700" cellpadding="3" cellspacing="1" bgcolor="cccccc">
    <tr bgcolor="808080">
      <td class="style3">Con Number:</td>
      <td class="style3">Item Number:</td>
      <td class="style3">Name:</td>
      <td class="style3">Date:</td>
    </tr>
    <cfoutput query="searchform2">
    <tr bgcolor="ffffff">
      <td>#connumber#</td>
      <td>#itemno#</td>
      <td>#name#</td>
      <td>#date#</td>
    </tr>
    </cfoutput>
  </table>
</cfif>
</body>
</html>
0
 
LVL 6

Expert Comment

by:campbelc
ID: 12252051
Sorry, keep adding more and more features on to this: =)


<cfquery name="SearchFormNames" datasource="ChrisForTest">
  SELECT * FROM SpeakerCard order by name
</cfquery>
<cfquery name="SearchFormDates" datasource="ChrisForTest">
  SELECT * FROM SpeakerCard order by date
</cfquery>

<title>Speaker Card Search Form</title>

<style type="text/css">
<!--
  .style1 {font-family: Arial, Helvetica, sans-serif;font-size: 14px;font-weight: bold;}
  .style2 {font-size: 16px}
  .style3 {font-family: Arial, Helvetica, sans-serif;font-size: 11px;font-weight: bold;color:#ffffff;}
-->
</style>
</head>

<body bgcolor="#CCFFFF">
<p align="center" class="style1 style2"><strong>Search Form</strong></p>
<table width="25%" align="center" >
  <tr>
    <td><div align="center" class="style14 style17"><strong><cfoutput>#DateFormat(Now(), "mmmm dd, yyyy")#</cfoutput> </strong></div></td>
    <td><cfoutput><span class="style14 style1 style17"><strong>#TimeFormat(Now(), "hh:mm:sstt")#</strong></span></cfoutput></td>
  </tr>
</table>
<div align="right">
  <input type="button" name="Submit2" value="Back to Menu Page" style="font:Arial, Helvetica, sans-serif; font-size:12px; font-weight:bolder" onClick="window.open('SearchandReportMenu.cfm');" onMouseOver="this.style.color='red';" onMouseOut="this.style.color='black';" >
</div>
<hr size="5" color="#333333">
<form action="report.cfm?a=results" method="post" name="SearchForm" id="SearchForm">
  <p align="center" class="style1">Con<br>
    Number:<br>
    <input name="ConNumber" type="text" id="ConNumber" size="20" maxlength="25">    
    <br>
    <br>
    Item Number/<br>
    <input name="ItemNo" type="text" id="ItemNo" size="30" maxlength="35">
  </p>
  <p align="center" class="style1">Name:<br>
    <select name="Name" size="1" id="Name">
      <option value=""></option>
      <cfoutput query="SearchFormNames" group="name">
      <option value="#SearchFormNames.Name#">#SearchFormNames.Name#</option>
    </cfoutput>    
    </select>
  </p>
<p align="center" class="style1">Date:<br>
    <select name="Date" size="1" id="Date">
      <option value=""></option>
      <cfoutput query="SearchFormDates" group="date">
      <option value="#SearchFormDates.Date#">#SearchFormDates.Date#</option>
    </cfoutput>    
    </select>
  </p>
    <p align="center" class="style1"><span class="style1">
    <input type="submit" name="Submit" value="Search">
  </span> </p>
</form>
<p>&nbsp;</p>

<cfif isdefined("url.a")>
  <cfquery name="SearchForm2" datasource="ChrisForTest">
    SELECT * FROM SpeakerCard WHERE 0=0
      <cfif form.ConNumber NEQ "">AND ConNumber LIKE '%#form.ConNumber#%'</cfif>
      <cfif form.ItemNo NEQ "">AND ItemNo = '#form.ItemNo#'</cfif>
      <cfif form.Name NEQ ""> AND Name= '#form.Name#'</cfif>
      <cfif form.Date NEQ ""> AND Date = '#form.Date#'</cfif>
  </cfquery>
  <cfif SearchForm2.RecordCount EQ 0>
    <script language="javascript">
        alert('Your search found 0 records');
        self.location='report.cfm';
      </script>  
  </cfif>
  <table align="center" width="700" cellpadding="3" cellspacing="1" bgcolor="cccccc">
    <tr bgcolor="808080">
        <td class="style3">Con Number:</td>
        <td class="style3">Item Number:</td>
        <td class="style3">Name:</td>
        <td class="style3">Date:</td>
    </tr>
    <cfoutput query="searchform2">
    <tr bgcolor="ffffff">
        <td>#connumber#</td>
        <td>#itemno#</td>
        <td>#name#</td>
        <td>#date#</td>
    </tr>
    </cfoutput>
  </table>
</cfif>
<script language="javascript">
  document.SearchForm.ConNumber.focus();
</script>
</body>
</html>
0
 
LVL 6

Expert Comment

by:campbelc
ID: 12252085
Woops, this is using the database fields from your last question. Will modify to work for this question.
0
 
LVL 6

Accepted Solution

by:
campbelc earned 250 total points
ID: 12252321
Need this rewritten or can you figure it based on the last question? Let me know. =)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JKS to store upstart data 2 135
DNS @ Naked Domain Record 5 132
performance tune IIS 10 on win 2016 that only runs one website 4 244
Customising IE behaviour on certain pages 2 101
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question