Solved

no records are returned if some search fields are left blank

Posted on 2004-04-24
8
129 Views
Last Modified: 2010-04-25
DW MX 2004 7.01, ASP VBScript, Access DB

I have created a standard search/results set. If I submit the search page with some fields left blank, the results page will have no records. The search page form submission method is GET. Here is my SQL statement:

SELECT *
FROM tasks
WHERE request_date = #VARrequest_date#
AND requested_by = 'VARrequested_by'
AND job_ep = 'VARjob_ep'
AND deadline = 'VARdeadline'

I would like to return records based on whatever fields the user fills in, and ignore the blank ones.


thanks,
John
0
Comment
Question by:johnmarshall
8 Comments
 
LVL 8

Expert Comment

by:alexhogan
ID: 10910553
The query that you have now;

SELECT *
FROM tasks
WHERE request_date = #VARrequest_date#
AND requested_by = 'VARrequested_by'
AND job_ep = 'VARjob_ep'
AND deadline = 'VARdeadline'

will return results only if the request_date and the requested_by and the job_ep and the deadline equal the variables that you are sending to the statement.  In other words you are asking that all the fields be filled out in order to return results.

If you are just wanting the query to return results based on a single field then you would use OR.

SELECT *
FROM tasks
WHERE request_date = #VARrequest_date#
OR requested_by = 'VARrequested_by'
OR job_ep = 'VARjob_ep'
OR deadline = 'VARdeadline'

If you are wanting to use any combination of the fields then you would have to use additional code to identify your query.

For instance..,

Using Get as the method on your form, if someone filled in the fields requested_by and deadline, you would get simething that looks like this;

submittedpage.php?rb=John%20Doe&dl=050104

Where the url parameters rb and dl represent requested_by and deadline respectively.

You then set up a conditional statement that tracks how many of the possible combinations of the fields have been filled in. (the easiest way to do this is to give an inital value to all the fields and the ones that don't match the initial value are the ones you'll look for)

From there you would set up the values for the WHERE clause of your query by combining all of the filled in fileds into a string that's passed to the variable that holds the query.

sqlquery = "SELECT *
                  FROM tasks
                  WHERE "

This is what you get from the conditional statement

temp1 = "requested_by = 'VARrequested_by'"
temp2 = "deadline = 'VARdeadline'"

Now concat

sqlquery .= temp1
sqlquery .= "AND"
sqlquery .= temp2

This will give you the complete query;

sqlquery = "SELECT *
                  FROM tasks
                  WHERE requested_by = 'VARrequested_by'
                  AND deadline = 'VARdeadline'";

Now obviously this is psuedo code.  You didn't say what language you were using, but you should get the idea.
0
 

Author Comment

by:johnmarshall
ID: 10910787
What you seem to be proposing is a method for manipulating the data in different ways, the most relevent being combining fields. My needs are very simple though. I want to retrieve records that contain all the data entered. The more information the user enters, the less records retrieved. If I use OR and submit a blank form, it retrieves all the records, but the opposite should happen since I didn't enter any data. It seems to be treating blank fields as wildcards when it should be NULL or something else that would ignore it.

For now, I think entering a default value in the fields will do the trick, but its not very clean or elegant. It's also tricky because I have 'Text Areas' that could potentially contain the default text.


John
0
 

Author Comment

by:johnmarshall
ID: 10910792
Oh, and I'm using ASP VBScript with an Access DB in Dreamweaver MX.

John
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 23

Expert Comment

by:adilkhan
ID: 10911767
then on the search Result page Replace the NULL values with something, rather then setting some default value for textarea.

like this

VARrequested_by = Request("VARrequested_by")

if LEN(VARrequested_by) < 0 Then

VARrequested_by = "Not For Search"

End if

0
 
LVL 8

Expert Comment

by:alexhogan
ID: 10918440
johnmarshall,

I think you misunderstood what I was proposing.  My fault.

I'm not suggesting that you manipulate the data but rather the query statement.  Instead of using a single blanket query that could return inaccurate results you simple make the query more dynamic so it will produce the results that you require based on the user input on your form.  This way your are insured to retrieve accurate results.
0
 

Author Comment

by:johnmarshall
ID: 10970123
Well, I have solved this problem and it was very simple. I changed this:

AND field = 'VARfield'

to this:

AND field LIKE '%VARfield%'

...and now it accepts empty fields as a valid value. I'm leaving this question open
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 11535532
PAQed, with points refunded (250)

Computer101
E-E Admin
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Simple PHP Upload Script 11 2,686
Record Button 3 214
Debug application start 2 273
Adobe Acrobat Pro: Is this a known bug? 9 59
For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now