Search on select field

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

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

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>

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.

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?

I have an emergency item that I need to take care of. It might be the morning before I can post.
Thanks,

Okay

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,

> 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.

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>

> 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#">

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.

> 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>

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>

I changed the scope and got back the same results.

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".

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,,,

> <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?

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>">

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>

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.

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.

> 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.  

It looks like everything is functioning. Thanks again for all your extended help.

You're welcome. Glad its working.