Compare form entry and database values; Also need loop through 14 tables

Normally I'd suggest something like a text box with auto-suggest for looking up catalog numbers. But 14 tables is a lot.  I'd expect just one.  Why so many tables?

Also, what database - MS SQL, MySQL, ..?

Why 14 Tables: The 14 tables represent the 14 main categories that our catalog numbers are in. There are two levels of subcategories in each main category. I didn't design the website, so I'm not sure if that's the best way to do it. I think it was designed that way to optimize the search engine, but I'm not sure.

The database is MS SQL 5.

(Maybe there were other reasons .. ) Usually separate tables isn't the best approach.  Entities of the same type should generally be stored in a single table. So you'd have one "Category" table.  If the only difference between a "Category" and "Subcategory" is the level, then subcategories could be stored in the "Category" table too.  With an additional "ParentID" column indicating which is which. There are other structures too, but that's a common one for storing your typical hierarchy like a menu or ORG Chart.

Do you have any flexibility with this app or do you just need to work with what you have?

MS SQL 5.

Do you mean MySQL 5 or SQL Server 2005?

No flexibility, unfortunately. I have to work with what's there.

Database is SQL Server 2005. Sorry. :)

Not tested but the form code might be something like below. Keep in mind you don't have to use autosuggest. It's just a nice-to-have feature. Once you have that working, adding the validation is simple.

Your Form

<cfset numOfFields = 6>
<cfform>
	<cfloop from="1" to="#numOfFields#" index="row">
		<cfinput name="catalogNum#row#" autoSuggest="cfc:YourComponent.getCatalogNum({cfautosuggestvalue})"><hr>
	</cfloop>
	<input type="submit">
	<cfinput type="hidden" id="numOfFields" name="numOfFields" value="#numOfFields#">
</cfform>

Select allOpen in new window

YourComponent.cfc:

<cfcomponent output="false">
	<cffunction name="getCatalogNum" access="remote" returntype="Array">
		<cfargument name="num" type="string" required="yes">
		<cfset var q = "">
		<cfquery name="q" datasource="YourDSNNameHere">
                       <!--- find all catalog numbers that _start with_ whatever the user entered --->
			SELECT CatalogNumber 
                        FROM   AllCatalogNumbers
                        WHERE CatalogNumber LIKE <cfqueryparam value="#arguments.num#%" cfsqltype="cf_sql_varchar">
		</cfquery>
		 <cfreturn listToArray(valueList(q.CatalogNumber))>    
	</cffunction>
</cfcomponent>

Select allOpen in new window

I am using Coldfusion MX.

Oh crap ... stupid me.  I'd forgotten it was MX. Scratch the autosuggest stuff, it's CF8+ only  (or use jquery's autosuggest stuff instead).  Give me a minute to regroup ;-)

aqx,

Thank you so much!!!!!! I will give this a try today and see what I come up with. I'll post any questions as I go along.

aqx,

YOU ROCK!!  THANKS A MILLION!
KOKO

Welcome :)