• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Search on select field

I have the following query which is working great, I added a select menu (cats_Info) to the previous page where the list="#editfields#" are selected. How can I add to my query below to show only the records that are associated with the select menu value I selected? The select menu is not required so if no slection is made I just run the query below as it is currently.

<cfquery name="GetProds" datasource="GH234" cachedwithin=#CreateTimeSpan(0,0,30,0)#>
SELECT ProductMatrix.ProductID,Cats.Caption,Cats.CategoryID,
      <CFLOOP index="field" list="#editfields#">
            Products.#Field#,
      </CFLOOP>ProductOrd.Level
FROM Products, ProductMatrix, Cats
WHERE Products.ProductID = ProductMatrix.ProductID
</cfquery>
0
usky1
Asked:
usky1
  • 14
  • 12
1 Solution
 
_agx_Commented:
What table and column does "cats_Info" relate to, and what is the default value if no selection is made? An empty string ""  or something else?

Also, you're missing a join between "Cats" and the other two tables

FROM Products, ProductMatrix, Cats
WHERE Products.ProductID = ProductMatrix.ProductID
AND     Cats.SomeField = OneOfTheOtherTables.SomeField


0
 
usky1Author Commented:
What table  - (cats)
and column (cats_information)
does "cats_Info" relate to, and what is the default value if no selection is made? An empty string "" (yes)  or something else?

Also, you're missing a join between "Cats" and the other two tables (if I use the AND in the sql statement what happens if there is not a selection made with the cats_info?)

FROM Products, ProductMatrix, Cats
WHERE Products.ProductID = ProductMatrix.ProductID
AND     Cats.SomeField = OneOfTheOtherTables.SomeField
0
 
_agx_Commented:
There's a difference between joining on related columns and filtering on a specific value.  If you join on the related columns, you should ideally get the same results as before.  

FROM Products, ProductMatrix, Cats
WHERE Products.ProductID = ProductMatrix.ProductID
AND     Cats.SomeField = OneOfTheOtherTables.SomeField

Then if a selection is made for "cats_info", add another statement to filter the results.  In other words, out of those results .. return only the records where Cats.Cats_information = '#form.cats_info#'

FROM Products, ProductMatrix, Cats
WHERE Products.ProductID = ProductMatrix.ProductID
AND     Cats.SomeField = OneOfTheOtherTables.SomeField
<cfif len(trim(form.cats_info)) gt 0>
      AND   Cats.Cats_information = '#form.cats_info#'
</cfif>

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
usky1Author Commented:
I had to add a IsDefined to the query because I received the following error if I did not do a selection,
Element CATS_INFO is undefined in FORM
Was: <cfif len(trim(form.cats_info)) gt 0>
Changed to: <cfif IsDefined("form.cats_info")AND form.cats_info GT 0>
I am also doing an output of form.cats_info to see if it is being working and it is.
The output from the query is still the same as the original and does not output only the selected value.
0
 
_agx_Commented:
A "single" select list should always exist on the action page, unless it has no options at all or its using "multiple" and no selections where made.

Can you post the code for generating the form.cats_info select list and your new query code?
0
 
usky1Author Commented:
I have an emergency item that I need to take care of. It might be the morning before I can post.
Thanks,
0
 
_agx_Commented:
Okay
0
 
usky1Author Commented:
The query is partially working. I get the results on the first page but it does not show any additional pages. I have it set for 15 records per page and I know with the selection I did that there are multiple pages. Also it will not allow any updates now. I do not get any errors back but I select update it comes back with out any updated information. Is there a way for me to email you the two pages. There is sensitive information in the code that I will need to modify if I post it here.  Thanks,
0
 
_agx_Commented:
> There is sensitive information in the code that I will need to modify if I post it here

Yes, I know its a pain, but its probably best to xxx out confidential info and post the code here. That way everyone can benefit from the final answer.
0
 
usky1Author Commented:
This is the select list:
---------------------------------------------------------------------------------------------------------
<CFIF Getcats.recordcount is not 0>
        <p><SELECT NAME="Category_ID" SIZE="6" class="textblacknormal">
                <CFOUTPUT QUERY="getcats">
                  <CFIF Len("#Caption#") GT 20>
                    <CFSET cTitle = "#Left("#Caption#", 20)#...">
                        <CFELSE>
                    <CFSET cTitle = #Caption#>
                    </CFIF>
                      <OPTION VALUE="#CategoryID#">#cTitle#</CFOUTPUT>
                </SELECT>
                  <CFELSE>
              <P>
              <FONT FACE=ARIAL color="#000080">No Categories Available</FONT></p>
            </CFIF>
---------------------------------------------------------------------------------------------------
This is the code from the results page: (it is a work in progress)
-----------------------------------------------------------------------------------------------------
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfparam name="PageNum_GetProds" default="1">

<cfset MaxRows_GetProds=15>

<cfparam name="RetailPrice" default="">
<cfparam name="ENumber" default="">
<cfparam name="OBS" default="">
<cfparam name="editfields" default="">
<cfparam name="relevel" default="">
<cfparam name="warehousecode" default="">
<cfparam name="Information" default="">
<cfparam name="cats.Information" default="">
<cfparam name="Category_ID" default="">

<!--- Query change on 10/03/07 per EE recoomendation. New query below --->
<!--- <cfquery name="GetProds" datasource="GH234b" cachedwithin=#CreateTimeSpan(0,0,30,0)#>
SELECT ProMatx.ProductID,cats.Information,cats.CategoryID,
      <CFLOOP index="field" list="#editfields#">
            Products.#Field#,
      </CFLOOP>ProMatx.InStock, ProMatx.relevel
FROM Products, ProMatx, Warehouses, cats
WHERE Products.ProductID = ProMatx.ProductID
      <cfif IsDefined("Category_ID">
      AND cats.CategoryID = Products.CategoryID
      </cfif>
</cfquery> --->
<cfquery name="GetProds" datasource="GH234b" cachedwithin=#CreateTimeSpan(0,0,30,0)#>
SELECT ProMatx.ProductID,cats.Information,cats.CategoryID,
      <CFLOOP index="field" list="#editfields#">
            Products.#Field#,
      </CFLOOP>ProMatx.InStock, ProMatx.relevel
FROM Products, ProMatx, Warehouses, cats
WHERE Products.ProductID = ProMatx.ProductID
      AND      cats.CategoryID = Products.CategoryID
      <cfif IsDefined("form.Category_ID")AND form.Category_ID GT 0>
            AND cats.CategoryID = '#form.Category_ID#'
      </cfif>
</cfquery>
<cfif IsDefined("form.Category_ID")AND form.Category_ID GT 0>
rc====<cfoutput>#form.Category_ID#</cfoutput>
<cfelse>
rc====00000000000
</cfif>


<cfquery name="GetCatID" datasource="GH234b">
SELECT <CFLOOP index="field" list="#editfields#">
            Products.#Field#,
      </CFLOOP>cats.CategoryID, cats.Information
FROM cats, Products
WHERE Products.CategoryID = cats.CategoryID
ORDER BY Products.ProductID
</cfquery>

      <cfif NOT IsDefined("session.editfields")>
            <cfset session.editfields = "#editfields#">
            <cfelse>
      </cfif>
      <!--- <cfoutput>#Session.EditFields#</cfoutput> --->

<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">
<cfloop index="idx" from="#form.startCount#" to="#form.endCount#">
<cfquery datasource="GH234b">
Update Products
Set Custom6 =
            <cfif IsDefined("form.Custom6" & idx) AND form["Custom6" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.Custom6" & idx))#" cfsqltype="cf_clob">
            <cfelse>
            ''
            </cfif>
      <CFIF IsDefined("GetProds.SKU")>
            , SKU =
            <cfif IsDefined("form.SKU" & idx) AND form["SKU" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.SKU" & idx))#" cfsqltype="cf_clob">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.OBS")>
            , OBS =
            <cfif IsDefined("form.OBS" & idx) AND form["OBS"& idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.OBS" & idx))#" cfsqltype="cf_sql_decimal">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.RetailPrice")>
            , RetailPrice =
            <cfif IsDefined("form.RetailPrice" & idx) AND form["RetailPrice" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.RetailPrice" & idx))#" cfsqltype="cf_sql_money">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.ENumber")>
            , ENumber =
            <cfif IsDefined("form.ENumber" & idx) AND form["ENumber" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.ENumber" & idx))#" cfsqltype="cf_sql_clob">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.ProductSName")>
            , ProductSName =
            <cfif IsDefined("form.ProductSName" & idx) AND form["ProductSName" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.ProductSName" & idx))#" cfsqltype="cf_sql_clob">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.ProductName")>
            , ProductName =
            <cfif IsDefined("form.ProductName" & idx) AND form["ProductName" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.ProductName" & idx))#" cfsqltype="cf_sql_clob">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.Profit_pct")>
            , Profit_pct =
            <cfif IsDefined("form.Profit_pct" & idx) AND form["Profit_pct" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.Profit_pct" & idx))#" cfsqltype="cf_sql_clob">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.Description")>
            , Description =
            <cfif IsDefined("form.Description" & idx) AND form["Description" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.Description" & idx))#" cfsqltype="cf_sql_varchar">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.Weight")>
            , Weight =
            <cfif IsDefined("form.Weight" & idx) AND form["Weight" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.Weight" & idx))#" cfsqltype="cf_sql_varchar">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsBook")>
            , IsBook =
            <cfif IsDefined("form.IsBook" & idx) AND form["IsBook" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsBook" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsCatalog")>
            , IsCatalog =
            <cfif IsDefined("form.IsCatalog" & idx) AND form["IsCatalog" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsCatalog" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsChocolate")>
            , IsChocolate =
            <cfif IsDefined("form.IsChocolate" & idx) AND form["IsChocolate" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsChocolate" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsFood")>
            , IsFood =
            <cfif IsDefined("form.IsFood" & idx) AND form["IsFood" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsFood" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsMedia")>
            , IsMedia =
            <cfif IsDefined("form.IsMedia" & idx) AND form["IsMedia" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsMedia" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsPerm")>
            , IsPerm =
            <cfif IsDefined("form.IsPerm" & idx) AND form["IsPerm" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsPerm" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsPrint")>
            , IsPrint =
            <cfif IsDefined("form.IsPrint" & idx) AND form["IsPrint" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsPrint" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsSchoolsupp")>
            , IsSchoolsupp =
            <cfif IsDefined("form.IsSchoolsupp" & idx) AND form["IsSchoolsupp" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsSchoolsupp" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.IsWrapsCatalog")>
            , IsWrapsCatalog =
            <cfif IsDefined("form.IsWrapsCatalog" & idx) AND form["IsWrapsCatalog" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.IsWrapsCatalog" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.Active")>
            , Active =
            <cfif IsDefined("form.Active" & idx) AND form["Active" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.Active" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
      <CFIF IsDefined("GetProds.Taxable")>
            , Taxable =
            <cfif IsDefined("form.Taxable" & idx) AND form["Taxable" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.Taxable" & idx))#" cfsqltype="cf_sql_bit">
            <cfelse>
            ''
            </cfif>
      </CFIF>
<CFIF IsDefined("GetProds.WarehouseID")>
            , WarehouseID =
            <cfif IsDefined("form.WarehouseID" & idx) AND form["WarehouseID" & idx] NEQ "">
            <cfqueryparam value="#Trim(Evaluate("form.WarehouseID" & idx))#" cfsqltype="cf_sql_integer">
            <cfelse>
            ''
            </cfif>
      </CFIF>
Where Products.ProductID = <cfqueryparam value="#Trim(Evaluate("form.ProductID" & idx))#" cfsqltype="cf_sql_numeric">

<!--- Update ProMatx
Set InStock  =
       <cfif  IsDefined("form.InStock" & idx) AND form["InStock"&  idx] NEQ "">
      <cfqueryparam value="#Trim(Evaluate("form.InStock" & idx))#" cfsqltype="cf_sql_smallint">
      <cfelse>
    ''
        </cfif>
    , relevel =
      <cfif IsDefined("form.relevel" & idx) AND form["relevel" & idx] NEQ "">
      <cfqueryparam value="#Trim(Evaluate("form.relevel" & idx))#" cfsqltype="cf_sql_smallint">
      <cfelse>
    ''
        </cfif>
Where ProMatx.ProductID = <cfqueryparam value="#Trim(Evaluate("form.ProductID" & idx))#" cfsqltype="cf_sql_numeric"> --->
</cfquery>
</cfloop>
</cfif>
<!--- Query change on 10/03/07 per EE recoomendation. New query below --->
<!--- <cfquery name="GetProds" datasource="GH234b" cachedwithin=#CreateTimeSpan(0,0,30,0)#>
SELECT ProMatx.ProductID,cats.Information,cats.CategoryID,
      <CFLOOP index="field" list="#editfields#">
            Products.#Field#,
      </CFLOOP>ProMatx.InStock, ProMatx.relevel
FROM Products, ProMatx, Warehouses, cats
WHERE Products.ProductID = ProMatx.ProductID
      <cfif IsDefined("Category_ID">
      AND cats.CategoryID = Products.CategoryID
      </cfif>
</cfquery> --->
<cfquery name="GetProds" datasource="GH234b" cachedwithin=#CreateTimeSpan(0,0,30,0)#>
SELECT ProMatx.ProductID,cats.Information,cats.CategoryID,
      <CFLOOP index="field" list="#editfields#">
            Products.#Field#,
      </CFLOOP>ProMatx.InStock, ProMatx.relevel
FROM Products, ProMatx, Warehouses, cats
WHERE Products.ProductID = ProMatx.ProductID
      AND      cats.CategoryID = Products.CategoryID
      <cfif IsDefined("form.Category_ID")AND form.Category_ID GT 0>
            AND cats.CategoryID = '#form.Category_ID#'
      </cfif>
</cfquery>
<cfif IsDefined("form.Category_ID")AND form.Category_ID GT 0>
rc====<cfoutput>#form.Category_ID#</cfoutput>
<cfelse>
rc====00000000000
</cfif>

<cfif IsDefined("Category_ID") AND IsDefined("Information")>
<cfoutput> Selected Catid = #Category_ID# - #Information#</cfoutput><br />
<cfelse>
No cat selected<br />
</cfif>
<cfif IsDefined("Category_ID")>
      <cfoutput><cfloop query="getprods" startrow="1" endrow="10">cat ID is #getprods.ProductID#--#getprods.CategoryID# - #getprods.Information#<br /></cfloop></cfoutput>
<cfelse>
      cat ID is ??????
</cfif>

<cfset MaxRows_GetProds=15>
<cfset StartRow_GetProds=Min((PageNum_GetProds-1)*MaxRows_GetProds+1,Max(GetProds.RecordCount,1))>
<cfset EndRow_GetProds=Min(StartRow_GetProds+MaxRows_GetProds-1,GetProds.RecordCount)>
<cfset TotalPages_GetProds=Ceiling(GetProds.RecordCount/MaxRows_GetProds)>
<cfset QueryString_GetProds=Iif(CGI.QUERY_STRING NEQ "",DE("&"&XMLFormat(CGI.QUERY_STRING)),DE(""))>
<cfset tempPos=ListContainsNoCase(QueryString_GetProds,"PageNum_GetProds=","&")>
<cfif tempPos NEQ 0>
  <cfset QueryString_GetProds=ListDeleteAt(QueryString_GetProds,tempPos,"&")>
</cfif>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Edit Products Update</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="/store/includes/css/css.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
a:link {
      color: #0000FF;
}
a:visited {
      color: #0000FF;
}
a:hover {
      color: #FF0000;
}
-->
</style></head>
<body>
<form name="form1" id="form1" method="post" action="#CGI.Script_Name#">
<p align="center" class="Header">Product Quick Edit
<cfoutput>
<div align="center" class="small_blue">Records #StartRow_GetProds# to #EndRow_GetProds# of #GetProds.RecordCount# </div>
</cfoutput>
</p>
<div align="center">
<table width="57%" border="1" bordercolor="#999999">
  <cfoutput>
    <tr>
      <td width="20%" align="center" class="subselectio"><cfif PageNum_GetProds GT 1>
        <a href="#CurrentPage#?PageNum_GetProds=1#QueryString_GetProds#">| First |</a>
      </cfif></td>
      <td width="20%" align="center" class="subselectio"><cfif PageNum_GetProds GT 1>
        <a href="#CurrentPage#?PageNum_GetProds=#Max(DecrementValue(PageNum_GetProds),1)##QueryString_GetProds#" class="productinfo">| Previous |</a>
      </cfif>
      </td>
      <td width="20%" align="center" class="subselectio"><cfif PageNum_GetProds LT TotalPages_GetProds>
        <a href="#CurrentPage#?PageNum_GetProds=#Min(IncrementValue(PageNum_GetProds),TotalPages_GetProds)#&editfields=#editfields#">| Next |</a>
      </cfif></td>
      <td width="20%" align="center" class="subselectio"><cfif PageNum_GetProds LT TotalPages_GetProds><a href="#CurrentPage#?PageNum_GetProds=#TotalPages_GetProds#&editfields=#editfields#">| Last |</a>
      </cfif></td>
        <td width="20%" align="center" class="subselectio"><a href="Products_qeList.cfm">| List |</strong></a></td>
    </tr>
  </cfoutput>
</table>
</div>

<table border="1" align="left" cellpadding="1" cellspacing="2">
<tr>
<td width="25" class="Header"><div align="center"></div></td>
<td width="70" class="Header"><div align="center"><strong>ProductID</strong></div></td>
      <CFIF IsDefined("GetProds.SKU")>      
            <td width="70" class="Header"><div align="center"><strong>SKU</strong></div></td>
      </CFIF>
      <!--- <CFIF IsDefined("GetProds.InStock")>
            <td width="70" class="Header"><div align="center"><strong>In Stock</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.relevel")>
            <td width="70" class="Header"><div align="center"><strong>Reorder Level</strong></div></td>
      </CFIF> --->
      <CFIF IsDefined("GetProds.Active")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsActive</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.Taxable")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsTaxable</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsBook")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsBook</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsCatalog")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsCatalog</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsChocolate")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsChocolate</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsFood")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsFood</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsMedia")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsMedia</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsPerm")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsPerm</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsPrint")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsPrint</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsSchoolsupp")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsSchoolSupp</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsWrapsCatalog")>
            <td width="80" align="center" class="Header"><div align="center"><strong>IsWrapsCatalog</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.ProductName")>
            <td width="80" align="center" class="Header"><div align="center"><strong>Title</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.ENumber")>
            <td width="70" align="center" class="Header"><div align="center"><strong>IB Part No </strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.OBS")>
            <td width="70" align="center" class="Header"><div align="center"><strong>OBS</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.ProductSName")>
            <td width="80" align="center" class="Header"><div align="center"><strong>Thumbnail</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.Profit_pct")>
            <td width="70" align="center" class="Header"><div align="center"><strong>Profit %</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.Description")>
            <td width="70" align="center" class="Header"><div align="center"><strong>Description</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.CategoryID")>      
            <td width="70" class="Header"><div align="center"><strong>Category</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.RetailPrice")>
            <td width="70" align="center" class="Header"><div align="center"><strong>RPrice</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.WarehouseID")>
            <td width="70" align="center" class="Header"><div align="center"><strong>Whse</strong></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.Weight")>
            <td width="70" align="center" class="Header"><div align="center"><strong>Weight</strong></div></td>
      </CFIF>
</tr>
<!--- Set onblur color to white --->
<cfset obbgc = "this.style.backgroundColor='##ffffff'">
<!--- Set onfocus color to yellow --->
<cfset ofbgc = "this.style.backgroundColor='##fcff77'">
<!--- dumb3------------<cfdump var="#GetCats#" top="10"> --->

<cfoutput query="GetProds" startRow="#StartRow_GetProds#" maxRows="#MaxRows_GetProds#">
<tr valign="top" bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('cccccc'))#" onMouseOver="this.style.backgroundColor='##ffcc99'" onMouseOut="this.style.backgroundColor='###iif(currentrow MOD 2,DE('ffffff'),DE('cccccc'))#'">
<td class="textblackbold"><div align="center">#GetProds.currentRow#</div></td>
<td class="textblacknormal"><div align="center">#GetProds.ProductID#
  <input name="ProductID#CurrentRow#" type="hidden" id="ProductID#CurrentRow#" value="#GetProds.ProductID#" onFocus="this.style.backgroundColor='##fcff77'" onBlur="this.style.backgroundColor='##ffffff'" />
</div></td>
      <CFIF IsDefined("GetProds.SKU")>      
            <td align="center" class="textblacknormal"><div align="center">
            <input name="SKU#CurrentRow#" type="text" id="SKU#CurrentRow#" value="#GetProds.SKU#" size="8" maxlength="8" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <!--- <CFIF IsDefined("GetProds.InStock")>
            <td align="center" class="textblacknormal"><div align="center">#GetProds.InStock#
            <input name="InStock#CurrentRow#" type="hidden" id="InStock#CurrentRow#" value="#GetProds.InStock#" size="8" maxlength="8" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.relevel")>
            <td align="center" class="textblacknormal"><div align="center">#GetProds.relevel#
            <input name="relevel#CurrentRow#" type="hidden" id="relevel#CurrentRow#" value="#GetProds.relevel#" size="8" maxlength="8" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF> --->
      <CFIF IsDefined("GetProds.Active")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="Active#CurrentRow#" type="checkbox" id="Active#CurrentRow#" value="1" <cfif #GetProds.Active# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.Taxable")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="Taxable#CurrentRow#" type="checkbox" id="Taxable#CurrentRow#" value="1" <cfif #GetProds.Taxable# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsBook")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsBook#CurrentRow#" type="checkbox" id="IsBook#CurrentRow#" value="1" <cfif #GetProds.IsBook# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsCatalog")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsCatalog#CurrentRow#" type="checkbox" id="IsCatalog#CurrentRow#" value="1" <cfif #GetProds.IsCatalog# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsChocolate")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsChocolate#CurrentRow#" type="checkbox" id="IsChocolate#CurrentRow#" value="1" <cfif #GetProds.IsChocolate# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsFood")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsFood#CurrentRow#" type="checkbox" id="IsFood#CurrentRow#" value="1" <cfif #GetProds.IsFood# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsMedia")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsMedia#CurrentRow#" type="checkbox" id="IsMedia#CurrentRow#" value="1" <cfif #GetProds.IsMedia# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsPerm")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsPerm#CurrentRow#" type="checkbox" id="IsPerm#CurrentRow#" value="1" <cfif #GetProds.IsPerm# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsPrint")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsPrint#CurrentRow#" type="checkbox" id="IsPrint#CurrentRow#" value="1" <cfif #GetProds.IsPrint# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsSchoolsupp")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsSchoolsupp#CurrentRow#" type="checkbox" id="IsSchoolsupp#CurrentRow#" value="1" <cfif #GetProds.IsSchoolsupp# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.IsWrapsCatalog")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="IsWrapsCatalog#CurrentRow#" type="checkbox" id="IsWrapsCatalog#CurrentRow#" value="1" <cfif #GetProds.IsWrapsCatalog# is 1> checked</cfif> onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.ProductName")>
            <td align="center" class="textblacknormal"><div align="center">
            <textarea name="ProductName#CurrentRow#" cols="30" rows="2" onFocus="#ofbgc#" onBlur="#obbgc#">#HtmlEditFormat(Trim(GetProds.ProductName))#</textarea></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.ENumber")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="ENumber#CurrentRow#" type="text" id="ENumber#CurrentRow#" value="#GetProds.ENumber#" size="8" maxlength="8" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.OBS")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="OBS#CurrentRow#" type="text" id="OBS#CurrentRow#" value="#GetProds.OBS#" size="8" maxlength="8" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.ProductSName")>
            <td align="center" class="textblacknormal"><div align="center">
            <textarea name="ProductSName#CurrentRow#" cols="30" rows="1" onFocus="#ofbgc#" onBlur="#obbgc#">#HtmlEditFormat(Trim(GetProds.ProductSName))#</textarea></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.Profit_pct")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="Profit_pct#CurrentRow#" type="text" id="Profit_pct#CurrentRow#" value="#GetProds.Profit_pct#" size="6" maxlength="6" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.Description")>
            <td align="center" class="textblacknormal"><div align="center">
            <textarea name="Description#CurrentRow#" cols="40" rows="4" onFocus="#ofbgc#" onBlur="#obbgc#">#HtmlEditFormat(Trim(GetProds.Description))#</textarea></div></td>
      </CFIF>
      <!--- <CFIF IsDefined("GetCatID.CategoryID") AND IsDefined("GetProds.CategoryID")>
            <td align="center" class="textblacknormal"><div align="center">
              <input name="CategoryID#CurrentRow#"id="CategoryID#CurrentRow#" value="#GetCatID.Information#" size="20" maxlength="50" onfocus="#ofbgc#" onblur="#obbgc#"></textarea></div></td>
      </CFIF> --->
      <CFIF IsDefined("GetProds.CategoryID")>
            <td align="center" class="textblacknormal"><div align="center">
              <input name="CategoryID#CurrentRow#" id="CategoryID#CurrentRow#" value="#GetProds.CategoryID#" size="20" maxlength="50" onFocus="#ofbgc#" onBlur="#obbgc#"></textarea></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.RetailPrice")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="RetailPrice#CurrentRow#" type="text" id="RetailPrice#CurrentRow#" value="#GetProds.RetailPrice#" size="8" maxlength="8" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <!--- <CFIF IsDefined("GetWID.WarehouseID") AND IsDefined("GetProds.WarehouseID")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="WarehouseID#CurrentRow#" type="text" id="WarehouseID#CurrentRow#" value="#GetWID.WarehouseCode#" size="6" maxlength="6" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF> --->
      <CFIF IsDefined("GetProds.WarehouseID")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="WarehouseID#CurrentRow#" type="text" id="WarehouseID#CurrentRow#" value="#GetProds.WarehouseID#" size="6" maxlength="6" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
      <CFIF IsDefined("GetProds.Weight")>
            <td align="center" class="textblacknormal"><div align="center">
            <input name="Weight#CurrentRow#" type="text" id="Weight#CurrentRow#" value="#GetProds.Weight#" size="8" maxlength="8" onFocus="#ofbgc#" onBlur="#obbgc#" /></div></td>
      </CFIF>
</tr>
</cfoutput>
            <CFLOOP QUERY="Getprods">
<!--- loop the query to put all the product id's into a list for later use --->
                        <cfset lstProductIDs = ListAppend(editfields, "")>
            </cfloop>      
<tr align="center">
<td colspan="12">
<input name="CategoryID" type="hidden" value="form.Custom6" />
<input name="CategoryID" type="hidden" value="form.CategoryID" />
<input name="relevel" type="hidden" value="form.relevel" />
<input name="InStock" type="hidden" value="form.InStock" />
<input name="Weight" type="hidden" value="form.Weight" />
<input name="WarehouseID" type="hidden" value="form.WarehouseID" />
<input name="WarehouseCode" type="hidden" value="form.WarehouseID" />
<input name="Description" type="hidden" value="form.Description" />
<input name="OBS" type="hidden" value="form.OBS" />
<input name="SKU" type="hidden" value="form.SKU" />
<input name="RetailPrice" type="hidden" value="form.RetailPrice" />
<input name="ENumber" type="hidden" value="form.ENumber" />
<input name="ProductSName" type="hidden" value="form.ProductSName" />
<input name="ProductName" type="hidden" value="form.ProductName" />
<input name="Profit_pct" type="hidden" value="form.Profit_pct" />
<input name="Reset" type="reset" class="small_blue" value="Reset" />
<input name="Submit" type="submit" class="small_red" value="Update" />
<input name="Submit2" type="hidden" value="Update" />
<input type="hidden" name="MM_UpdateRecord" value="form1">
<cfoutput>
<input name="startCount" type="hidden" value="#StartRow_GetProds#" />
<input name="endCount" type="hidden" value="#EndRow_GetProds#" />
<input name="editfields" type="hidden" value="#editfields#" />
</cfoutput>
</td>
</tr>
</table>
</form>
</body>
</html>

0
 
_agx_Commented:
> The query is partially working. I get the results on the first page but it does not show any
> additional pages. I have it set for 15 records per page and I know with the selection I did
> that there are multiple pages.

First let's figure out why that's happening.  Is the "GetProds" query actually returning more than 15 records?  If its not, that explains why you're not seeing additional pages.  Dump the query and see how many records it contains

<cfdump var="#GetProds#">

0
 
usky1Author Commented:
Your are correct. Stupid oversight on my part. the correct counts are coming back.
The updates still are not functioning. they were working before.The page shows correct when first selecting. Then when I change any of the fields and submit it comes back with all the records not the selected "Category_ID" records. And the updates are not present. No errors come back.
0
 
_agx_Commented:
> Then when I change any of the fields and submit it comes back with all the records not
> the selected "Category_ID" records.

Sounds like the "Category_ID" value isn't submitted with the update form or in the navigation links.  So  this code never runs because "Category_ID" doesn't exist.

<cfif IsDefined("form.Category_ID")AND form.Category_ID GT 0>
            AND cats.CategoryID = '#form.Category_ID#'
</cfif>

But if "Category_ID" can be either a form or url parameter, you may need to remove the scope

<cfif IsDefined("Category_ID")AND Category_ID GT 0>
            AND cats.CategoryID = '#Category_ID#'
</cfif>
0
 
_agx_Commented:
As far as the updates go,  I would fix the other issues first.  Then if the updates still aren't working,  place some debugging code before the update section to determine if that section is executing at all.

<cfoutput>
IsDefined("FORM.MM_UpdateRecord") = #IsDefined("FORM.MM_UpdateRecord")#<br>
FORM.MM_UpdateRecord EQ "form1" = #(FORM.MM_UpdateRecord EQ "form1")#<br>
from="#form.startCount#"<br>
to="#form.endCount#"<br>
</cfoutput>
0
 
usky1Author Commented:
I changed the scope and got back the same results.
0
 
_agx_Commented:
Yes, but did you add the "Category_ID" value to the form and/or navigation links?  If you're not passing the selected category to subsequent pages, it will always revert to "all categories".
0
 
usky1Author Commented:
but did you add the "Category_ID" value to the form and/or navigation links? I tried to add them with the results the same. I added,
<cfif cgi.request_method is "post">
  <cfdump var="#form#">
</cfif>
To see if the values passed. The category_ID was correct on the first page but when submitting for an update it did not hold the value. Even if I added it to links and hidden fields. Please let me know what you suggestions you have. Thanks,,,
0
 
_agx_Commented:
> <cfif cgi.request_method is "post">
>  <cfdump var="#form#">
> </cfif>

Btw, you can dump the FORM or ULR scope without the CFIF.  If its not a form post/get the results will just be empty.

> Even if I added it to links and hidden fields.

What do you mean did not hold the value?  The value was blank "" or the variable didn't exist at all?  Can you post the code showing how you added it to the link and hidden fields?
0
 
usky1Author Commented:
Can you post the code showing how you added it to the link and hidden fields?
hidden field - <input name="Category_ID" type="hidden" />
link - <form name="form1" id="form1" method="post" action="#CGI.Script_Name#?categoryid=<cfoutput>#form.category_id#</cfoutput>">
0
 
_agx_Commented:
The hidden field is missing a value

<cfoutput>- <input name="Category_ID" type="hidden"  value="#Category_ID#"/></cfoutput>

> <form name="form1" id="form1" method="post"
> action="#CGI.Script_Name#?categoryid=<cfoutput>#form.category_id#</cfoutput>">

If you're storing the value in a hidden form field, don't add it to the action URL.  

These are the navigation links I was referring to:

<a href="#CurrentPage#?PageNum_GetProds=#Max(DecrementValue(PageNum_GetProds),1)##QueryString_GetProds#" class="productinfo">| Previous |</a>
      </cfif>
0
 
usky1Author Commented:
I actually had the value but got an error. the one thing I didn't do was add the cfoutput statement. I did that and the page stays the same.
I will add debug code to see if I can figure out why it isn't updating.
0
 
usky1Author Commented:
The  cachedwithin=#CreateTimeSpan(0,0,30,0)# in the query was holding on to the values. When i did a dump I saw the field values were actually updating from the database but the table was not. As soon a i removed the cache statement it started to work.
Question - I read that using the cachedwithin made the query more efficient. What are your comments on this?
Also one last thing is that the Category field is always selected. I'll take a look at that.
0
 
_agx_Commented:
> I read that using the cachedwithin made the query more efficient. What are your comments on this?

It usually does. The reason is that the query is pulled from memory (within the timespan you specify).  Pulling the query from memory is usually more efficient that retrieving the data from the database every time.  But if the data is frequently updated, it may not be worth it.  If you do decide to use cachedwithin, you would have to remember to clear the query every time you update.  
0
 
_agx_Commented:
.. if you don't clear the cached query, you won't see the updated values until you exceed #CreateTimeSpan(0,0,30,0)#  or the query is pushed out of cache (IIRC).
0
 
usky1Author Commented:
It looks like everything is functioning. Thanks again for all your extended help.
0
 
_agx_Commented:
You're welcome. Glad its working.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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