[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Returning No Records When the form first loads

Hello !, i have a form which searches for firstnames & lastnames and orders by and sorts Ascending/Descending etc.. The issue i am trying to solve is when the form first loads is returns all the records from the contacts table which is a few thousand, I DONT WANT THE QUERY TO RUN WHEN THE PAGE FIRST LOADS ! I am using a SPROC for the search query. Any tips on how to solve this and to improve the below code would be much appreciated !!

dsp_job_add_home.cfm:  -

<cfparam name="URL.clientid" default="">
<cfparam name="URL.clientsiteid" default="">
<cfparam name="FORM.firstname" default="">
<cfparam name="FORM.lastname" default="">
<cfparam name="FORM.SortBy" default="" type="string">
<cfparam name="FORM.AscDesc" default="" type="string">
<cfparam name="FORM.searchcontact" default="" type="string">

<cfinclude template="/Jobs/add/home/qry_Select_Contact_Search.cfm">


<form name="dsp_job_add_home" id="horizontalForm" method="post" action="dsp_job_add_home.cfm">

<!--- Search Bar --->
<div class="divheaderlightblue">
      <span style="font-size:10px; ">Firstname <input type="text" name="firstname" value="<cfoutput>#FORM.firstname#</cfoutput>" /></span>
      <span style="font-size:10px; ">Lastname <input type="text" name="lastname" value="<cfoutput>#FORM.lastname#</cfoutput>" /></span>
      <span style="font-size:10px; ">
            Sort By
            <select name="SortBy">
                  <option value="firstname" <cfif FORM.sortby EQ "firstname">Selected="yes"</cfif>>First Name</option>
                  <option value="lastname" <cfif FORM.sortby EQ "lastname">Selected="yes"</cfif>>Last Name</option>
                </select>
      </span>
      
      <span style="font-size:10px; ">
            Order By
            <select name="AscDesc" style="width:4em; ">
                  <option value="A" <cfif FORM.AscDesc EQ "A">Selected="yes"</cfif>>Asc</option>
                  <option value="D" <cfif FORM.AscDesc EQ "D">Selected="yes"</cfif>>Desc</option>
                </select>
      </span>
      
      <input type="submit" name="searchcontact" value="Search" class="buttonstylesmall"/>
</div>

<table cellspacing="1" summary="Site Contacts" width="100%">
            <thead>
                  <tr>
                        <th width="25%">Name</th>
                        <th width="25%">Company</th>
                        <th width="25%">Position</th>
                        <th width="25%">Work No</th>
                  </tr>
            </thead>
            <tbody>
                  <cfif Get_Contact_Details.recordcount gte 1>
                        <cfoutput query="Get_Contact_Details" startrow="#URL.StartRow#" maxrows="#RowsPerPage#">
                        <!--- Use class "DataA" or "DataB" for alternate rows --->
                      <CFSET Class = IIF(Get_Contact_Details.CurrentRow MOD 2 EQ 0, "'DataA'", "'DataB'")>
                                    <tr class="#Class#">
                                          <td><a href="/jobs/add/dsp_job_add.cfm?clientid=#Get_Contact_Details.clientid#&clientsiteid=#Get_Contact_Details.clientsiteid#&contactid=#Get_Contact_Details.contactid#" target="dsp_contact" onclick="return!openPopup(this.href, this.target,800,600)">#Get_Contact_Details.firstname# #Get_Contact_Details.lastname#</a></td>  
                                          <td>#Get_Contact_Details.companyname#</td>
                                          <td>#Get_Contact_Details.position#</td>
                                          <td>#Get_Contact_Details.workno#</td>
                                    </tr>
                        </cfoutput>
                  <cfelse>
                        <tr>
                              <td colspan="5">No Contacts Found</td>
                        </tr>
                  </cfif>
            </tbody>
</table>

</form>

THE Query SPROC (qry_Select_Contact_Search.cfm) : -

<cfstoredproc procedure="spSelect_Contact_Search" datasource="#request.dsn#">
      <cfprocparam type="In" maxlength="50" cfsqltype="cf_sql_varchar" value="#FORM.firstname#" null="no">
      <cfprocparam type="In" maxlength="50" cfsqltype="cf_sql_varchar" value="#FORM.lastname#" null="no">
      <cfprocparam type="In" maxlength="20" cfsqltype="cf_sql_varchar" value="#FORM.SortBy#" null="no">
      <cfprocparam type="In" maxlength="1" cfsqltype="cf_sql_char" value="#FORM.AscDesc#" null="no">
      <cfprocresult name="Get_Contact_Details" resultset="1" maxrows="500">
</cfstoredproc>


spSelect_Contact_Search: -

USE Intranet

GO

ALTER PROCEDURE spSelect_Contact_Search
     @Firstname     VARCHAR(50),
     @Lastname     VARCHAR(50),
     @SortBy                      VARCHAR(20),
     @SortAscending      CHAR(1)

As

SET NOCOUNT ON

IF @SortAscending = 'A'
     BEGIN
          SELECT    
               ct.contactid,
               ct.firstname,
               ct.lastname,
          FROM Contacts ct
          WHERE     firstname LIKE ('%'+@firstname +'%') AND
               lastname LIKE ('%'+@lastname +'%')
          ORDER BY
               CASE
                    WHEN @SortBy = 'FirstName' Then firstname
                    WHEN @SortBy = 'LastName' Then lastname
                    ELSE mobileno
               END
               ASC
     END
ELSE
     BEGIN
          SELECT    
               ct.contactid,
               ct.firstname,
               ct.lastname,

          FROM Contacts ct

          WHERE     firstname LIKE ('%'+@firstname +'%') AND
               lastname LIKE ('%'+@lastname +'%')
          ORDER BY
               CASE
                    WHEN @SortBy = 'FirstName' Then firstname
                    WHEN @SortBy = 'LastName' Then lastname
                    ELSE mobileno
               END
               DESC
     END
0
jturkington
Asked:
jturkington
  • 2
3 Solutions
 
trailblazzyr55Commented:
try this...

<cfparam name="URL.clientid" default="">
<cfparam name="URL.clientsiteid" default="">
<cfparam name="FORM.firstname" default="">
<cfparam name="FORM.lastname" default="">
<cfparam name="FORM.SortBy" default="" type="string">
<cfparam name="FORM.AscDesc" default="" type="string">
<cfparam name="FORM.searchcontact" default="" type="string">
<cfparam name="FORM.forSubmitted" default="no">

<cfinclude template="/Jobs/add/home/qry_Select_Contact_Search.cfm">


<form name="dsp_job_add_home" id="horizontalForm" method="post" action="dsp_job_add_home.cfm">

<!--- Search Bar --->
<div class="divheaderlightblue">
     <span style="font-size:10px; ">Firstname <input type="text" name="firstname" value="<cfoutput>#FORM.firstname#</cfoutput>" /></span>
     <span style="font-size:10px; ">Lastname <input type="text" name="lastname" value="<cfoutput>#FORM.lastname#</cfoutput>" /></span>
     <span style="font-size:10px; ">
          Sort By
          <select name="SortBy">
               <option value="firstname" <cfif FORM.sortby EQ "firstname">Selected="yes"</cfif>>First Name</option>
               <option value="lastname" <cfif FORM.sortby EQ "lastname">Selected="yes"</cfif>>Last Name</option>
              </select>
     </span>
     
     <span style="font-size:10px; ">
          Order By
          <select name="AscDesc" style="width:4em; ">
               <option value="A" <cfif FORM.AscDesc EQ "A">Selected="yes"</cfif>>Asc</option>
               <option value="D" <cfif FORM.AscDesc EQ "D">Selected="yes"</cfif>>Desc</option>
              </select>
     </span>
     
     <input type="submit" name="searchcontact" value="Search" class="buttonstylesmall"/>
</div>

<table cellspacing="1" summary="Site Contacts" width="100%">
          <thead>
               <tr>
                    <th width="25%">Name</th>
                    <th width="25%">Company</th>
                    <th width="25%">Position</th>
                    <th width="25%">Work No</th>
               </tr>
          </thead>
          <tbody>
               <cfif Get_Contact_Details.recordcount gte 1>
                           <cfif isdefined(forSubmitted) AND forSubmitted IS "yes">
                    <cfoutput query="Get_Contact_Details" startrow="#URL.StartRow#" maxrows="#RowsPerPage#">
                    <!--- Use class "DataA" or "DataB" for alternate rows --->
                   <CFSET Class = IIF(Get_Contact_Details.CurrentRow MOD 2 EQ 0, "'DataA'", "'DataB'")>
                              <tr class="#Class#">
                                   <td><a href="/jobs/add/dsp_job_add.cfm?clientid=#Get_Contact_Details.clientid#&clientsiteid=#Get_Contact_Details.clientsiteid#&contactid=#Get_Contact_Details.contactid#" target="dsp_contact" onclick="return!openPopup(this.href, this.target,800,600)">#Get_Contact_Details.firstname# #Get_Contact_Details.lastname#</a></td>  
                                   <td>#Get_Contact_Details.companyname#</td>
                                   <td>#Get_Contact_Details.position#</td>
                                   <td>#Get_Contact_Details.workno#</td>
                              </tr>
                    </cfoutput>
                          </cfif>
               <cfelse>
                    <tr>
                         <td colspan="5">No Contacts Found</td>
                    </tr>
               </cfif>
          </tbody>
</table>
<input type="hidden" name="forSubmitted" value="yes">
</form>
0
 
INSDivision6Commented:
Just check  IsDefined("searchcontact") - your submit button.  If it was not pushed - means your form just loaded - skip your record selection precedure.
0
 
Renante EnteraCommented:
Hi jturkington!

Do you mean that once your form loaded, no single record will be displayed ???  If yes, then INSDivision6 answered your question.  

No need to initialize "form.searchcontact" and your condition will be like this : <cfif IsDefined('form.searchcontact')>...</cfif>

So, there are some codes which will be affected.

Here's a revised code :

<!---------------------------------------------------------------------------------------------------------------------------
* Revisions : Just look for the combination of "<!--- Begin Revision x ---> and <!--- End Revision x --->"
*                 1.  Remove initialization of "FORM.searchcontact".
*                 2.  Monitor submit button when including the query/procedure.
*                 3.  Instead of [Selected = "yes"], use only [Selected].
*                 4.  Check if 'Get_Contact_Details.recordcount' is defined and not empty before displaying it.
*                 5.  To avoid redundancy, you may specify "#fieldname#" instead of "#Queryname.fieldname#"
*                      when you are within the tag "<cfoutput query="Queryname" ...> ... </cfoutput>
*                      since you are only referring to a single query resultset.
---------------------------------------------------------------------------------------------------------------------------->

<cfparam name="URL.clientid" default="">
<cfparam name="URL.clientsiteid" default="">
<cfparam name="FORM.firstname" default="">
<cfparam name="FORM.lastname" default="">
<cfparam name="FORM.SortBy" default="" type="string">
<cfparam name="FORM.AscDesc" default="" type="string">
<!--- Begin Revision 1
<cfparam name="FORM.searchcontact" default="" type="string">
End Revision 1 --->

<!--- Begin Revision 2 Monitor submit button, once defined procedure will be called --->
<cfif IsDefined('form.searchcontact')>
  <cfinclude template="/Jobs/add/home/qry_Select_Contact_Search.cfm">
</cfif>
<!--- End Revision 2 --->

<form name="dsp_job_add_home" id="horizontalForm" method="post" action="dsp_job_add_home.cfm">

<!--- Search Bar --->
<div class="divheaderlightblue">
     <span style="font-size:10px; ">Firstname <input type="text" name="firstname" value="<cfoutput>#FORM.firstname#</cfoutput>" /></span>
     <span style="font-size:10px; ">Lastname <input type="text" name="lastname" value="<cfoutput>#FORM.lastname#</cfoutput>" /></span>
     <!--- Begin Revision 3 --->
     <span style="font-size:10px; ">
          Sort By
          <select name="SortBy">
               <option value="firstname" <cfif FORM.sortby EQ "firstname">Selected</cfif>>First Name</option>
               <option value="lastname" <cfif FORM.sortby EQ "lastname">Selected</cfif>>Last Name</option>
              </select>
     </span>
     
     <span style="font-size:10px; ">
          Order By
          <select name="AscDesc" style="width:4em; ">
               <option value="A" <cfif FORM.AscDesc EQ "A">Selected</cfif>>Asc</option>
               <option value="D" <cfif FORM.AscDesc EQ "D">Selected</cfif>>Desc</option>
              </select>
     </span>
     <!--- End Revision 3 --->
     
     <input type="submit" name="searchcontact" value="Search" class="buttonstylesmall"/>
</div>

<table cellspacing="1" summary="Site Contacts" width="100%">
          <thead>
               <tr>
                    <th width="25%">Name</th>
                    <th width="25%">Company</th>
                    <th width="25%">Position</th>
                    <th width="25%">Work No</th>
               </tr>
          </thead>
          <tbody>
               <!--- Begin Revision 4 --->
               <cfif IsDefined('Get_Contact_Details.recordcount') And Get_Contact_Details.recordcount>
               <!--- End Revision 4 --->
                    <!--- Begin Revision 5 --->
                    <cfoutput query="Get_Contact_Details" startrow="#URL.StartRow#" maxrows="#RowsPerPage#">
                    <!--- Use class "DataA" or "DataB" for alternate rows --->
                   <CFSET Class = IIF(CurrentRow MOD 2 EQ 0, "'DataA'", "'DataB'")>
                              <tr class="#Class#">
                                   <td><a href="/jobs/add/dsp_job_add.cfm?clientid=#clientid#&clientsiteid=#clientsiteid#&contactid=#contactid#" target="dsp_contact" onclick="return!openPopup(this.href, this.target,800,600)">#firstname# #lastname#</a></td>  
                                   <td>#companyname#</td>
                                   <td>#position#</td>
                                   <td>#workno#</td>
                              </tr>
                    </cfoutput>
                    <!--- End Revision 5 --->
               <cfelse>
                    <tr>
                         <td colspan="5">No Contacts Found</td>
                    </tr>
               </cfif>
          </tbody>
</table>

</form>

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
Renante EnteraCommented:
Hi trailblazzyr55!

Good to see you here posting some comments.  I just wanna remind you about your question : http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21400403.html.

We need some feedback from you.  Thanks ...


Regards!
eNTRANCE2002 :-)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now