Greetings experts.
I have two .asp pages using vb script and developed w/ ultradev4. I have one page named product_search.asp that allows the user to enter up to 4 parameters(client, product code, ink color, and stock name). Text boxes on this page are named selectClient (drop-down list box), txtProductCode, txtInkColor, and txtStockName. This page passes data via POST.
The second page product_results.asp is a table w/ a record set that is querying a SOLID db. Here is my sql statement:
SELECT PRODUCTS.ID, PRODUCTS.PRODCODE, PRODUCTS.LONGNAME, PRODUCTS.STOCK_NAME, PRODUCTS.IMPRINT_COLOR, PRODUCTS.BATCH_STYLE, PRODUCTS.AFT_PROD_CODE, CLIENTS.LONGNAME
FROM PRODUCTS INNER JOIN CLIENTS ON PRODUCTS.CLIENT = CLIENTS.CLIENT
WHERE PRODUCTS.CLIENT = 'varClient' OR PRODCODE = 'varProductCode' OR IMPRINT_COLOR = 'varInkColor' OR STOCK_NAME = 'varStockName'
ORDER BY PRODUCTS.CLIENT
varClient=sselectClient
varProductCode=txtProductC
ode
varInkColor=txtInkColor
varStockName=txtStockName
The query works but not like i need it too. I can search w/ one parameter, say client name and return only results from the entered client name, but if i enter more than one search parameter the results are random. Here is what i need the query to do.
If one paramerter is selected (client) then return all records for that client.
If two parameters are selected (client, product code) then return all records from that client that also has the product code.
If three parameters are selected (client, product code, ink color) then return all records from that client that also has the product code and ink color.
Get the idea? Basically the more parametes that are entered the fewer records returned. Hope this makes sense. Say I enter, client=Acme, product code=001, ink color=blue, stock name=white paper then maybe only 5 records are returned. Whereas if i only entered client=Acme then 50 records are returned.
I am sure it is the way i have my sql statement configured but i surely cannot figure out what it should be. Also if no parameters are entered then i would like for all records to be returned. Thanks in advance. 200 points for this one.
Start Free Trial
