Filtered Search

[eggie]

Atleast that is what I am calling it.I have a table (orders) with (order_date[date/time], order_appl_duedate[date/time], client_id, order_status) as fieldsform.asp has form fieldsorder_dateorder_statusorder_appl_duedateclient_idformpost_exec.asp:I want to retrieve the queries and search the database for the above fields and filter the results accordingly. I wrote this, which I know is wrong because if the order.date and order.duedate are missing I get a parameter is missing error:

porder_date			 = Request.querystring("order_date")pstatus_id			 = Request.querystring("status_id")porder_appl_duedate	 = Request.querystring("order_appl_duedate")pclient_id			 = Request.querystring("client_id")call openDb()mySQL="SELECT order_id, order_lend_company, order_prop_type, order_prop_city, order_prop_state, order_status, order_date FROM orders WHERE order_date="&porder_date&" OR order_status="&pstatus_id&" OR order_appl_duedate="&porder_appl_duedate&" OR client_id="&pclient_id&" ORDER BY order_date"

Basically what I am trying to do is allow someone to pick any or all of the form fields and do a search through the database then display teh results. How I explained that well enough? I know using * is frowned upon, I just put it here to make it simple to read...Thanks,Michael

[justsomeguy]

You'll need to build the query in steps and check each value before adding it to the query. e.g.:

mySQL="SELECT order_id, order_lend_company, order_prop_type, order_prop_city, order_prop_state, order_status, order_date FROM orders WHERE "where = ""if porder_date <> "" then  if where <> "" then where = where & " OR "  where = where & "order_date=" & porder_dateend ifif pstatus_id <> "" then  if where <> "" then where = where & " OR "  where = where & "order_status=" & pstatus_idend if...etcif where <> "" then  mySQL = mySQL & where & " ORDER BY order_date"else  'error - no valuesend if

[eggie]

I had a feeling I would need to do somethign like that, but wasn't sure if it was the mose efficient. Thank you.

[eggie]

So everything works except the dates? I know the dates are being passed as I did a response.write to test.

[justsomeguy]

Are you sure the date is in the format that the database is expecting?

[eggie]

yep, double check that.table = 3/15/2009query response.write = 3/15/2009

[justsomeguy]

So what do you mean when you say the dates don't work?

[eggie]

If I try to filter by a known order date (ex: order date 3/15/2009) I get, "No orders to list". If I try to filter by a known due date (ex: due date 3/31/2009) I get, " No orders to list".I know both dates have something to show, so i am confused...

[justsomeguy]

It does sound like the format is wrong. Check the documentation for whichever database you're using to make sure you're converting the string date value into a date like the database is expecting. SQL Server in particular is pretty sticky about it. I've written a script dealing with SQL Server where it got all records from the database, and just tried to insert them into another database. The format that SQL Server returned the date in was not compatible with the insert statement (how brilliant is that?). I had to reformat the returned date value to get it to work right in the insert.